Using mysqldump and binlog to backup and restore database

Time:2020-8-9

Write it at the front

Mysqldump is used to back up data, perform full database backup or single database backup.

Binlog is used for incremental backup. The premise of incremental backup is based on full backup.

In other words, a complete recovery requires mysqldump backup file + binlog to restore data correctly.

———————————————————————————————————————————————————————————

1、 Use mysqldump to backup the whole database + binlog backup and restore.
Note: the binlog format here is mixed. I feel it is better to set it to mixed format.
1. First, create the test library and the corresponding tables tb1 and TB2
Using mysqldump and binlog to backup and restore database
2. Full database backup
Using mysqldump and binlog to backup and restore database
3. At this time, we are adding some data to the TB2 table, and the inserted records are recorded in binlog.
Using mysqldump and binlog to backup and restore database
4. At this time, we simulate the error deletion operation and update the whole table of TB2
Using mysqldump and binlog to backup and restore database
5. Check the current binlog file name [at this time, it is recommended to refresh binlog — > flush logs]. This update is also recorded in binlog. I did not refresh binlog in this test. If binlog is refreshed, the corresponding operation should be in the previous binlog file.
Using mysqldump and binlog to backup and restore database
At this point, we conduct data recovery
、、、
Recovery idea: recover the test library from the full database backup, and use binlog for incremental data recovery.
、、、

1. Restore the specified database in full backup
Restore the specified database from mysqldump full backup, and use the parameter – one-database to abbreviate – O, which greatly facilitates the flexibility of data recovery
mysql -uroot [email protected] -o test < all.sql

The view data has been restored to the state before using mysqldump backup.
Using mysqldump and binlog to backup and restore database
2. After incremental backup using binlog, the data of fields 7, 8 and 9 are recovered
Log in the data and view the events of binlog
Using mysqldump and binlog to backup and restore database
Using mysqldump and binlog to backup and restore database

We see that the update operation is performed at position 2304, so we resume from start position = 123 to stop position = 2132, that is, skip the update operation. When mysqldump to the time when the binlog is generated, all operations are played back so that.
Use mysqlbinlog to specify the start POS and end POS for recovery.

Using mysqldump and binlog to backup and restore database

So we can recover the data.

2、 Now we are testing using mysqldump for single database backup + binlog incremental backup for data test.

1. First, as before, create two tables tb1 and TB2
Using mysqldump and binlog to backup and restore database
2. Then we are doing a single database backup
Using mysqldump and binlog to backup and restore database
In this way, we have such a backup for the test library (consistent with the backup strategy of the production environment)
3. Similarly, we perform data insertion and full table update operations.
Using mysqldump and binlog to backup and restore database

Using mysqldump and binlog to backup and restore database
At this point, we simulate the error deletion operation, and at this time we refresh the binlog (the reason is that the error deletion operation is terminated to this binlog, so that we can find information)
4. Let’s recover the data.
Using mysqldump and binlog to backup and restore database
At this point, we can see that part of the data has been recovered, which is based on the backup and recovery of mysqldump.
Next, we are using the binlog log log for data recovery
5. Log in to the database and view the events of binlog
Using mysqldump and binlog to backup and restore database
Using mysqldump and binlog to backup and restore database

We see that the update operation is performed at position 7485, so we resume from start position = 123 to stop position = 7313, that is, skip the update operation. When mysqldump to the time when the binlog is generated, all operations are played back so that.
Use mysqlbinlog to specify the start POS and end POS for recovery.
Using mysqldump and binlog to backup and restore database
At this point, the data has been fully recovered.

3、 When using mysqldump to backup data, we are all backing up the whole database or single database. In the actual production environment, we usually do the backup in these two ways. I understand that the backup is relatively rough. No refinement to table.
But in general, the operations we delete by mistake are basically aimed at the table or the fields in the table.
Therefore, I have tested that the backup single library is used for backup, but the tables with problems are extracted from the library during recovery, and then the recovery is performed. Finally, the data is restored by binlog.
1. First, there are two tables tb1 and TB2 in the test library

Using mysqldump and binlog to backup and restore database

2. At this point, we perform a single database backup, that is, the backup database test
Using mysqldump and binlog to backup and restore database

3. Insert data into TB2 table and delete data by mistake (full table update)

Using mysqldump and binlog to backup and restore database

Using mysqldump and binlog to backup and restore database

The binlog is refreshed
Using mysqldump and binlog to backup and restore database

4. In this case, we will first backup from a single database test.sql Take out the table TB2. Use sed – n

sed -n -e ‘/Table structure for table tb2/,/UNLOCK TABLES/p’ test.sql > tb2_$(date +%F).sql

Using mysqldump and binlog to backup and restore database

Then we are recovering (in the same way as before, but the data files are changed later)

Using mysqldump and binlog to backup and restore database

5. At this time, we are using binlog for incremental recovery, and enter the database to view binlog events.
mysql> show binlog events in “mysql-bin.000010”;

Using mysqldump and binlog to backup and restore database

We see that the update operation is performed at the position position of 16556, so we resume from start position = 123 to stop position = 16384, that is, skip the update operation. When mysqldump to the time when the binlog is generated, all operations are played back so that.
Use mysqlbinlog to specify the start POS and end POS for recovery.
Using mysqldump and binlog to backup and restore database
At this point, the data has been recovered.

summary

The database backup uses mysqldump + binlog to backup the database in a full incremental mode.

No matter you use mysqldump for full database, single database and single table (this type has not been tested, but it feels OK). Can restore the database. In addition to the incremental backup of binlog, incremental restore can complete the database migration, that is, in case of misoperation, this method can be used to try to recover.