I recently moved to a master/slave setup with MySQL. At first I was thinking.. “Oh this is great, I have a backup database now in case anything breaks on the master. “ Don’t go there.. think about it for a second. If something gets corrupted on the master, then it is going to push the changes over to the slave and then you will have 2 corrupted databases. It is not that hard to create a backup, though, using mysql_dump + shell script + crontab. That way you can have a nightly backup and reload it into MySQL if anything gets corrupted. Here is what you do:
Create a file with your favorite editor called backup.sh and add the following to it:
NOW=$(date +”%b-%d-%y”)
mysqldump -u youruserid -pyourpassword –all-databases > /path/to/mysql_backups/mysql_backup_$NOW.sql
The 2nd line in this file sets a variable for the current date in format like this: Aug-26-09. The last line uses the mysql_dump command to output all your databases to a file that appends the current date to the file name using the NOW variable. If you did not append the date to the mysql backup, then it would get overwritten every night. NOTE: you may also want to create a delete script, so this cron does not fill up your filesystem.
Next thing you need to do is change the permission on the file so that you can execute it. I am just assigning RWX for everyone, because I am too lazy to do it the right way (you probably just want to make this executable by the crontab user)
The last thing you have to do is add it to your crontab. 0 0 * * * *, makes the cron execute it at midnight every night.
Now this script will execute every night and export your entire database. Now, dont you feel better?? Thought so.
