Mysql incremental backup breakpoint recovery

Misoperation in mysql in a production environment is quite normal, so incremental backup recovery of mysql is required.

Incremental backup is often used, it can specify a certain misoperation time and location for data recovery, more accurate recovery of the data we want to restore.

The following experiment demonstrates the misoperation of using incremental backup for data recovery.

1. Open the binary log in the configuration file and restart mysql

2. Create the test database and write the content

3. View the binary log, truncate the log, and complete the full backup

Mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql_bin.000001 / / View the binary log

Mysqladmin-uroot-pabc123 flush-logs / / refresh truncation to form a new log mysqldump-uroot-pabc123 school > / opt/school.sql / / full backup school database

4. View the newly generated binaries

5. Add two pieces of data and view the newly generated binary file again

Mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql-bin.000002 / / View the newly generated binaries again

6. Simulate misoperation and view the location point and time point of misoperation in binary files

7. Delete the list table and restore the full backup first.

Source / opt/school.sql / / for full backup and restore

Check the table again and the fully backed up data has been restored

8. Truncate the log again, and then perform incremental backup and restore

Mysqladmin-uroot-pabc123 flush-logs truncates the log again (the log is truncated here so that the correct data will not be overwritten by the previous operation)

Revert to the operation mysqlbinlog before the point in time before the misoperation-- no-defaults-- stop-datetime='2018-09-03 22 stop-datetime='2018 18 stop-datetime='2018 29'/ usr/local/mysql/data/mysql_bin.000002 | mysql-uroot-pabc123

Restore the part of the data that added jerry mysqlbinlog-- no-defaults-- start-datetime='2018-09-03 22 no-defaults 19VR 27' / usr/local/mysql/data/mysql_bin.000002 | mysql-uroot-pabc123 can see that all five pieces of data have been restored