How to Automate MySQL Database Backup
In the previous article, I told you how to create My SQL database backup using a special software (mysqldump). And here, I would like to tell you how to automate MySQL database backup.
Let’s think how to automate creating backup. There is a special program – cron. I allows to launch processes at a specified time or at regular intervals. Cron can be used only for Unix. So if you use Windows for your hosting, please contact you hosting-provider to get know how you can run processes at a certain time.
0 0 * * * /usr/local/bin/mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date “+\%Y-\%m-\%d”`.gz
This command will be performed every day at midnight (00.00 AM) from cron. It will make a dump of your database (DBNAME) and will archive it with gzip to an archive file that corresponds to a current date. For example, if we create dump on Jan, 3, 2010, archive file will be named as 2010-01-03.gz. We use ‘date’ command to get files with creation date. This command is standard for all unix-systems. It allows setting of arbitrary date output format: date “+%Y-%m-%d”. We quoted this command with backticks. In unix shell, this will insert the command to the result of performing another command.
Save this rule for cron and wait for results. So, every day we will get archived copy of our database. You can easy find the desired archive by its name and restore some info from there. Besides, if you wish to automate removing of old archives, use ‘find’ standard cron command. Please read documentation to get more info on this.
If you have a PC that is constantly connected to the Internet, you can copy backups there using cron. Provider’s hosting-machine is a very reliable thing. But better safe than sorry. Use ftp and scp commands when copying to another PC. Add these commands to cron. If your PC supports ssh connection, use secure copy client for files copying (scp). Please find more info about this command in the respective documentation.
Tip
mysqlhotcopy script (it is created with Perl) would help you to simplify creating of database & tables backup. You can use it to increase flexibility of backup creating.
