mysql

Backup all MySQL databases automagicly with the PHP admin script

Server administration software like Cpanel, Plesl, Webmin etc have already their backup solutions. But they are runned mostly once a week or max a day because of performance reasons. Let say we just want to backup all of our mysql-databases every 2 hours.

To do this:

  • Create a directory like /backup/mysql
  • Save code below under something like backup_mysql.php
  • Chmod it to 755
  • Run it manually or with cronjobs /root/scripts/backup_mysql.php
#!/usr/bin/php
<?php
// use this if your php.ini does not allow shell etc #!/usr/bin/php -n

Mysql Order / Sort by before Group by

Every developer knows the horror of mysql sort/order by or select distinct using with group by. Mysql does group by before order by and you get mixed results not what you expected. This is a small solution with less performance problem:

SELECT * FROM 

(
select * from `my_table` order by timestamp desc
) as my_table_tmp

group by catid

order by nid desc

In this example we get latest news in each category. We create a temp table by sorting by timestamp and group by after it. It worked for me.

Comparison of free Shopping Carts - 2009

This is a comparison of free -mostly open source- "shopping carts" / "e-commerce systems": Magento, osCommerce , Virtuemart (+ Joomla), Ubercart (+ Drupal), X-Cart , Zen Cart , ShopCMS , CS-Cart , Cube Cart , eCommerce Framework G5 (ECF), Ecommerce Templates , FlyingCart, Go Ecommerce EShop Builder , Interspire Shopping Cart , iScripts Multicart , JadaSite , JShop , LiteCommerce , PhPepperShop, PrestaShop, ProductCart , Ecommerce Shopping Cart Software , VP-ASP Shopping Cart , WebAsyst Shop-Script , Zeus Cart (AJ Shopping Cart).

Repair all mysql databases-tables with PHP

phpMyAdmin is a great tool but there is no option to repair all databases at once yet. Imagine you have no shell-access and need to repair all your 100 databases. It would take long time. This small PHP-Script lets you list, analyze and repair all your databases belong to same user at once.

If you already have shell access you can do it with mysqlcheck:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases


Repair with PHP:

  

How do I secure my web site?

  1. Brute force dedection should be enabled.
  2. Cache directories should be secured.
  3. Captcha's should be enabled and required for anonym users to avoid spam.
  4. Code Injection should be prevented.
  5. Cookies should NOT be used to store passwords and sensitive data.
  6. CSRF should be prevented.
  7. Directory listing should be disabled.

#1045 Cannot log in to the MySQL server (PhpMyAdmin 2.4.8)

I have just installed new phpMyAdmin (version 3.4.8). There is no password for mysql root user. But I coudnt login and got this error:

#1045 Cannot log in to the MySQL server

Reason is: Missing blowfish string and config file.

The solution:

- change config.sample.inc.php to config.inc.php
- put some text for $cfg['blowfish_secret'] on line 17.

change

$cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

to

$cfg['blowfish_secret'] = 'sdghwkglsdkhuRandomTEXToituwetu45z'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

Mysql: update weight by an auto-incremented string created on the fly

I had a problem with Drupal6 by sorting tags. Mysql can sort or place some non-ascii letters correctly but PHP can not.

Example: I have a list of cities and I want to sort them by first letter. I want that "ívori" and "îwata" beeing listet with "Ibiza" under "i". PHP places both ívori and îwata after Z. To prevent this I update "weight" manually with mysql and sort them by weight instead of alpha.

select @my_temp_counter:=0;
update `term_data` set weight = (@my_temp_counter:=@my_temp_counter+1) order by name;

#2006 - MySQL server has gone away

I have an i7 QuadCore PC. I have installed WAMP on it. Last time I get this error commonly: "#2006 - MySQL server has gone away". But when I try with mysql console I see that Mysql server is online also not "gone away". How can I configue Phpmyadmin to wait and not give such rush errors ?

Invalid utf8 character string

If you get this error: "Invalid utf8 character string" when you are using iconv you should convert from utf-8 to utf-8 to.

$htmc = iconv("ISO-8859-9", "UTF-8//TRANSLIT//IGNORE", $htmc);
		 
$htmc = iconv("UTF-8", "UTF-8//TRANSLIT//IGNORE", $htmc);

Dont forget: //TRANSLIT comes before //IGNORE

Using shared same Mysql Data dir on multiOS Linux+Windows Laptop

My old OS was Win XP. I have formatted my Notebook and installed Windows 7 64bit english and Ubuntu 10 64 bit LTS. My laptop is a Fujitsu Siemens Amilo pa 1510 with AMD Turion 64 X2.

The reason why I installed 2 OS is using same codebase for webdevelopment. I want to use same htdocs/www directory for both Windows and Linux.

Syndicate content

CafeWebmaster.com(CW) is a free online community for webdevelopers and beginners. Anybody can share their code, articles, tips, tutorials, code-examples or other webdesign related material on the site. Newbies can submit their questions and reply to existing questions. CW does not guarantee or warrant reliability of code, data and information published on the site. Use the site on your own risk. The site takes no responsibility of direct or indirect loss or any kind of harm to its users. The site also doesn't take responsibility of infected files or source code with any kind of infection or viruses, worms, spywares, malwares, trojan horses. CW reserves the right to edit, move, or delete any of content for any reason.