1027mysqlbinlog Tool Log Recovery

--Transfer from http://bbs.csdn.net/topics/310068149
is to find the corresponding location and execute the statement

--First part output
Mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql-bin.000063" > D:\ab\01.txt

--Part II input to MySQL
Mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql-bin.000063" | mysql-uroot-p111111 back_db

Mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql-bin.000063" | mysql-uroot-p111111 back_db

237 1018 1170 1488
Mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql-bin.000064"--start-position 1170--stop-position 1488 | mysql-uroot-p111111 back_db

Mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql-bin.000064"--start-position 237--stop-position 1018 | mysql-uroot-p111111 back_db

237 664
Mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql-bin.000065"--start-position 237--stop-position 664 | mysql-uroot-p111111 back_db

For the use of Mysqlbinlog tools, you can see the Help manual for MySQL. There's a detailed use in it,
In this example, the emphasis is on the use of the--start-position parameter and the--stop-position parameter.

?--start-position=n
Start reading from the event that the 1th position in the binary log equals the n parameter.
?--stop-position=n
Stops reading from the event that the 1th position in the binary log is equal to and greater than the N parameter.

OK, now start, to start binary logging,
To add the mysqld in the My.cnf/my.ini file first
log-bin= Log Name
Here, I am the setting is Log-bin=liangck
Then start the MySQL service, because I am using the Windows system,
So the net start MySQL command can be executed.

Then, in a test database, create a table and add records.

SQL Code?

1234567891011121314151617181920212223242526272829303132333435363738394041

mysql> createtabletest(id intauto_increment notnullprimarykey,             val int,data varchar(20));mysql> insertintotest(val,data) values(10,‘liang‘);Query OK, 1 row affected (0.03 sec)mysql> insert intotest(val,data) values(20,‘jia‘);Query OK, 1 row affected (0.08 sec)mysql> insertintotest(val,data) values(30,‘hui‘);Query OK, 1 row affected (0.03 sec)mysql> flush logs;   --产生第二个日志文件Query OK, 0 rowsaffected (0.09 sec)mysql> insertintotest(val,data) values(40,‘aaa‘);Query OK, 1 row affected (0.05 sec)mysql> insertintotest(val,data) values(50,‘bbb‘);Query OK, 1 row affected (0.03 sec)mysql> insertintotest(val,data) values(60,‘ccc‘);Query OK, 1 row affected (0.03 sec)mysql> deletefromtest whereid between4 and5;  --删除记录Query OK, 2 rowsaffected (0.05 sec)mysql> insertintotest(val,data) values(70,‘ddd‘);Query OK, 1 row affected (0.03 sec) mysql> flush logs;          --产生第三个文件文件Query OK, 0 rowsaffected (0.11 sec)mysql> insertintotest(val,data) values(80,‘dddd‘);Query OK, 1 row affected (0.05 sec) mysql> insertintotest(val,data) values(90,‘eeee‘);Query OK, 1 row affected (0.03 sec)mysql> droptabletest;       --删除表Query OK, 0 row affected (0.05 sec)

――――――――――――――――――――――――――――――――――
OK, now that the test data has been built, what is the requirement?
is to recover all the data from the test table.

Use the Mysqlbinlog tool to generate a TXT file from the log file for analysis.

F:\Program files\mysql_data\data\log>
Mysqlbinlog liangck.000001 > G:\001.txt

F:\Program files\mysql_data\data\log>
Mysqlbinlog liangck.000002 > G:\002.txt

F:\Program files\mysql_data\data\log>
Mysqlbinlog liangck.000003 > G:\003.txt

With these three commands, you can generate 3 files under G disk,
The contents of the log file are recorded separately,
That is, the steps of the user operation.
Because we need to redo all the operations of the first log file,
So this is all it takes to recover the first log file.
F:\Program files\mysql_data\data\log>
Mysqlbinlog liangck.000001 | Mysql-uroot–p

Ok, and then we need to analyze the second log file. Why analyze It,
Because it performs one operation in the middle is delete, because all we have to do is restore all the data,
That is, we do not want to redo this statement. So here we have to find a way to get around it.
Let's start by opening the 002.txt file to analyze it.


In this file, we can see that the start position of the delete operation is 875, and the terminating position is 1008.
Then we just redo the start of the second log file to 875 operation, and then from 1008 to the end of the operation,
We can restore the data back without the delete data. So execute two commands:

F:\Program files\mysql_data\data\log>
Mysqlbinlog liangck.000002--stop-pos=875 | Mysql-uroot-p

F:\Program files\mysql_data\data\log>
Mysqlbinlog liangck.000002--start-pos=1008 | Mysql-uroot-p mytest


OK, now the second log file has data.
The third log file is also the same, as long as you find the location of the drop table.

F:\Program files\mysql_data\data\log>
Mysqlbinlog liangck.000003--stop-pos=574 | Mysql-uroot–p

Now let's look at the data and see:

SQL Code?

123456789101112131415

mysql> select* fromtest;+----+------+-------+| id | val  | data  |+----+------+-------+|  1 |   10 | liang ||  2 |   20 | jia   ||  3 |   30 | hui   ||  4 |   40 | aaa   ||  5 |   50 | bbb   ||  6 |   60 | ccc   ||  7 |   70 | ddd   ||  8 |   80 | dddd  ||  9 |   90 | eeee  |+----+------+-------+9 rowsinset(0.00 sec)

As you can see, all the data is back.

1027mysqlbinlog Tool Log Recovery