MySQL Incremental Backup - Point In Time Backup and Recovery of InnoDB and MyIsam Databases

https://www.howtoforge.com/tutorial/mysql-incremental-backup-point-in-time-recovery

Doing incremental backups is an important requirement for large production databases. Without a safe incremental backup, you can not tell yourself that you have a reliable production database. Because you must have enough data in order to recover your database in emergency cases. After some search on Internet, I could not find any tool that can do a complete incremental backup for MyISAM and InnodB in a mixed environment were applications use both database engines simultaneously (maybe I am not an expert searcher on Google and Internet). So I decided to write this one, but to avoid wasting time and benefit from other open-source solutions, I preferred to add this feature to -automysqlbackup- script that is the best script for full backup in simplicity and widespread use.

Mechanism

We use the Post- and Pre feature of automysqlbackup to do an incremental backup. Before starting a full backup, mysql-backup-pre executes a query to lock the whole database during backup process because we have to freeze the binlog to avoid any change while backup is running. The binlog name and position may not change during backup. The binary log position is very crucial in the subsequent incremental backup process and will be used as a starting point to begin the next incremental backup. After finishing the full backup, mysql-backup-post removes the database lock.

Lock Query: FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)
Find Lock Queries:mysql -u[username] -p[pass] -e "show processlist" | grep "SELECT SLEEP(86400)" | awk '{print $1}'

Requirements

• root privileges to install package and update mysql.conf
• mysql-community-client package
• installation automysqlbackup and mysql-incremental

Installation

Install mysql-community-client package for your distro.
Note: after the MySQL installation you must have the 'mysqlshow' command.
Install automysqlbackup:

download the package from https://sourceforge.net/projects/automysqlbackup/

tar -xzf [PathYouSavedTarFile] -C /tmp/

cd /tmp/

./install.sh

During installation of automysqlbackup, you will be asked about path of automysqlbackup.conf and its binary, you can leave defaults without any change.

rm /etc/automysqlbackup/myserver.conf

Install the mysql-incremental: Download the package from https://sourceforge.net/projects/mysqlincrementalbackup/

cd /tmp
wget http://downloads.sourceforge.net/project/mysqlincrementalbackup/mysql-incremental.tar.gz
tar xfz mysql-incremental.tar.gz

cp mysql-incremental /etc/automysqlbackup/

chmod 755 /etc/automysqlbackup/mysql-incremental

cp mysql-backup-post /etc/automysqlbackup/

chmod 755 /etc/automysqlbackup/mysql-backup-post

cp mysql-backup-pre /etc/automysqlbackup/

chmod 755 /etc/automysqlbackup/mysql-backup-pre

Update the automysqlbackup.conf:
Find below parameters, uncomment and change them:

        CONFIG_mysql_dump_username='Mysql user name. It must has privileges to get Lock'
	CONFIG_mysql_dump_password='Password'
	CONFIG_backup_dir='The backup directory you want to store full and incremental backup'
	CONFIG_db_names=('databaseName1' 'databaseName2' )
	CONFIG_db_month_names=('databaseName1' 'databaseName2' )
	CONFIG_mysql_dump_master_data=2
	CONFIG_prebackup="/etc/automysqlbackup/mysql-backup-pre"
	CONFIG_postbackup="/etc/automysqlbackup/mysql-backup-post"

Update my.cnf:

Edit the MySQL configuration file:

nano /etc/mysql/my.cnf

1- BinLog Format
Due to some limitation on STATEMENT format, my recommendation is to set ROW based format. For more information please see the 'troubleshoot' section in this howto. You can check the type of binary log format by executing "select @@binlog_format;" query. To modify logbin format , you must add binlog_format = ROW to mysql.conf or my.cnf .
2- binlog_do_db
You must specify the databases that you intend to have the related changes in the binary log. Please note if you do not specify any database, any change on any database will be logged into binary log. In this case, if you chose STATEMENT format, maybe you have some trouble when restoring from incremental backup and binlog files. You can add databases to this option:

binlog_do_db = DATABASENAME1
binlog_do_db = DATABASENAME2

3- expire_logs_days
To have binary log files for a longer time, you can increase this parameter to a higher value. My recommendation is 60 days. So you must add or change it to "expire_logs_days = 60".
4- log-bin
The directory where the binary logs will be stored. In old MySQL versions, mysql-incremenetal might not be able to find the correct path. So if you get an error about this after executing mysql-incremental, you must update mysql-incremental script and set the binary log path.
5- log_slave_updates
If you are setting up mysql-incremental backup on a slave server, you must enable this option. Normally, a slave does not log updates to its own binary log as they were received from a master server. This option tells the slave to log the updates performed by its SQL threads to its own binary log. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates

Run automysqlbackup

Run automysqlbackup manually to have at least one full backup from your specified databases.

automysqlbackup

After executing the command successfully, check the /[BackupDirInAutomysqlbackup]/status/backup_info file for the newly added information about the daily backup. For error details, check /var/log/Backup_Post_Pre_log . The backup file will be stored in the directory /[BackupDirInAutomysqlbackup]/daily/[DatabaseName]/ .

Run mysql-incremental

