A case of data recovery after truncate misoperation in Mysql _mysql

The actual line of the scene is more complex, then involved in the TRUNCATE, delete two operations, confirmed lost data almost 70,000 lines, and so on stop, almost a total of more than 10,000 rows of data written. Here for a simple explanation, just get a simple example of a business scenario.

Test environment: percona-server-5.6.16
Log format: Mixed didn't employ gtid.

The table structure is as follows:

CREATE TABLE ' tb_wubx ' (
' id ' int ') NOT NULL auto_increment,
' name ' varchar ' DEFAULT null,
PRIMARY KEY (' Id ')
) engine=innodb auto_increment=2 DEFAULT charset=utf8
 
CREATE TABLE ' tb_wubx ' (
' id ' int () not NULL AUT O_increment,
' name ' varchar () default NULL,
PRIMARY KEY (' id ')
) engine=innodb auto_increment=2 Default Charset=utf8

Having a backup or a full amount of binlog based on a point in time is the only guarantee that data can be recovered. For example, our backup is a table structure creation statement, Binlog POS related information: mysql-bin.000004, 4, and then proceed as follows:

–t1 Time Program writes:

Insert into TB_WUBX (name) VALUES (' John '), (' Dick ');
Insert into TB_WUBX (name) values (' Next door Old King ');

–t2 time, some people are wrong.

TRUNCATE TABLE TB_WUBX;

–T3 Time Program Write

Insert into TB_WUBX (name) VALUES (' Lao Zhao ');
Update TB_WUBX set name= ' Old Zhao Zhao ' where id=1;

Data in the table now:

Mysql>select * from TB_WUBX;
+----+-----------+
| id | name |
+----+-----------+
| 1 | old Zhao Zhao |
+----+-----------+
1 row in Set (0.00 sec)
 
Mysql>select * from TB_WUBX;
+----+-----------+
| id | name |
+----+-----------+
| 1 | old Zhao Zhao |
+----+-----------+
1 row in Set (0.00 sec)

After you can see the TRUNCATE TABLE operation, the table's auto ID is changed to start at 1, and the data originally written should be:

+--+ ——— –+ |
ID | name
| +--+ ——— –+
| 1 | John
| +--+ ——— –+
| 2 | Dick
| +--+ ——— –+
| 3 | next Door Lao Wang |
+--+ ——— –+

If you do not have a TRUNCATE table operation, the actual data should be:

+--+ ——— –+ |
ID | name
| +--+ ——— –+
| 1 | John
| +--+ ——— –+
| 2 | Dick
| +--+ ——— –+
| 3 | next Door Lao Wang |
+--+ ——— –+
| 4 | old Zhao Zhao |
+--+ ——— –+

And the online recovery that table and the order developers know that the original ID and cache and other places have dependencies, because the ID is messy, can also cause program confusion. This time repair ID in the program layer of the disorder, left to the developers Guan Jian is to explain to them what the outcome of the recovery, our shutdown task is to restore the data. Well, the next step is to start recovering the data from the Binlog.
Use: Show binary logs; View the log file distribution, then use show Binlog events in ' binary log file '; View the contents of the log file to find the log location where truncate occurred.
In addition, because based on the backup (from the log start position) or from the volume log, if based on the backup has log starting position, we need to process the log file is the starting position to the occurrence of Truncate day value (the following data processing can not, A primary build conflict error causes the TRUNCATE data to be unrecoverable.
If it is a full volume log, it needs to be processed from the log after the MySQL library is created to the current occurrence truncate (the data will not be written in the main building conflict)
Resume preparation, create a library to restore the data, here creates a re_wubx, and the original structure of the table: TB_WUBX (equivalent to restore the backup, process 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)

I have a backup file here. The SQL statement that created the table, located in mysql-bin.000004, 4
In this case I only use cover to live mysql-bin.000004 this document.

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 * | +------------------+------+-------------+-----------+-------------+-------------------------------------------- --------+
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 * | +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ rows in Set (0.00 sec)

 

To see that this table has just begun a truncate, it can also explain that I resumed the first truncate to later that TRUNCATE TABLE of the error between the data is lost data.
This recovery can be from mysql-bin.000004 pos:4 to mysql-bin.000004 pos:633 namely:

Mysqlbinlog--rewrite-db= ' wubx->re_wubx '--start-position=4--stop-position=633 mysql-bin.000004 |mysql-s/tmp/ Mysql.sock re_wubx


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

The results of the recovery are 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 | John | | 2 | Dick | | 3 |
Old King Next Door |
+----+--------------+ 3 rows in Set (0.00 sec) Mysql>insert to 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 | John | | 2 | Dick | | 3 | Old King Next Door | | 4 |
Lao Zhao 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 | John | | 2 | Dick | | 3 |
Old King Next Door |
+----+--------------+ 3 rows in Set (0.00 sec) Mysql>insert to 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 | John | | 2 | Dick | | 3 | Old King Next Door | | 4 |
Lao Zhao Zhao |

 +----+--------------+ 4 rows in Set (0.00 sec)

Restore complete.