MySQL binary log recovery data

1. Turn on MySQL binary log

In the MySQL configuration file My.ini, add:

Log-bin=mysql-bin (this name can be taken casually, English, do not know Chinese is not possible, have not tried)

2. Restart MySQL

After rebooting, if a file appears in the directory where the data is stored in MySQL, the binary log is already open

　　

　　mysql-bin.000001 is a MySQL binary log file that can not be viewed directly, can be viewed by exporting data, the statement that exports the data is

　　

　explain: Red down The line is the directory where the MySQL binary mysql-bin.000001 file is located, to enter here to execute the following statement, this is my case

syntax for Mysqlbinlog:

Mysqlbinlog binary log files > target Files

To export to where you decide, but must first have this directory, the exported file will be automatically generated MySQL, so do not have to create a new

3. View the binary log status

Show master status;

+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 349 | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

4. Test two examples

The first one:

CREATE TABLE Bin_test (

ID int,

Name Char (32)

) engine MyISAM charset UTF8;

drop table bin_test;

Note that the following statement has previously said that another cmd must be turned on, and then go into the directory of the mysql-bin.000001 binary file to execute

E:\itcast\gzclass14\gzbasicclass14\web\mysql\data\data>mysqlbinlog mysql-bin.000001 >D:/binlog/binlog.txt

View Binlog.txt File

e:\itcast\gzclass14\gzbasicclass14\web\mysql\data\data>mysqlbinlog mysql-bin.000001--stop_pos=239 | mysql- Uroot-p
Enter Password: * * *

Explain:--stop_pos=239 is the location of the Create statement above, the principle of MySQL binary recovery data is to let MySQL re-execute the binary inside the SQL statement, we do limit its execution position, let it in which location stop not down execution

A second example:

Because the bin_test has been restored, inserting data into it

Mysql> select * from Bin_test;
Empty Set (0.00 sec)

mysql> INSERT INTO bin_test values (1, ' hello ');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT into bin_test values (default, ' Hi ');
Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into bin_test values (default, ' Guangzhou ');
Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into bin_test values (default, ' lonely ');
Query OK, 1 row Affected (0.00 sec)

Mysql> select * from Bin_test;
+------+-----------+
| ID | name |
+------+-----------+
| 1 | Hello |
| NULL | Hi |
| NULL | guangzhou |
| NULL | Lonely |
+------+-----------+

Mysql> Delete from Bin_test;
Query OK, 4 rows affected (0.08 sec)

Mysql> select * from Bin_test;
Empty Set (0.00 sec)

　　viewing binary logs

CREATE TABLE Bin_test (ID int,name char (+)) engine MyISAM CharSet UTF8
/*!*/;
# at 481
#160917 15:27:45 Server ID 1 end_log_pos 549 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097265/*!*/;
BEGIN
/*!*/;
# at 549
#160917 15:27:45 Server ID 1 end_log_pos 650 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097265/*!*/;
INSERT into bin_test values (1, ' hello ')
/*!*/;
# at 650
#160917 15:27:45 Server ID 1 end_log_pos 719 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097265/*!*/;
COMMIT
/*!*/;
# at 719
#160917 15:28:00 Server ID 1 end_log_pos 787 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097280/*!*/;
BEGIN
/*!*/;
# at 787
#160917 15:28:00 Server ID 1 end_log_pos 891 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097280/*!*/;
INSERT into bin_test values (default, ' Hi ')
/*!*/;
# at 891
#160917 15:28:00 Server ID 1 end_log_pos 960 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097280/*!*/;
COMMIT
/*!*/;
# at 960
#160917 15:28:09 Server ID 1 end_log_pos 1028 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097289/*!*/;
BEGIN
/*!*/;
# at 1028
#160917 15:28:09 Server ID 1 end_log_pos 1139 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097289/*!*/;
INSERT into bin_test values (default, ' Guangzhou ')
/*!*/;
# at 1139
#160917 15:28:09 Server ID 1 end_log_pos 1208 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097289/*!*/;
COMMIT
/*!*/;
# at 1208
#160917 15:28:17 Server ID 1 end_log_pos 1276 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097297/*!*/;
BEGIN
/*!*/;
# at 1276
#160917 15:28:17 Server ID 1 end_log_pos 1384 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097297/*!*/;
INSERT into bin_test values (default, ' lonely ')
/*!*/;
# at 1384
#160917 15:28:17 Server ID 1 end_log_pos 1453 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097297/*!*/;
COMMIT
/*!*/;
# at 1453
#160917 15:29:11 Server ID 1 end_log_pos 1521 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097351/*!*/;
BEGIN
/*!*/;
# at 1521
#160917 15:29:11 Server ID 1 end_log_pos 1604 querythread_id=2exec_time=0error_code=0
SET timestamp=1474097351/*!*/;
Delete from Bin_test

Another CMD window executes the following statement

The corresponding point of the above at 1521:

However, this time to recover the data error, it is because we restore the data when the time to give an end, did not give the starting point, so MySQL will be back from the beginning to the end of the place, and the binary file already exists

This can compare the contents of the binary file above

Now that the MySQL database already exists in the Bin_test data table, this is our previous recovery, so SQL will report this error:

For this problem, we can set up a recovery point for it to solve:

E:\itcast\gzclass14\gzbasicclass14\web\mysql\data\data>mysqlbinlog mysql-bin.000001--start_pos=549--stop_pos =1521 | Mysql-uroot-p
Enter Password: * * *

E:\itcast\gzclass14\gzbasicclass14\web\mysql\data\data>

The starting position here is the first time to start inserting the SQL statement location, you can compare the previous binary log points in the point, here is no longer, there is no error indicating that it has been successful

MySQL binary log recovery data