Mysql is the world's most popular database for Internet use, powering applications on thousands of websites. It's used in most blogs (such as Wordpress), most CMS's (such as Joomla and Drupal), and most forums (such as phpBB). It's very likely that you use one or more Mysql databases.
Mysql databases need to be backed-up, and backup should usually be done more than once per day. Rimuhosting (my VPS provider, which I highly recommend) backs up VPS servers once per week. Obviously, if you use mysql databases for frequently-updated data, you *must* have a backup and recovery strategy. (A RAID sometimes fails!)
This article (with backup script) shows you how to set up full and incremental compressed backups of multiple databases to Rimuhosting's (or another) backup server using mysqldump and mysql binary log files. Backup will be done every 6 hours (configurable), with a full backup once per week. Since backups are incremental and are compressed, the amount of data to be transmitted is relatively small. It is assumed you have a running mysql server, which should provide you also with the utility programs needed for both backup and recovery. This backup method should work for any mysql database storage engine (e.g., myisam and innodb). It is however tested only for myisam databases on Debian Etch.
mysqldump is a utility program that reads databases and generates the mysql statements necessary to completely rebuild the databases. It can be used to make a full backup of one or more databases in a single operation.
BINARY LOG FILES
Mysql can be configured to generate "binary logs." A mysql binary log file in an incremental backup. It contains the mysql statements needed to update the database since the last full or incremental backup.
The backup strategy is:
The restore strategy is:
1. Configure mysql to do binary logging.
log-bin = mybinlog
You can specify which databases to do binary logging for, or which databases NOT to do binary logging for.
"binlog_do_db" turns binary logging on for a given database. If this statement is used, all databases not specifically named will have binary logging turned off. Multiple statements can be made, one to a line:
binlog_do_db = drupalaa
binlog_do_db = tosdrupal1
Alternatively, you can use the statement binlog_ignore_db. This statements turns binary logging on for all databases EXCEPT the database(s) names. Again, you can make several binlog_ignore_db statements, one to a line:
binlog_ignore_db = sqlgrey
binlog_ignore_db = uselessdb
If you have applications that use more than one database, you should be careful when using either binlog_do_db or binlog_ignore_db. If you have doubts, you should read http://dev.mysql.com/doc/refman/5.0/en/binary-log.html.
Restart your mysql server:
2. Set up automatic full and incremental backup via cron
(The following script assumes the backup directory on the FTP server is /myvar/lib/mysql. Modify this as desired. The script refers to the Rimuhosting backup server. However, the script can easily be modified to back up to any FTP server. I can install this system on most servers for a fee. If interested, contact me)
Copy this script to /root/crontasks/newmysqlbinlog:
#echo "Creating mysql new binary log at `date`"
if [ `date +%A` == "Sunday" -a `date +%H` == "06" -o "$1" == "dump" ]; then
echo "Weekly Backup started `date`"
echo "Full mysql database dump started"
echo 'All existing full backups and binary log files will be removed'
rm -f /var/lib/mysql/*.bz2
mysqldump -uusername -ppassword --flush-logs --delete-master-logs --master-data=2 --add-drop-table --lock-all-tables --databases database1 database2 database3 > /var/lib/mysql/$DBFN
echo "mysql dump complete"
# echo "starting new bin log"
mysqladmin -uusername -ppassword flush-logs
newestlog=`ls -d /var/lib/mysql/mybinlog.?????? | sed 's/^.*\.//' | sort -g | tail -n 1`
for file in `ls /var/lib/mysql/mybinlog.??????`
if [ "/var/lib/mysql/mybinlog.$newestlog" != "$file" ]; then
lftp -u 'ftpuser,ftppassword' backupspace.rimuhosting.com -e "set ftp:ssl-protect-data true; mirror -er --reverse -I *.bz2 -X $newestlog /var/lib/mysql /myvar/lib/mysql; mput /var/lib/mysql/mybinlog.index -O /myvar/lib/mysql; exit;"
#echo "Bin Logs backed up"
Edit the above script to reflect your mysql username (probably root) and password, as well as your FTP backup server username and password. Replace "database1 database2" with the names of the databases to back up. (The "echoed" statements will be emailed to you, the system administrator, by cron. Remove these lines if you do not want the emails.)
The script handles both incremental and full backups, compresses all files before transmission, and automatically cleans outdated backup files from the backup ftp server. It performs one full backup per week, on Sunday at 06:02. You can force the creation of a full backup (for testing or whatever purpose) by passing the argument "dump".
The "flush-logs" mysql command is used to create a new binary log file. This command might be issued to the mysql server outside of this script (such as when the mysql server is restarted), producing an extra binary log file. In this case, the script will compress and back up the extra binary log file the next time backup is done.
Store the following line in /etc/crontab to execute newmysqlbinlog every 6 hours (this is of course configurable):
2 */6 * * * root /root/crontasks/newmysqlbinlog
(If you have to restore damaged MyISAM tables, try to recover them using REPAIR TABLE or myisamchk -r first.)
For any backup system, It is a good idea to practice the recovery step before assuming that you are protected!