MySQL data recovery case after truncate misoperation, mysqltruncate

MySQL data recovery case after truncate misoperation, mysqltruncate

The actual online scenario is complicated. At that time, the truncate and delete operations were involved. It was confirmed that more than 70 thousand rows of data were lost. when the operation was stopped, more than 10 thousand rows of data were written. Here, we will give an example of a simple business scenario.

Test environment: Percona-Server-5.6.16
Log format: mixed does not use gtid

The table structure is as follows:

CREATE TABLE `tb_wubx` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 CREATE TABLE `tb_wubx` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

A backup or full binlog at a certain time point is a unique guarantee for data recovery. For example, our backup is a table structure creation statement, binlog pos information: mysql-bin.000004, 4, and then carried out the following:

-T1 time program write:

Insert into tb_wubx (name) values ('zhang san'), ('Li si'); insert into tb_wubx (name) values ('Next Old Wang ');

-Personnel errors at t2

truncate table tb_wubx;

-T3 time program write

Insert into tb_wubx (name) values ('old Zhao '); update tb_wubx set name = 'old Zhao' where id = 1;

Data in the current table:

Mysql> select * from tb_wubx; + ---- + ----------- + | id | name | + ---- + ----------- + | 1 | old zhao | + ---- + ----------- + 1 row in set (0.00 sec) mysql> select * from tb_wubx; + ---- + ----------- + | id | name | + ---- + ----------- + | 1 | old zhao | + ---- + ----------- + 1 row in set (0.00 sec)

As you can see, after the truncate table operation, the auto-increment id of the table is changed from 1. The original data written should be:

+ -- + ---- + | Id | name | + -- + ---- + | 1 | Zhang San | + -- + ---- + | 2 | Li Si | + -- + ---- + | 3 | Old Wang next door | + -- + ---- +

If the truncate table operation is not generated, the actual data should be:

+ -- + ---- + | Id | name | + -- + ---- + | 1 | Zhang San | + -- + ---- + | 2 | Li Si | + -- + ---- + | 3 | Old Wang next door | + -- + ---- + | 4 | Old Zhao | + -- + ---- +

In addition, the online recovery of the table is only known by the time and sequence developers. The original id and cache and other places have dependencies, because the id is messy, it will also cause program disorder. At this time, I fixed the Issue of id disorder at the program layer and left it to the developers to tell them what the recovery result is. Our customs task is to recover the data. Okay. The next step is to start restoring data from binlog.
Use: show binary logs; to view the log file distribution, and then use show binlog events in 'binary log file' to view the log file content, in order to find the log location where truncate occurs.
In addition, because the backup is based on the log start location or the log volume, if the backup is based on the start location of the log, the log file we need to process is the log file starting from the location to the day when the truncate occurs (the subsequent data cannot be processed, and a master-created conflict error will occur, causing the data cannot be recovered after the truncate ),
For full logs, you need to process the logs after the mysql database is created to the current truncate location (subsequent data will not be written due to master creation conflicts)
Prepare for recovery. Create a database for data recovery. Here, create a re_wubx and a table with the original structure: tb_wubx (equivalent to restoring the backup, and the process is omitted)

mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 143 || mysql-bin.000002 | 261 || mysql-bin.000003 | 562 || mysql-bin.000004 | 1144 |+------------------+-----------+4 rows in set (0.00 sec) mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 143 || mysql-bin.000002 | 261 || mysql-bin.000003 | 562 || mysql-bin.000004 | 1144 |+------------------+-----------+4 rows in set (0.00 sec)

Here I have a backup file that is the SQL statement for creating the table, location is mysql-bin.000004, 4
In this case, I only used to cover the mysql-bin.000004 file.

