A simple tutorial for using mysqlbinlog flashback in MySQL

Brief introduction:
mysqlbinlog flashback function is taobao Peng Lixun (http: / / www. penglixun. com /) of a very strong work.
Main features: binlog in rows format can be reversed. delete generates insert in reverse, update generates update in reverse, and insert generates delete in reverse. dba students also have the opportunity to simply restore the data. Recoverable :insert, update,delete related operations.

Use process in Demo 1:

Generate with flashback mysqlbinlog tool:

Check the home page: http: / / mysql taobao. org/index php/Patch_source_code # Add_flashback_feature_for_mysqlbinlog

Ready es45EN-5.5.18 source code, here with Percona-MySQL-5.5.18 source code
cd mysql-5.5.18
wget http://mysql.taobao.org/images/0/0f/5.5.18_flashback.diff
patch -p0 < 5.5.18_flashback.diff

You can see mysqlbinlog, because this is only for the program mysqlbinlog, so the compilation of MySQL did not add special parameters. Whether the tool has flashback function can confirm whether there is "-ES63en" parameter under 1.

Start the experiment:

mysql test
mysql> select count(*) from pic_url;
+----------+
| count(*) |
+----------+
| 786476 |
+----------+
1 row in set (0.11 sec)
mysql>delete from pic_url;
Query OK, 786476 rows affected (22.81 sec)
mysql>select count(*) from pic_url;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.03 sec)

mysql>show binary logs;
...
| mysql-bin.000011 | 195001447 |
| mysql-bin.000012 | 106 |
+------------------+-----------+

Tips:
If the current log file is small, then it's the last file and I won't tell you why. You can also use mysqlbinlog to actually check and confirm 1.

The next step is to find the interval between delete and position in log. This is not a tricky one and is usually used:

./mysqlbinlog -v --base64-output=decode-rows 　/u1/mysql/logs/mysql-bin.000011 >11.sql

The 11.sql file is then searched for the table name to find the node. Large table deletions usually have the last stop point at the end of the file. After finding the node, you can:

./mysqlbinlog -v --base64-output=decode-rows -B --start-position=377 --stop-position=195001377 /u1/mysql/logs/mysql-bin.000011>B11.sql

Also verify the file B11.sql 1. Let's see if the end is exactly what we expected. After verifying OK, you can:

./mysqlbinlog -B --start-position=377 --stop-position=195001377 /u1/mysql/logs/mysql-bin.000011|mysql test

If the table is large, the execution is slow. If there is no mistake, please wait patiently. After completion of execution:

mysql>select count(*) from pic_url;
+----------+
| count(*) |
+----------+
| 786476 |
+----------+
1 row in set (0.11 sec)

The data came back.

Note:
To prevent error recovery, max_allowed_packet should be changed to the maximum value of 1G;
mysql > set global max_allowed_packet=1024*1024*1024;

#max_allow_packet size is not enough
ERROR 1153 (08S01) at line 403133: Got a bigger than 'max_allowed_packet' bytes

Recovery operation is risky. Please do it in the backup operation or under the guidance of an experienced student.

Hexadecimal file upload on the server, 2 to 1 2 base on github file: https: / / github com wubx/mysql - binlog - statistic/tree master/bin linux system compiled in 64. Direct download if necessary.