MySQL backup: MySQL incremental backup with binary logging enabled

A small number of databases we can make a full backup every day because it won't take much time, but when the database is large, we are less likely to make a full backup every day and change it to a weekly full backup, an incremental backup like this one every day. The rationale for incremental backups is to use the MySQL binary log, so we must enable binary logging.

first, incremental backups

1, for example, we do a full backup in Sunday 11 o'clock in the afternoon:

Mysqldump--single-transaction--flush-logs--master-data=2--all-databases > Fullbackup_sunday_11_pm.sql

In the SQL file we see two lines:

–position to start replication or point-in-time recovery from

–change MASTER to Master_log_file= ' bin-log.000002′, master_log_pos=107;

The second line contains the information we need, meaning that all changes will be saved to the bin-log.000002 binary file after the backup.

2, then in Monday 11 o'clock in the afternoon we'll do an incremental backup:

Mysqladmin Flush-logs

A new binary log file will be generated bin-log.000003,bin-log.000002 saves all changes since Sunday 11 o'clock in the afternoon and we just need to back up the file to a safe place. Then Tuesday we do an incremental backup, or execute the same command, when we save the bin-log.000003 file.

Second, restore backup

For example, Wednesday noon 12 O'Clock a failure, then need to restore, we first import Sunday full backup:

MySQL < Fullbackup_sunday_3_am.sql

We then import incremental backups for Monday and Tuesday:

Mysqlbinlog bin-log.000002 bin-log.000003 MySQL

Now that we have recovered all the backup data, we can also find bin-log.000004 to recover the latest data.