Incremental backup of MySQL and recovery based on point-in-time and location

The advantage of incremental backup is that there is no duplicate data, the amount of backup is small and the time is short. The disadvantage is also obvious, the need for the last full backup and full backup after all the incremental backup to recover, reverse recovery, operation is more cumbersome.

MySQL does not provide a method for incremental backups, but incremental backups can be implemented indirectly via binary logs.

The meaning of the binary log to the backup is as follows:
1) binary logs save all updates or log files that may update the database
2) The binary log starts logging after starting the MySQL server and re-creates the new log file after the file reaches the size set by max_binlog_size or the flush Logs command received.
3) Only the Flush logs method must be timed to recreate the new log, generate a sequence of binary files, and save these logs to a safe place in a timely manner to complete an incremental backup of the time period.

Incremental backup

Turn on the MySQL binary logging feature

# vim /etc/my.cnf    [mysql]    log-bin=mysql-bin    //在[mysql]项下添加# systemctl restart mysqld          //重启服务  （此时usr/local/mysql/data/ 目录下会生成 mysql-bin.000001二进制文件）

Flush-logs Save the last-to-current data and operation in the last generated binary and generate another new binary file

    # mysqldump -u root -p school > /opt/school.sql   //完全备份 数据库school    # mysqladmin -u root -p flush-logs  //增量备份 生成新的二进制文件    （此时usr/local/mysql/data/ 目录下会生成 mysql-bin.000002二进制文件）

View the contents of a binary file

    # mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

Incremental Recovery

Scenarios for incremental recovery:
1) considered that the SQL statement destroyed the database
2) A system failure before the next full backup causes data loss in the database
3) in the master-slave architecture, the main library data has failed.

There are two types of data loss based on:
1) Only the data changed after the full backup is lost
2) lost all data after full backup

Data recovery changed after a full backup is lost

# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p（恢复操作  注意恢复的顺序，要先恢复最先生成的二进制文件）

All data recovery after a full backup is lost

# mysql -u root -p school < /opt/school.sql # mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p（恢复操作  注意恢复的顺序，要先恢复最先生成的二进制文件）

The following is a summary of the principle of incremental backup: The first is the weekly use of mysqldump full backup of the whole library, after the time of the whole library backup, using flush logs to generate new binaries every day, the binary files are stored daily changes in the operation of the database content, content is not duplicated. So the weekly backup of the whole library plus the daily binary preparation files, equivalent to the current data state of the database

• Data recovery based on point-in-time and location
• Using binary logs allows for time-and location-based restores, as we delete a table in the middle of an operation by mistake
• We can check the time of the binary log or the location of the recovery to skip this step error operation.
• We accidentally deleted the Zhangsan.
• Mysqladmin-u root-p Mr. Flush-logs into binary files
• Then look at the point-in-time, location, and point-in-time, location of the wrong operation of this binary file

进入数据库 # mysql -u root -p> use school;     > drop table info；    删除表

• Restore in sequence

    # mysqldump-u Root-p School >/opt/school.sql# mysqlbinlog--no-defaults mysql-bin.00 0001 | Mysql-u root-p# mysqlbinlog--no-defaults mysql-bin.000002 | Mysql-u root-p # mysqlbinlog--no-defaults mysql-bin.000003 | Mysql-u root-p# mysqlbinlog--no-defaults mysql-bin.000004 | Mysql-u root-p  

• Time-based recovery

• Skipped the wrong time

    # mysqlbinlog--no-defaults-- Stop-datetime= ' 2018-07-03 18:19:49 '/usr/local/mysql/data/mysql-bin.000005 | Mysql-u root-p# mysqlbinlog--no-defaults--start-datetime= ' 2018-07-03 18:22:21 '/usr/local/mysql/data/ mysql-bin.000005 | Mysql-u root-p  

• Location-based recovery

• Skipped the wrong location

    # mysqlbinlog--no-defaults-- stop-position= ' 347 '/usr/local/mysql/data/mysql-bin.000005 | Mysql-u root-p# mysqlbinlog--no-defaults--start-position= ' 395 '/usr/local/mysql/data/mysql-bin.000005 | Mysql-u root-p  

Incremental backup of MySQL and recovery based on point-in-time and location