MYSQL backup: enables incremental MYSQL backup of binary logs.

We can perform full backup for a small number of databases every day, because it does not take much time. However, when the database is very large, we are unlikely to perform a full backup every day, in addition, it is changed to a weekly full backup, and an incremental backup every day is similar to this backup policy. The principle of Incremental backup is to use mysql binary logs, so we must enable

We can perform full backup for a small number of databases every day, because it does not take much time. However, when the database is very large, we are unlikely to perform a full backup every day, in addition, it is changed to a weekly full backup, and an incremental backup every day is similar to this backup policy. The principle of Incremental backup is to use mysql binary logs, so we must enable

We can perform full backup for a small number of databases every day, because it does not take much time. However, when the database is very large, we are unlikely to perform a full backup every day, in addition, it is changed to a weekly full backup, and an incremental backup every day is similar to this backup policy. The principle of Incremental backup is to use mysql binary logs, so we must enable the binary log function.

I. Incremental Backup

1. For example, we make a full backup at eleven o'clock P.M. on Sunday:

Mysqldump -- single-transaction -- flush-logs -- master-data = 2 -- all-databases> fullbackup_sunday_11_PM. SQL

In the SQL file, we will see two rows:

-Position to start replication or point-in-time recovery from

-Change master to MASTER_LOG_FILE = 'bin-log.20.02 ', MASTER_LOG_POS = 107;

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

2. perform an incremental backup at eleven o'clock P.M. on Monday:

Mysqladmin flush-logs

A new bin-log.000003 of the binary log file will be generated, and the bin-log.000002 will save all the changes from Sunday eleven o'clock P.M. till now, we just need to back up the file to a safe place. Then on Tuesday we made an incremental backup, or execute the same command, and then we saved the bin-log.000003 file.

Ii. Restore backup

For example, if a fault occurs at on Wednesday, we need to restore the fault. First, we need to import the complete backup on Sunday:

Mysql <fullbackup_sunday_3_AM. SQL

Next we import incremental backups on Monday and Tuesday:

Mysqlbinlog bin-log.000002 bin-log.000003 mysql

At this point we have recovered all the backup data, we can also find the bin-log.000004 to further restore the latest data.