Using Mysqlbinlog to recover database data

For a detailed explanation of Binlog, please refer to:http://zlyang.blog.51cto.com/1196234/1833062

The Binlog log is used to record all statements that have updated data or that have potentially updated data. Statements are saved as "events," which describe data changes. When we fail the database for some reason, we can use the Binlog log to retrieve it (provided the Binlog is already configured), then we configure

First, open the Mysql-binlog log

In MySQL configuration file my.cnf Add the following configuration

[Mysqld]

Log-bin=mysql-bin

Binlog_do_db=bin_test

Restart MySQL

Service mysqld Restart

Second, back up the database

1) First check the current database and Binlog log situation:

mysql> show databases;

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

| Database |

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

| Information_schema |

| MySQL |

| Test |

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

3 Rows in Set (0.00 sec)

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

| mysql-bin.000001 | 106 | Bin_test | |

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

1 row in Set (0.00 sec)

2) Create library and data: Bin_test

mysql> CREATE DATABASE Bin_test;

Query OK, 1 row Affected (0.00 sec)

mysql> use bin_test;

Database changed

Mysql> CREATE TABLE ABC (ID int (TEN) primary key auto_increment,name varchar (255));

Query OK, 0 rows Affected (0.00 sec)

Mysql> INSERT INTO ABC (name) value (' Zhangsan ');

Query OK, 1 row Affected (0.00 sec)

Mysql> INSERT INTO ABC (name) value (' Lisi ');

Query OK, 1 row Affected (0.00 sec)

Mysql> INSERT INTO ABC (name) value (' Wangwu ');

Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * FROM ABC;

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

| ID | name |

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

| 1 | Zhangsan |

| 2 | Lisi |

| 3 | Wangwu |

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

3 Rows in Set (0.00 sec)

3) Back up data to/tmp/test.sql

[Email protected] ~]# Whereis mysqldump

Mysqldump:/usr/bin/mysqldump/usr/share/man/man1/mysqldump.1.gz

[Email protected] ~]#/usr/bin/mysqldump-uroot-p123456 bin_test >/tmp/test.sql

4) Look at the following Binlog log situation:

Mysql> show Binlog events in ' mysql-bin.000001 ';

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

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

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

| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver:5.1.73-log, Binlog ver:4 |

| mysql-bin.000001 | 106 | Query | 1 | 197 | Create Database Bin_test |

| mysql-bin.000001 | 197 | Query | 1 | 337 | Use ' bin_test '; CREATE TABLE ABC (ID int) PRIMARY key auto_increment,name varchar (255)) |

| mysql-bin.000001 | 337 | Intvar | 1 | 365 | insert_id=1 |

| mysql-bin.000001 | 365 | Query | 1 | 471 | Use ' bin_test '; INSERT into ABC (name) value (' Zhangsan ') |

| mysql-bin.000001 | 471 | Intvar | 1 | 499 | insert_id=2 |

| mysql-bin.000001 | 499 | Query | 1 | 601 | Use ' bin_test '; INSERT into ABC (name) value (' Lisi ') |

| mysql-bin.000001 | 601 | Intvar | 1 | 629 | insert_id=3 |

| mysql-bin.000001 | 629 | Query | 1 | 733 | Use ' bin_test '; INSERT into ABC (name) value (' Wangwu ') |

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

9 Rows in Set (0.00 sec)

Three, at this time analog error operation (delete database)

Suddenly the database is corrupted or artificially deleted.

mysql> drop Database bin_test;

Query OK, 1 row Affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| Information_schema |

| MySQL |

| Test |

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

3 Rows in Set (0.00 sec)

Check the following Binlog log for the operation of deleting the library:

Mysql> Show Binlog events in "mysql-bin.000001";

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

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

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

| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver:5.1.73-log, Binlog ver:4 |

| mysql-bin.000001 | 106 | Query | 1 | 197 | Create Database Bin_test |

| mysql-bin.000001 | 197 | Query | 1 | 337 | Use ' bin_test '; CREATE TABLE ABC (ID int) PRIMARY key auto_increment,name varchar (255)) |

| mysql-bin.000001 | 337 | Intvar | 1 | 365 | insert_id=1 |

| mysql-bin.000001 | 365 | Query | 1 | 471 | Use ' bin_test '; INSERT into ABC (name) value (' Zhangsan ') |

| mysql-bin.000001 | 471 | Intvar | 1 | 499 | insert_id=2 |

