MySQL uses binary logs to restore Data

For the use of the mysqlbinlog tool, you can refer to the MySQL help manual. It has detailed usage,

In this example, the focus is on the use of the -- start-position parameter and -- stop-position parameter.

 

· -- Start-position = N

 

Read from the event where the nth position in the binary log is equal to n.

 

· -- Stop-position = N

 

Stop reading from the event where the first position in the binary log is equal to or greater than N.

 

 

 

OK. Now, to start the binary log record, add it in mysqld Of My. CNF/My. ini file.

 

Log-bin = Log name

 

Here, log-bin = liangck is used.

 

Then start the MySQL service. Because the Windows system is used, run the Net start MySQL command.

 

 

 

Create a table in a test database and add records.

 

Mysql> Create Table Test (ID int auto_increment not null primary key, Val int, data varchar (20 ));

 

Mysql> insert into test (Val, data) values (10, 'shanghai ');

 

Query OK, 1 row affected (0.03 Sec)

 

Mysql> insert into test (Val, data) values (20, 'jia ');

 

Query OK, 1 row affected (0.08 Sec)

 

Mysql> insert into test (Val, data) values (30, 'hui ');

 

Query OK, 1 row affected (0.03 Sec)

 

Mysql> flush logs; -- generate the second log file

 

Query OK, 0 rows affected (0.09 Sec)

 

Mysql> insert into test (Val, data) values (40, 'aaa ');

 

Query OK, 1 row affected (0.05 Sec)

 

Mysql> insert into test (Val, data) values (50, 'bbb ');

 

Query OK, 1 row affected (0.03 Sec)

 

Mysql> insert into test (Val, data) values (60, 'ccc ');

 

Query OK, 1 row affected (0.03 Sec)

 

Mysql> Delete from test where ID between 4 and 5; -- delete a record

 

Query OK, 2 rows affected (0.05 Sec)

 

Mysql> insert into test (Val, data) values (70, 'ddd ');

 

Query OK, 1 row affected (0.03 Sec)

 

Mysql> flush logs; -- generate the third file

 

Query OK, 0 rows affected (0.11 Sec)

 

Mysql> insert into test (Val, data) values (80, 'ddddd ');

 

Query OK, 1 row affected (0.05 Sec)

 

Mysql> insert into test (Val, data) values (90, 'eeeee ');

 

Query OK, 1 row affected (0.03 Sec)

 

Mysql> drop table test; -- delete a table

 

Query OK, 0 row affected (0.05 Sec)

 

――――――――――――――――――――――――――――――――――

 

OK. Now the test data has been created. What are the requirements?

 

That is, all the data in the test table is restored.

 

Use mysqlbinlog to generate a TXT 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 a file under disk g, which records the log file content, that is, the user operation steps.

 

Because we need to redo all the operations on the first log file, we only need to restore the first log file.

 

F:/program files/mysql_data/data/log> mysqlbinlog liangck.000001 | mysql-uroot-P

 

OK. Next, we need to analyze the second log file. Why do we need to analyze it, because it executes a delete operation halfway, because what we need 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 bypass it.

 

Open the. txt file for analysis.

 

/*

 

 

/*! 40019 set @ session. max_insert_delayed_threads = 0 */;

 

/*! 50003 set @ old_completion_type = @ completion_type, completion_type = 0 */;

 

Delimiter /*! */;

 

# At 4

 

#090427 15:27:56 server Id 1 end_log_pos 106 start: binlog v 4, server v 5.1.32-Community-log created 090427 15:27:56

 

Binlog'

 

Bytes

 

Aaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaauwaegggaaaaicagc

 

'/*! */;

 

# At 106

 

#090427 15:28:37 server Id 1 end_log_pos 176 query thread_id = 1 exec_time = 0 error_code = 0

 

Use mytest /*! */;

 

Set timestamp = 1240817317 /*! */;

 

Set @ session. pseudo do_thread_id = 1 /*! */;

 

Set @ session. foreign_key_checks = 1, @ session. SQL _auto_is_null = 1, @ session. unique_checks = 1, @ session. autocommit = 1 /*! */;

 

Set @ session. SQL _mode = 1344274432 /*! */;

 

Set @ session. auto_increment_increment = 1, @ session. auto_increment_offset = 1 /*! */;

 

/*! /C gbk *//*! */;

 

Set @ session. character_set_client = 28, @ session. collation_connection = 28, @ session. collation_server = 28 /*! */;

 

Set @ session. lc_time_names = 0 /*! */;

 

Set @ session. collation_database = default /*! */;

 

Begin

 

