This is my mySQL backup page. I just though I would help everyone who was having issues with backing up mySQL. I have my mySQL backup setup to run nightly with a cron job. I use multiple scripts sdate.sh and dump.sh that store the mySQL backup in /home/backup/mysql. The scripts are stored in /usr/local/bin on my system cause I use sdate.sh for alot of scripts.
File sdate.sh Or the txt version sdate.txt. This file is used to set the days and only save 7 or more days of backups. You run the command like #./sdate.sh 25 which shows you the date 25 days ago. Sdate.sh returns the date in yearmonthday so 20080528 format for the mySQL backup script to use. Have you ever wondered how to remove the 0 from 05 in a script. Just add 0 to it.
The next script I use is the dump.sh file, or you can view the file at dump.txt The dump.sh script does most of the work with my mySQL backups. Dump.sh puts the current date into variables, and then backs up all the mysql databases, and then gzips them to the /home/backup/mysql directory. It also gets the date from 7 days ago, and then removes the backup file from 7 days ago.
You will need to change the following line in dump.sh for your master mySQL password. I know I could have written my mySQL backup script so you just change the variable at the top of the file, but I have never learned anything from a script like that. People don’t usually go through it and customize it for themselves, and that is the best part of using Linux. Please feel free to customize my mySQL backup script for your own.
/usr/bin/mysqldump -A -c -uadmin -ppassword | gzip –best > /home/backup/mysql/all_dbs.$year$month$day.gz
Here is the crontab that I setup.
0 0 * * * /home/backup/bin/dump.sh |/usr/bin/mailx -s “Dump.sh” email@emailaddress.com