MySQL uses binary logs to restore data _ MySQL

MySQL uses binary logs to restore data using 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.