/*! */;

 

# At 176

 

#090427 15:28:37 server Id 1 end_log_pos 204 intvar

 

Set insert_id = 4 /*! */;

 

# At 204

 

#090427 15:28:37 server Id 1 end_log_pos 312 query thread_id = 1 exec_time = 0 error_code = 0

 

Set timestamp = 1240817317 /*! */;

 

Insert into test (Val, data) values (40, 'aaa ')

 

/*! */;

 

# At 312

 

#090427 15:28:37 server Id 1 end_log_pos 339 Xid = 12

 

Commit /*! */;

 

# At 339

 

#090427 15:28:46 server Id 1 end_log_pos 409 query thread_id = 1 exec_time = 0 error_code = 0

 

Set timestamp = 1240817326 /*! */;

 

Begin

 

/*! */;

 

# At 409

 

#090427 15:28:46 server Id 1 end_log_pos 437 intvar

 

Set insert_id = 5 /*! */;

 

# At 437

 

#090427 15:28:46 server Id 1 end_log_pos 545 query thread_id = 1 exec_time = 0 error_code = 0

 

Set timestamp = 1240817326 /*! */;

 

Insert into test (Val, data) values (50, 'bbb ')

 

/*! */;

 

# At 545

 

#090427 15:28:46 server Id 1 end_log_pos 572 Xid = 13

 

Commit /*! */;

 

# At 572

 

#090427 15:29:35 server Id 1 end_log_pos 642 query thread_id = 1 exec_time = 0 error_code = 0

 

Set timestamp = 1240817375 /*! */;

 

Begin

 

/*! */;

 

# At 642

 

#090427 15:29:35 server Id 1 end_log_pos 670 intvar

 

Set insert_id = 6 /*! */;

 

# At 670

 

#090427 15:29:35 server Id 1 end_log_pos 778 query thread_id = 1 exec_time = 0 error_code = 0

 

Set timestamp = 1240817375 /*! */;

 

Insert into test (Val, data) values (60, 'ccc ')

 

/*! */;

 

# At 778

 

#090427 15:29:35 server Id 1 end_log_pos 805 Xid = 14

 

Commit /*! */;

 

# At 805

 

#090427 15:30:21 server Id 1 end_log_pos 875 query thread_id = 1 exec_time = 0 error_code = 0

 

Set timestamp = 1240817421 /*! */;

 

Begin

 

/*! */;

 

# At 875

 

#090427 15:30:21 server Id 1 end_log_pos 981 query thread_id = 1 exec_time = 0 error_code = 0

 

Set timestamp = 1240817421 /*! */;

 

Delete from test where ID between 4 and 5

 

/*! */;

 

# At 981

 

#090427 15:30:21 server Id 1 end_log_pos 1008 Xid = 15

 

Commit /*! */;

 

# At 1008

 

#090427 15:30:34 server Id 1 end_log_pos 1078 query thread_id = 1 exec_time = 0 error_code = 0

 

Set timestamp = 1240817434 /*! */;

 

Begin

 

/*! */;

 

# At 1078

 

#090427 15:30:34 server Id 1 end_log_pos 1106 intvar

 

Set insert_id = 7 /*! */;

 

# At 1106

 

#090427 15:30:34 server Id 1 end_log_pos 1214 query thread_id = 1 exec_time = 0 error_code = 0

 

Set timestamp = 1240817434 /*! */;

 

Insert into test (Val, data) values (70, 'ddd ')

 

/*! */;

 

# At 1214

 

#090427 15:30:34 server Id 1 end_log_pos 1241 Xid = 16

 

Commit /*! */;

 

# At 1241

 

#090427 15:30:41 server Id 1 end_log_pos 1282 rotate to liangck.000003 pos: 4

 

Delimiter;

 

# End of Log File

 

Rollback/* added by mysqlbinlog */;

 

/*! 50003 set completion_type = @ old_completion_type */;

 

 

 

―――――――――――――――――――――――――――――――――――――

 

*/

 

 

In this file, we can see that the start position of the delete operation is, and the end position is. then, we only need to redo the operations starting with the second log file, and then from the end, we can restore the data without deleting the data. Therefore, 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 data in the second log file is ready.

 

The third log file is the same. You only need to find the drop table location.

 

F:/program files/mysql_data/data/log> mysqlbinlog liangck.000003 -- stop-Pos = 574 | mysql-uroot-P

 

 

 

Now let's look at the data again:

 

Mysql> select * from test;

 

+ ---- + ------ + ------- +

 

| 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 rows in SET (0.00 Sec)

 

 

 

We can see that all the data is back.