MySQL truncate delete records recovery notes

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:

The code is as follows	Copy Code
CreateTable ' TB_WUBX ' (' ID ' INT (one) notnullauto_increment, ' name ' VARCHAR defaultnull,primarykey (' id ') engine= InnoDB Auto_increment=2default 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:

The code is as follows	Copy Code
-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:

The code is as follows	Copy Code
Mysql>select*from TB_WUBX; +----+-----------+| ID | Name |+----+-----------+|1| Lao Zhao Zhao |+----+-----------+1rowinset (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 | Old King Next Door | +--+ ———-+

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

code is as follows	copy code
+--+ ———-+ | ID | name | +--+ ———-+ | 1 | john | +--+ ———-+ | 2 | dick | +--+ ———-+ | 3 | next door Old King | +--+ ———-+ | 4 | old Zhao Zhao | +--+ ———-+

And on-line recovery that table and the order developers know that the original ID and cache and other places are dependent, 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, and then use Show Binlog event in ' binary log file '; View the contents of the log file to find out where the truncate occurred.
also because based on the backup (from the log start position) or from the volume log, if based on the backup has log start position, we need to process the log file is the starting position to the occurrence of Truncate day value (the following data processing is not, A primary build conflict error causes the TRUNCATE data to be unrecoverable, and
if it is a full volume log, it needs to be processed from the log after the MySQL post library to the current occurrence truncate (the data will not be written in the main build 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, procedure omitted)
Author: Wu Bingxi Source: http://www.mysqlsupport.cn/Contact: wubingxi# gmail.com reproduced please indicate the translator and the source, and can not be used for commercial purposes, offenders must investigate.

The code is as follows	Copy Code
Mysql>showbinary logs; +------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 |143| | mysql-bin.000002 |261| | mysql-bin.000003 |562| | mysql-bin.000004 |1144|+------------------+-----------+4rowsinset (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.

The code is as follows

Copy Code

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 '; Truncatetable 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         &nbsP; |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 '; Truncatetable 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 */|+------------------+------+-------------+-----------+-------------+------------------------ ----------------------------+19rowsinset (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:

The code is as follows	Copy Code
Mysqlbinlog--rewrite-db= ' wubx->re_wubx '--start-position=4--stop-position=633 mysql-bin.000004/ Tmp/mysql.sock RE_WUBX

The results of the recovery are as follows:

The code is as follows

Copy Code

mysql-s/tmp/ Mysql.sock RE_WUBX; Mysql>selectcount (*) from TB_WUBX; +----------+| COUNT (*) |+----------+|3|+----------+1rowinset (0.02 sec)   Mysql>select*from tb_wubx; +----+---- ----------+| ID | Name |+----+--------------+|1| John | | 2| Dick | | 3| Next door Lao Wang |+----+--------------+3rowsinset (0.00 sec)   Mysql>insertinto tb_wubx (name) SELECT name from WUBX.TB_WUBX; Query Ok,1row Affected (0.00 sec) Records:1 duplicates:0 warnings:0   mysql>renametable wubx.t B_WUBX to WUBX.BAK_TB_WUBX; Query Ok,0rows Affected (0.04 sec)   mysql>renametable re_wubx.tb_wubx to WUBX.TB_WUBX; Query Ok,0rows 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 |+----+--------------+4rowsinset (0.00 sec)

Restore completed.
Think about it, what if I skip that truncate and go ahead and execute those binlog?

Summary: From this data loss to get some feelings after the deletion we still need to regularly back up the database, so as to ensure that our data is not lost, but also to ensure that our website data security.