MySQL uses binary logs to recover data two ways

MySQL uses binary logs to recover data two ways

If the MySQL server has binary logs enabled, you can use the MySQL tutorial Binlog tool to recover data starting at a specified point in time (for example, from your last backup) until now or another specified point in time. For information about enabling binary logging, see section 5.11.3, "binary log." For more information about Mysqlbinlog, "Mysqlbinlog: a utility for processing binary log files."

To recover data from a binary log, you need to know the path and file name of the current binary log file. You can generally find the path from the option file (that is, my.cnf or My.ini, depending on your system). If it is not included in the option file.

The use of Mysqlbinlog tools, you can see the MySQL help manual. There's a detailed use in it,

In this example, the emphasis is on the use of--start-position parameters and--stop-position parameters.

--start-position=n

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

--stop-position=n

Stops reading from an event that is equal to or greater than the n parameter in the binary log.

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

log-bin= Log Name

Here, I'm setting it Log-bin=liangck

Then start the MySQL service, because I use the Windows system, so execute the net start MySQL command.

Then in a test database tutorial, create a table and add a record.

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; --Produces 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; --Deleting 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; --Produce 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 the test data has been built, what is the requirement?

is to recover all the data from the test table.

First use the Mysqlbinlog tool to generate the log file TXT file 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 files, which is the steps of user operation.

Because we need to redo all the operations of the first log file, we just need to restore the first log file as a whole.

F:/program Files/mysql_data/data/log>mysqlbinlog liangck.000001 | Mysql-uroot–p

Ok, then, what we need to analyze is the second log file. Why do you want to analyze it, because one of the operations in midstream is delete, because all we have to do is recover all the data, which means we don't 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 @OLD_COMPLETION_TYPE =@ @COMPLETION_TYPE, completion_type=0*/;

DELIMITER/*!*/;

# at 4

#090427 15:27:56 Server ID 1 end_log_pos 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. PS Tutorial eudo_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 completion_type= @OLD_COMPLETION_TYPE * *;

?????????????????????????????????????

*/

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

The third log file is 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 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 |

|   9 2 70 | DDD |

|   8 | 80 | dddd |

|   9 | 90 | eeee |

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

9 Rows in Set (0.00 sec)

Method Two

To recover data from a binary log, you need to know the path and file name of the current binary log file. You can generally find the path from the option file (that is, my.cnf or My.ini, depending on your system). If it is not included in the options file, it can be given as an option on the command line when the server is started. The option to enable binary logging is--log-bin. To determine the file name of the current binary log file, enter the following MySQL statement:

Show Binlog EVENTS G

You can also enter the following from the command line:

MySQL--user=root-pmy_pwd-e ' show binlog EVENTS G '

Replace the password my_pwd with the server's root password.

1. Specify recovery time

For MySQL 4.1.4, you can specify the start and end times of the DateTime format through the--start-date and--stop-date options in the Mysqlbinlog statement. For example, suppose that at 10:00 today (today is April 20, 2006), execute the SQL statement to delete a large table. To recover the tables and data, you can restore the previous night's backup and enter:

Mysqlbinlog--stop-date= "2005-04-20 9:59:59"/var/log/mysql/bin.123456

Mysql-u root-pmypwd

This command restores all data up to the date and time given in datetime format in the--stop-date option. If you do not detect the wrong SQL statement entered after a few hours, you may want to restore the activity that occurred later. Depending on these, you can use the date and time to run the Mysqlbinlog again:

Mysqlbinlog--start-date= "2005-04-20 10:01:00"/var/log/mysql/bin.123456

Mysql-u root-pmypwd

In this row, the SQL statement that logs in from 10:01 will run. The two rows of the dump file and Mysqlbinlog on the eve of the combined execution can restore all data to one second before 10:00. You should check the log to make sure the time is accurate. The next section describes how to implement.

2. Specify the recovery location

You can also specify the log location by using Mysqlbinlog options--start-position and--stop-position without specifying a date and time. They function the same as the start and end dates, and the difference is given the number of positions from the log. Using log locations is a more accurate method of recovery, especially when many transactions occur concurrently due to destructive SQL statements. To determine the location number, you can run Mysqlbinlog to find a time range that performs an unexpected transaction, but you should point the results back to a text file for inspection. The action method is:

Mysqlbinlog--start-date= "2005-04-20 9:55:00"--stop-date= "2005-04-20 10:05:00"

/var/log/mysql/bin.123456 >/tmp/mysql_restore.sql

This command creates a small text file in the/tmp directory and displays the SQL statement when the incorrect SQL statement is executed. You can open the file in a text editor and look for statements that you don't want to repeat. If the location number in the binary log is used to stop and resume the recovery operation, you should comment. Mark position with Log_pos plus a number. After restoring the previous backup file using the location number, you should enter the following from the command line:

Mysqlbinlog--stop-position= "368312"/var/log/mysql/bin.123456

Mysql-u root-pmypwd

Mysqlbinlog--start-position= "368315"/var/log/mysql/bin.123456

Mysql-u root-pmypwd

The 1th line above reverts to all transactions until the stop position. The next line restores all transactions from the given starting position to the end of the binary log. Because the output of the Mysqlbinlog includes the set TIMESTAMP statement before each SQL statement record, the recovered data and the associated MySQL log will react to the original time of the transaction execution