MySQL How To

How to set up automatic MySQL database backup

If you have shell access to your web/db server, get the lates automysqlbackup script. Copy it to a bin folder, change permissions to 755, create a config file based on the content of the automysqlbackup (look at the beginning) and save it somewhere. Make that file only available for you to read (chmod 600).

Now create a mysql user. Avoid special characters in the user password as they often interfere with the automysqlbackup script. Allow that user SELECT and LOCKTABLES privileges to the databases you want to back up.

Test it our and schedule via 'crontab -e' with something like this:

0 3 * * * /home/user/bin/automysqlbackup-2.5.1-01.sh -c /home/user/etc/automysqlbackup.conf

(to use the -c option you need the script version 2.5.1 or higher). Note that the 2.5.1-01 has a glitch with the -c option. You need to edit it and change USERNAME to be something other than "debian"

Now if you want to pull the DB backups to your home box run something along the following lines:

rsync -av --delete --link-dest=/backup/web/site/ site@site.org:backups :bin :public_html :site2.com /backup/web/rsync/site.$DATE/ && rm /backup/web/site && ln -s /backup/web/rsync/site.$DATE /backup/web/site

The above command uses hard links to limit the amount of space and keep rolling backups. Again, scheduled via crontab on the local box such similar to this:

0 2 * * * /bin/bash /home/user/bin/rsyncsite >> /home/user/rsync.log

How to restore Mysql root priveledges

so you've change root password, but forgot to set priviledges to 'grant' at the same time...

sudo /etc/init.d/mysql stop
sudo mysqld -u root --skip-grant-tables
 sudo mysql
use mysql
 select * from user;
 update user set Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',Index_priv='Y',Alter_priv='Y' where Host='localhost' and User='root';
FLUSH PRIVILEGES;