Full recovery of MySQL 5.7 database via mysqldump

1. Create tables and test data before backup

mysql> create table t1 (tm datetime);mysql> insert into t1 values(now());mysql> insert into t1 values(now());mysql> insert into t1 values(now());mysql> insert into t1 values(now());mysql> insert into t1 values(now());

2. View the current binary log

3. Fully prepared MySQL database

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --triggers --routines --events --set-gtid-purged=off> backup.sql

4, because mysqldump added the--flush-logs parameter, the backup binary log is saved in the new file

5. Add Database to table T1

6, read the binary log file, and export to a file, due to the gtid, you need to add parameters--skip-gtids

mysqlbinlog --skip-gtids mysql01-bin.000008 > bin.sql

7. Restore the data and view the records of the table T1

mysql> source backup.sql;

8. Execute the exported file from the binary log and view the results

mysql> source bin.sql;

9. Full recovery of data

Note: Read the contents of the binary log before restoring the database, because restoring the database results in a binary log.
Or, set the session to prohibit writing to the binary log set sql_log_bin=0 before the restore.

Full recovery of MySQL 5.7 database via mysqldump