| mysql-bin.000001 | 499 | Query | 1 | 601 | Use ' bin_test '; INSERT into ABC (name) value (' Lisi ') |

| mysql-bin.000001 | 601 | Intvar | 1 | 629 | insert_id=3 |

| mysql-bin.000001 | 629 | Query | 1 | 733 | Use ' bin_test '; INSERT into ABC (name) value (' Wangwu ') |

| mysql-bin.000001 | 733 | Query | 1 | 822 | Drop Database Bin_test |

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

Rows in Set (0.00 sec)

You can see that action 20th is to delete the database.

Iv. The database has been completely destroyed at this time

1) restore using the Mysqlbinlog command, first confirm the correct position start and end values before restoring:

Mysql> Show Binlog events in "mysql-bin.000001";

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

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

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

| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver:5.1.73-log, Binlog ver:4 |

| mysql-bin.000001 | 106 | Query | 1 | 197 | Create Database Bin_test |

| mysql-bin.000001 | 197 | Query | 1 | 337 | Use ' bin_test '; CREATE TABLE ABC (ID int) PRIMARY key auto_increment,name varchar (255)) |

| mysql-bin.000001 | 337 | Intvar | 1 | 365 | insert_id=1 |

| mysql-bin.000001 | 365 | Query | 1 | 471 | Use ' bin_test '; INSERT into ABC (name) value (' Zhangsan ') |

| mysql-bin.000001 | 471 | Intvar | 1 | 499 | insert_id=2 |

| mysql-bin.000001 | 499 | Query | 1 | 601 | Use ' bin_test '; INSERT into ABC (name) value (' Lisi ') |

| mysql-bin.000001 | 601 | Intvar | 1 | 629 | insert_id=3 |

| mysql-bin.000001 | 629 | Query | 1 | 733 | Use ' bin_test '; INSERT into ABC (name) value (' Wangwu ') |

| mysql-bin.000001 | 733 | Query | 1 | 822 | Drop Database Bin_test |

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

Rows in Set (0.00 sec)

At this point we have confirmed that the "starting value" is the beginning of the creation of the library: 106; "Terminating value" is the end of the previous sentence when the drop library: 733

2) Use Mysqlbinlog for recovery:

Mysqlbinlog--no-defaults--start-position= "106"--stop-position= "733"/var/lib/mysql/mysql-bin.000001 |mysql-uroot -P

Enter Password:

3) Check to see if the data is restored successfully:

Onemysql> show databases;

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

| Database |

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

| Information_schema |

| Bin_test |

| MySQL |

| Test |

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

4 rows in Set (0.00 sec)

mysql> select * from BIN_TEST.ABC;

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

| ID | name |

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

| 1 | Zhangsan |

| 2 | Lisi |

| 3 | Wangwu |

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

3 Rows in Set (0.00 sec)

3) Successful Recovery

"Summary": MySQL Backup and Bin-log logs

Backup data:

mysqldump-uroot-p123456 bin_test-l-F '/tmp/test.sql '

-L: Read lock (read only, cannot update)

-F: Flush logs, you can regenerate new log files, including, of course, Log-bin logs

To view the Binlog log:

Mysql>show Master Status

If you have more data, first back up your data before importing:

mysql-uroot-p123456 bin_test-v-F </tmp/test.sql

-V View Import details

-F is when an error is encountered in the middle, you can skip the past, continue to execute the following statement

To restore the binlog-file binary log file:

Mysqlbinlog--no-defaults Binlog-file | mysql-uroot-p123456

Start recovery from a point (567):

Mysqlbinlog--no-defaults--start-position= "567" mysql-bin.000001| mysql-uroot-p123456 Test

Check that out first (use more to see)

[Email protected] mysql]#/usr/bin/mysqlbinlog--no-defaults mysql-bin.000001--start-position= "794"--stop-position = "1055" | More

Then restore:

[Email protected] mysql]#/usr/bin/mysqlbinlog--no-defaults mysql-bin.000001--start-position= "794"--stop-position = "1055" | /usr/bin/mysql-uroot-p123456 Test

Reset Binlog Log

mysql> Reset Master;

Query OK, 0 rows affected (0.01 sec)

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

| mysql-bin.000001 | 106 | | |

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

mysql> flush logs; #关闭当前的二进制日志文件并创建一个新文件, the name of the new binary log file adds 1 to the number of the current binary file.

This article is from the "Elephant" blog, make sure to keep this source http://zlyang.blog.51cto.com/1196234/1833058

Using Mysqlbinlog to recover database data