MySQL Database Incremental Backup Implementation

It is the most common requirement for those who use big mysql databases in their production. Most of the people generally take the full dump of the mysql db once a week or daily. But the disadvantage of taking full dump is it may take long time to backup and restore. Also if the backups are incremental and compressed then it takes small amount of data to be transferred to backup site.

The best backup strategy is to use the full and incremental backups. The incremental backups should have short time backup frequency ( eg. 5 or 6 hours ) and once a week is enough for the full backup. It can be done with the help of mysql utility program which works for either of the mysql database engines ( eg. MYISAM and innodb ). 

Working

The key to implement the incremental backup is to use the mysql 'binary logs'. We can configure the the mysql to generate the binary log files which contains the mysql db statements needed to update the db since the last incremental or the full backup.
The full db backup can taken with the help of mysqldump utility.

Utilities used

* mysqladmin & mysqldump comes with mysql-client package.
* mysqlbinlog comes with mysql-server package.
* Any command line FTP client program to copy the backup logs to ftp server. 

Backup Plan

1. Firstly generate the complete backup of the dbs with utility mysqldump. Compress this full backup and store this on a remote ftp storage.
2. Enable the binary logging and start storing the current binary logs which contains every mysql statement which modifies the db after the full backup.
3. Configure the binary logs in such a way that every (5 or 6 hour) the current binary log file is closed and ftp'd to the backup storage and a new binary log file is created to record the changes in the db.
4. Automate the step 3. once a week, create full backup and cleanup the previous full backup and now-obsolete binary logs from the FTP storage server.

Restore Plan

1. Copy the full backup and all the binary log files to the db server. Restore the full backup after creating the db if the db do not exists.
2. Restore the incremental binary files consecutively using the utility programs "mysqladmin" and "mysqlbinlog". The db is now uptodate to the moment of last incremental backup.
3. After complete restoration, once again take the full backup of the entire db.

Implementation

1. Binary logging is configured in the mysql server.

vim /etc/mysql/my.cnf
add the folling line:
log-bin=mybinlog

"binlog_do_db" can be used to specify the dbs for which we want to enable the binary logging. If we use this statement alone then it means all rest of the dbs which are not specified will have binary logging turned off. We can specify the multiple dbs using this statement by adding repeated enties, like:

binlog_do_db = db1
binlog_do_db = db2

"binlog_ignore_db" can also be alternatively used to specify the dbs, for which we do not want to turn on the binary logging. We can specify the multiple dbs using multiple statements like previous example.

Note: We should use the above statements very carefully because they can dangerous in certain situations. The working these statements will be explained in next post.

Restart the mysql:
/etc/init.d/mysqld restart