A simple tutorial on using Mysqlbinlog flashback in Mysql _mysql

Brief introduction:
Mysqlbinlog Flashback function is a very strong work of Taobao Peng Lixun (http://www.penglixun.com/).
Main function: The Binlog in the rows format can be operated in reverse. Delete reverse generates INSERT, Update generates reverse Update,insert reverse generates delete. Let the DBA students also have a chance to simply recover the data. Recoverable: INSERT, update,delete related operation.

Demonstrate the use process:

Build with Flashback Mysqlbinlog tool:

Home: Http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog

Ready MySQL-5.5.18 source code, here with the 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

That is, you can see the mysqlbinlog, because this is only to mysqlbinlog this program, so when compiling MySQL did not add special parameters. Does the tool have the flashback function to confirm that there is a "-B" parameter.

Start 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 | About Us |
+------------------+-----------+

Tips:
Locate the daily value, generally look at the current log if the current log file is relatively small, then it is the previous file as to why, here is not said. You can also use Mysqlbinlog to actually see to confirm.

The next step is to find the range of changes that the delete position in the log. This is no trick, 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 and the corresponding node is found. Large table deletion usually the last stop point is at the end of the file. Once you have found 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 this file with B11.sql. Look at the end as expected. After you verify OK, you can:

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

If the table is larger, execution is slower. Please be patient if you don't make mistakes. After completion of execution:

Mysql>select Count (*) from Pic_url;
+----------+
| count (*) |
+----------+
| 786476 |
+----------+
1 row in Set (0.11 sec)

The data has been restored again.

Attention:
to prevent the recovery of the error need to: Max_allowed_packet to the maximum 1G;
Mysql>set Global max_allowed_packet=1024*1024*1024;

#max_allow_packet大小不够时报错如下:
ERROR 1153 (08s01) at line 403133:got a packet bigger than ' Max_allowed_packet ' bytes

Recovery operations are risky, either in a repository operation or under the guidance of an experienced classmate.

Binary files are not uploaded to the server and uploaded to GitHub on the previous binary: Https://github.com/wubx/mysql-binlog-statistic/tree/master/bin was compiled in 64 bit Linux. There is a need for direct downloads.