Mysql database Incremental backup and recovery methods

This article does not introduce the simple import and export operations on database data using mysqldump. Here we will introduce how to perform Incremental Backup Data and restore Data Based on mysql binlog logs.

Mysql Incremental Backup

A small number of databases can be fully backed up every day, because it does not take much time. However, when the database is very large, it is unlikely that a full backup is performed every day. At this time, Incremental Backup can be used. The principle of Incremental backup is to use the binlog log of mysql.

1. perform a full backup first:

The Code is as follows:	Copy code
Mysqldump-h10.6.208.183-utest2-p123-P3310 -- single-transaction -- master-data = 2 test> test. SQL

At this time, you will get a full backup file test. SQL

In the SQL file, we can see:

The Code is as follows:	Copy code
-- Change master to MASTER_LOG_FILE = 'bin-log.w.02 ', MASTER_LOG_POS = 107;

The changes after the backup are saved to the bin-log.000002 binary.

2. Add two records to the t_student table of the test database and run the flush logs command. A new bin-log.000003 of the binary log file is generated, and the bin-log.000002 saves all the changes after full backup, both adding record operations and saving them in the bin-log.00002.

3. Add two records to Table a in database test, and delete table t_student and table a by mistake. The operations for adding records in a and for deleting tables a and t_student are recorded in the bin-log.000003.

Ii. mysql recovery

1. First import full backup data

The Code is as follows:	Copy code
Mysql-h10.6.208.183-utest2-p123-P3310 <test. SQL

You can also use source to import data directly under the mysql command line.

2. Recovery bin-log.000002

The Code is as follows:	Copy code
Mysqlbinlog bin-log.000002 | mysql-h10.6.208.183-utest2-p123-P3310

3. Recovery part of bin-log.000003

Find the time point of the accidental deletion in general_log, and then find the corresponding position point in the more corresponding time point in the bin-log.000003, You need to restore to the previous position point of the accidental deletion.

You can use the following parameters to control the binlog interval.

-- Start-position start point -- stop-position end point

-- Start-date start Time -- stop-date end time

After the recovery point is found, you can start to recover.

The Code is as follows:	Copy code
Mysqlbinlog mysql-bin.000003 -- stop-position = 208 | mysql-h10.6.208.183-utest2-p123-P3310

Incremental Backup File Maintenance:

L run the following command on the Mysql console to clear the binary log file: reset master;

L in my. ini, you can set log Expiration days to automatically delete logs that expire, which helps reduce the workload of log management:

Expire_logs_day = 9

Logs can be saved for 9 days. logs that expire after 9 days are automatically deleted.

In daily applications, automatic full backup and Incremental Backup can be combined. For example, a full backup is performed every Sunday and the log cleaning time of Incremental backup is set to more than 7 days. If there is a problem with the database that day, you can use full backup and Incremental backup for recovery.