Run mysql-incremental manually now to have at least one hourly backup.

mysql-incremental

In case of an error, the details are logged in the file "/var/log/Backup_Incremental_Log" . The incremental backup files will be stored in the directory /[BackupDirInAutomysqlbackup]/IncrementalBackup/ .

Edit the root crontab

You can schedule mysql-incremental for more than one hour. You can find the total time of full backup from backup_status and then based on that value you set an accurate schedule time. Of course mysql-incremental backup does have a mechanism to find any running full backup before start, so there is no concern about conflict between incremental and full backup.

crontab -e

5 00 * * * root /usr/local/bin/automysqlbackup
25 *  * * * root  /etc/automysqlbackup/mysql-incremental

Restore Database

In order to restore up to a specific time (point in time recovery), first you must restore one full daily backup and then restore sequentially related incremental backup files. To clarify more, here is the steps to recover testDB database. In sample scenario we intend to recover our data up to 2015-5-01 at 2 AM. we have set /backup as our main backup dir and testDB as our target database:

1- mysql -u root -p DatabaseName < /backup/daily/testDB/daily_DatabaseName_2015-05-16_00h05m_Saturday.sql.gz
2- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_00h25m.1
3- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_01h25m.2
4- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_02h25m.3

Important notes and Troubleshooting

MySQL supports different formats for the binary log. Some Mysql versions use 'statement-based' as binlog format that this type of binlog does have some limitations that we must pay close attention to it when we intent to use it in incremental backup procedure. When mysql is set to statement-base format, it does not able to filter correctly based on databases. If you set 'USE or \u' to change database and then update another database which is not included in binlog-do-db, the statement will be logged in binlog file that it is not desirable state! and will expose some issue when restoring based on specific database and also if you change to another database that is not included in binlog-do-db, and update a database which is included in binlog-do-db, the statement will not logged to binlog file. our purpose from adding databases to binlog-do-db is to filter based on database,but it does not work as expected. If USE or \u is not executed before running queries, mysqlbinlog can not extract 'update queries' related to one database. We will explain more this issue with below scenarioes:

databases: 
 - binlog
     - person (table) 
  - binlog2
     - person (table)

 binlog-do-db=binlog2 (it is supposed only change of this database are logged to binlog file)
--------Scenario 1---------
\u binlog2
insert into person (data) values ('17') ---> loged in binlog  *desired state*
insert into binlog.person (data) values ('25'); ---> logged in binlog (target database is 'binlog' ) *undesired state*
--------Scenario 2---------
\u binlog
insert into person (data) values ('17') ---> is not logged in binlog  *desired state*
insert into binlog2.person (data) values ('25'); ---> is not logged in binlog (target database is 'binlog2' ) *undesired state* because the binlog2 database
is begin changed, so we want to have this change,but it will not logged in logbin file
--------Scenario 3---------
if you just connect to database without any USE or \u statement, all of updates on any databases will be logged, but mysqlbinlog can not able to filter
based on specific database, so that is not desirable state for our purpose in incremental backup. Using USE or \u before executing update queries, is very
important. Because mysqlbinlog finds update queries based on USE statement in binlog file.

Work around for the mentioned issue

1) By defining users on databases in a way that each user only has access to one database to update (application user) and when connection to database, the name of database must be specified. Of course most of applications do have a config file that the credentials and name of database are set in it, so in that case you will not have a cross-access on databases and there will not be concern on using "\USE or \u".
2) If you use row-based binlog format, so all of mentioned issue will be gone. in other words,row-based format is much more proper method for binlog. https://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html

Log Files

I did try to log everything in a log file so you can find enough information in the logs:
/var/log/Backup_Post_Pre_log/var/log/Backup_Incremental_Log/[SpecifiedBackupDirInAutomysqlbackup.conf]/status/backup_info
The file "backup_info" contains the detailed info about the backup and when the backup finished (Times are in Unix Time format). It contains the binlog name and position of the timepoint the backup started, the type of backup, number of backups since the last full backup and the duration of the backup.
Sample backup_info:

1431043501,mysql-bin.000026,120,Daily,2015-05-08,0,24
1431044701,mysql-bin.000026,120,Hourly,2015-05-08,1,1

Here are description of the different values:

 1th) 1431043501 : indicates the time when the backup has been finished. You can run date --date @1431043501 command on the server the backup has been done to view it in human readable format.
 2th) Mysql-bin.000026 : indicates the binary log name that backup up to this file has been done.
 3th) 120 : indicates the position of binlog  that backup up to this position in binary log has been done.
 4th) Daily/Hourly: indicates type of backup. Daily does mean the full backup by automysqlbackup script and Hourly is done by mysql-incremental script.
 5th) 2015-05-08: The date that backup has been done. This date will be used in creating directory for incremental backup and also as a base for restore hourly backups. In restoring procedure, first a full backup is restored and then sequentially other incremental backup are restored.
 6th) 0 : indicates number of backups from previous full backup. 0 does mean the backup is full and others mean hourly. This number is very important in restoring procedure.
 7th) 24: The backup duration in second.

Bug Report

You can report bugs or give your suggestions and reviews at https://sourceforge.net/projects/mysqlincrementalbackup .