Mysql based on point-in-time recovery misoperation mysqlbinlog how to recover

Mysql based on point-in-time recovery misoperation mysqlbinlog recovery, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Mysqlbinlog

In addition to xtrabackup supporting physical incremental backups, incremental backups can be done directly by copying binary log files. Because binary log files are critical to rolling back misoperations at a point in time, it is recommended that binary log files be backed up regularly, at least once a day. Let's demonstrate how to back up binary logs and how to restore them on a point-in-time basis.

Backup

1. Analog insert data

Select * from sys_log;desc sys_log;insert into sys_log (log_id) values (1); select * from sys_log

2. View the current binary log file information

Mysql-hlocalhost-P3306-uroot-e "show master status"

You can see that the latest binary log file in use is mysql-bin.000006. The location point is 467

3. Simulation mistakenly deletes the newly inserted data

Select * from sys_log;delete from sys_log where log_id = 1

4. Simulate and insert another piece of data manually.

Insert into sys_log (log_id) values (2)

Note: the log_id of the newly inserted data is 2, and the log_id of the mistakenly deleted data is 1. Later, we need to recover the erroneous deletion in a point-in-time manner. After the recovery is successful, you should be able to see two pieces of data through the query.

5. Scroll the binary log file

Mysql-hlocalhost-P3306-uroot-e "flush logs"; mysql-hlocalhost-P3306-uroot-e "show master status"

You can see that after scrolling, the latest binary log file is mysql-bin.000007, and the new log will be written to mysql-bin.000007 instead of mysql-bin.000006, which makes it easy to back up the whole file.

6. Copy the binaries to the backup destination

Cp / usr/local/mysql/data/mysql-bin.000006 / opt/backup/mysql/physical/increment/ recovery

1. Based on the guiding ideology of recovery, the first step is to shut down the mysql external service.

Logical recovery cannot be stopped. External services are prohibited by configuring skip-networking.

2. Find the location of misoperation sql

Mysqlbinlog / opt/backup/mysql/physical/increment/mysql-bin.000006 | less

Where 625 is the beginning of the erroneous deletion statement, and 749 is the beginning of the next statement and the end of the erroneous deletion statement.

3. Start recovery

Mysqlbinlog-database=test_backup / opt/backup/mysql/physical/increment/mysql-bin.000006-stop-position=625 | mysql- urootmysqlbinlog-database=test_backup / opt/backup/mysql/physical/increment/mysql-bin.000006-start-position=749 | mysql- uroot

If there are other backed-up binaries, you need to replay them all, including the latest binaries currently in use.

4. Verify the recovery result

As you can see, the mistakenly deleted data has come back.

After reading the above, have you mastered the method of how to recover Mysql based on point-in-time recovery misoperation mysqlbinlog? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!