MySQL uses binary logs to recover data

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

Starts reading from an event in the binary log where the first position equals the n parameter.

--stop-position=n

Stops reading from the event that the first position in the binary log is equal to and greater than the N parameter.

OK, now start, to start the binary logging, first in the My.cnf/my.ini file mysqld to add

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 execute the net start MySQL command.

Then, in a test database, create a table 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 (' Liang ');

Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO Test (Val,data) VALUES ("Jia");

Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO Test (Val,data) VALUES (' hui ');

Query OK, 1 row affected (0.03 sec)

mysql> flush logs; --Generate a second log file

Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO Test (Val,data) values (+, ' AAA ');

Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Test (Val,data) VALUES ("BBB");

Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO Test (Val,data) VALUES ("CCC");

Query OK, 1 row affected (0.03 sec)

mysql> Delete from test where ID between 4 and 5; --Delete records

Query OK, 2 rows affected (0.05 sec)

mysql> INSERT INTO Test (Val,data) VALUES ("ddd");

Query OK, 1 row affected (0.03 sec)

mysql> flush logs; --Generate a third file file

Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO Test (Val,data) VALUES ("dddd");

Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Test (Val,data) VALUES ("Eeee");

Query OK, 1 row affected (0.03 sec)

mysql> drop table test; --Delete Table

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

Through these three commands, you can generate a file under the G disk, which records the contents of the log file, that is, the steps of user operation.

Because we need to redo all the operations of the first log file, all we need to do is restore 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 an operation in the middle is delete, because we have to do is to recover all the data, that is, we do not want to redo the statement. So here we have to find a way to get around it.

Let's start by opening the. txt file to analyze it.

/*

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

/*!50003 SET @[email protected] @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 '

Ff71sq8baaaazgaaagoaaaaaaaqans4xljmylwnvbw11bml0es1sb2caaaaaaaaaaaaaaaaaaaaa

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_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 (+, ' 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 (' 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 ("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 ("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 [email protected]_completion_type*/;

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

*/

In this file, we can see that the start of the operation of the delete is that the terminating position is. So we just redo the beginning of the second log file to the operation, and then from the end of the operation, we can restore the data back, and not delete the 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:

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)

As you can see, all the data is back.

MySQL uses binary logs to recover data