mysql>show binlog events in 'mysql-bin.000004';+------------------+------+-------------+-----------+-------------+----------------------------------------------------+| Log_name   | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+------+-------------+-----------+-------------+----------------------------------------------------+| mysql-bin.000004 | 4 | Format_desc | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 || mysql-bin.000004 | 120 | Query   | 753306 | 209 | use `wubx`; truncate table tb_wubx || mysql-bin.000004 | 209 | Query   | 753306 | 281 | BEGIN || mysql-bin.000004 | 281 | Table_map  | 753306 | 334 | table_id: 91 (wubx.tb_wubx) || mysql-bin.000004 | 334 | Write_rows | 753306 | 393 | table_id: 91 flags: STMT_END_F || mysql-bin.000004 | 393 | Xid   | 753306 | 424 | COMMIT /* xid=1073 */ || mysql-bin.000004 | 424 | Query   | 753306 | 496 | BEGIN || mysql-bin.000004 | 496 | Table_map  | 753306 | 549 | table_id: 91 (wubx.tb_wubx) || mysql-bin.000004 | 549 | Write_rows | 753306 | 602 | table_id: 91 flags: STMT_END_F || mysql-bin.000004 | 602 | Xid   | 753306 | 633 | COMMIT /* xid=1074 */ || mysql-bin.000004 | 633 | Query   | 753306 | 722 | use `wubx`; truncate table tb_wubx || mysql-bin.000004 | 722 | Query   | 753306 | 794 | BEGIN || mysql-bin.000004 | 794 | Table_map  | 753306 | 847 | table_id: 92 (wubx.tb_wubx) || mysql-bin.000004 | 847 | Write_rows | 753306 | 894 | table_id: 92 flags: STMT_END_F || mysql-bin.000004 | 894 | Xid   | 753306 | 925 | COMMIT /* xid=1081 */ || mysql-bin.000004 | 925 | Query   | 753306 | 997 | BEGIN || mysql-bin.000004 | 997 | Table_map  | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) || mysql-bin.000004 | 1050 | Update_rows | 753306 | 1113 | table_id: 92 flags: STMT_END_F || mysql-bin.000004 | 1113 | Xid   | 753306 | 1144 | COMMIT /* xid=1084 */ |+------------------+------+-------------+-----------+-------------+----------------------------------------------------+19 rows in set (0.00 sec) mysql>show binlog events in 'mysql-bin.000004';+------------------+------+-------------+-----------+-------------+----------------------------------------------------+| Log_name   | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+------+-------------+-----------+-------------+----------------------------------------------------+| mysql-bin.000004 | 4 | Format_desc | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 || mysql-bin.000004 | 120 | Query   | 753306 | 209 | use `wubx`; truncate table tb_wubx || mysql-bin.000004 | 209 | Query   | 753306 | 281 | BEGIN || mysql-bin.000004 | 281 | Table_map  | 753306 | 334 | table_id: 91 (wubx.tb_wubx) || mysql-bin.000004 | 334 | Write_rows | 753306 | 393 | table_id: 91 flags: STMT_END_F || mysql-bin.000004 | 393 | Xid   | 753306 | 424 | COMMIT /* xid=1073 */ || mysql-bin.000004 | 424 | Query   | 753306 | 496 | BEGIN || mysql-bin.000004 | 496 | Table_map  | 753306 | 549 | table_id: 91 (wubx.tb_wubx) || mysql-bin.000004 | 549 | Write_rows | 753306 | 602 | table_id: 91 flags: STMT_END_F || mysql-bin.000004 | 602 | Xid   | 753306 | 633 | COMMIT /* xid=1074 */ || mysql-bin.000004 | 633 | Query   | 753306 | 722 | use `wubx`; truncate table tb_wubx || mysql-bin.000004 | 722 | Query   | 753306 | 794 | BEGIN || mysql-bin.000004 | 794 | Table_map  | 753306 | 847 | table_id: 92 (wubx.tb_wubx) || mysql-bin.000004 | 847 | Write_rows | 753306 | 894 | table_id: 92 flags: STMT_END_F || mysql-bin.000004 | 894 | Xid   | 753306 | 925 | COMMIT /* xid=1081 */ || mysql-bin.000004 | 925 | Query   | 753306 | 997 | BEGIN || mysql-bin.000004 | 997 | Table_map  | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) || mysql-bin.000004 | 1050 | Update_rows | 753306 | 1113 | table_id: 92 flags: STMT_END_F || mysql-bin.000004 | 1113 | Xid   | 753306 | 1144 | COMMIT /* xid=1084 */ |+------------------+------+-------------+-----------+-------------+----------------------------------------------------+19 rows in set (0.00 sec)

When we see a truncate occurred at the beginning of the table, it actually means that the data between the statement of the truncate at the beginning and the statement of the truncate at the later misoperation is lost.
This recovery can be from mysql-bin.000004 pos: 4 to mysql-bin.000004 pos: 633 I .e:

mysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubxmysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx

The recovery result is as follows:

Mysql-S/tmp/mysql. sock re_wubx; mysql> select count (*) from tb_wubx; + ---------- + | count (*) | + ---------- + | 3 | + ---------- + 1 row in set (0.02 sec) mysql> select * from tb_wubx; + ---- + -------------- + | id | name | + ---- + -------------- + | 1 | Zhang San | 2 | Li Si | 3 | Old Wang next door | + ---- + ------------ + 3 rows in set (0.00 sec) mysql> insert into tb_wubx (name) select name from wubx. tb_wubx; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> rename table wubx. tb_wubx to wubx. bak_tb_wubx; Query OK, 0 rows affected (0.04 sec) mysql> rename table re_wubx.tb_wubx to wubx. tb_wubx; Query OK, 0 rows affected (0.03 sec) mysql> select * from wubx. tb_wubx; + ---- + -------------- + | id | name | + ---- + -------------- + | 1 | Zhang San | 2 | Li Si | 3 | Old Wang next door | 4 | old zhao | + ---- + -------------- + 4 rows in set (0.00 sec) mysql-S/tmp/mysql. sock re_wubx; mysql> select count (*) from tb_wubx; + ---------- + | count (*) | + ---------- + | 3 | + ---------- + 1 row in set (0.02 sec) mysql> select * from tb_wubx; + ---- + -------------- + | id | name | + ---- + -------------- + | 1 | Zhang San | 2 | Li Si | 3 | Old Wang next door | + ---- + ------------ + 3 rows in set (0.00 sec) mysql> insert into tb_wubx (name) select name from wubx. tb_wubx; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> rename table wubx. tb_wubx to wubx. bak_tb_wubx; Query OK, 0 rows affected (0.04 sec) mysql> rename table re_wubx.tb_wubx to wubx. tb_wubx; Query OK, 0 rows affected (0.03 sec) mysql> select * from wubx. tb_wubx; + ---- + -------------- + | id | name | + ---- + -------------- + | 1 | Zhang San | 2 | Li Si | 3 | Old Wang next door | 4 | old zhao | + ---- + -------------- + 4 rows in set (0.00 sec)

Recovery completed.