To simulate mysql Backup and recovery of database , This document will introduce the creation of database 、 Table creation 、 Insert data into 、 Full database backup 、 Log segmentation 、 Data recovery, etc

1、 Database creation

Log in to the database ：

[root@zeshintax02 ~]# mysql -uroot –p

Create database tb1, surface ttab01：

mysql> create database tb1 default charset utf8mb4;

Query OK, 1 row affected (0.01 sec)

mysql> use tb1

Database changed

mysql> create table ttable01(

    -> id int auto_increment primary key,

    -> name varchar(15)

    -> )engine = InnoDB;

Query OK, 0 rows affected (0.02 sec)

2、 insert data ：

3、 Use mysqldump Full backup data ：

【 notes 】:  Usually, when using the full backup command, you need to add parameters ：

Mysqldump -uroot -p --single-transaction --master-data=2 --routines --flush-logs -B --all-databases > fullbackupfile.sql

Parameters --single-transaction、 --master-data=2 Represents a consistent backup , Generally used together , Not only .--routines Backup function trigger 、--flush-logs Refresh the log before backup （ Ensure complete data backup , If the database has binary logs , You do not need to use this parameter and consistency parameter --single-transaction、 --master-data=2） Parameters -B： The biggest feature is the addition of the library , There is no need to create a library during data recovery .

4、 Continue inserting data ：

5、 Use mysqlbinlog Check the log

mysqlbinlog You can view the contents of the database binary log file , There are statements for inserting data . view log file ：

6、mysqladmin Split log

To see the changes in the database , Use mysqladmin Options in command flush-logs Split the newly generated binary file of the database , In this way, after writing new data , The new binary file corresponds to the changed content of the database .

Check whether the binary log file is added ：

Insert a new data ;

Use mysqlbinlog Commands can be viewed mysql-bin.000003 Contents of database binary log file , In it, the newly inserted data statement is saved ：

Reuse mysqladmin Options in flush-logs Split the database binary log file ：

Because the database has not been added, deleted or modified, new data , Therefore, the binary log file after segmentation has no relevant content .

Insert a piece of data ：

Then check the binary log file ：

The new binary log file holds the newly inserted content , Above .

Two 、 Restore data

1、 Partial data recovery after full backup

            （1） Analog data loss ： Use delete Delete the two pieces of data inserted

         You can see that there are two pieces of data missing in the table .

        (2) Use the binary log file generated before to recover the data ,（ Need to pay attention to recovery order , The earliest data needs to recover the corresponding binary file first ）：

         You can view a piece of data that has been successfully restored , Continue to restore another ：

This completes the recovery of two pieces of data .

2、 Data recovery after full backup

        （1）、 View data ：

         After log segmentation , The list of binary files is as follows ：

 

Insert a piece of data ：

Do log segmentation , And look at the resulting new binary ：

Insert a piece of data again ：

Check the corresponding binary file , It is found that the written data has been recorded ：

Delete table , Then use the full set + Incremental backup to restore data ：

Now data recovery ：

        A、 Full data recovery first ：

You can find that the table is restored , View table data ：

The table saves the data during full standby , Then restore the incremental log .

        B、 Incremental data recovery , Until the last file ：

【 notes 】： When restoring incremental logs , In case of an error, you must solve it before you can restore the next file , Otherwise, the data may be incomplete .