Using Mysqlbinlog to recover database data

Source: Internet
Author: User
Tags mysql backup

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.