Shulou(Shulou.com)06/01 Report--

The following gives you instructions on using Mysqlbinlog logs to recover database data. If you are interested, let's take a look at this article. I believe it will be more or less helpful to you after reading the operation instructions on using Mysqlbinlog logs to recover database data.

1. Enable mysql-binlog log

Add the following configuration to the mysql configuration file my.cnf

[mysqld]

Log-bin=mysql-bin

Binlog_do_db=bin_test

Restart mysql

Service mysqld restart

II. Back up the database

1) check the current database and binlog logs first:

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 libraries 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 (10) 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) backup data to / tmp/test.sql

[root@localhost ~] # whereis mysqldump

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

[root@localhost] # / usr/bin/mysqldump-uroot-p123456 bin_test > / tmp/test.sql

4) check the binlog log:

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 (10) 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)

Third, simulate misoperation at this time (delete database)

At this point, the database is suddenly damaged 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)

Then check whether the deletion operation is recorded in the binlog log:

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 (10) 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 |

+- -- +

10 rows in set (0.00 sec)

You can see that the operation of Article 20 is the operation of deleting the database.

At this time, the database has been completely destroyed.

1) use the mysqlbinlog command to restore. 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 (10) 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 |

+- -- +

10 rows in set (0.00 sec)

At this point, we have confirmed that the "start value" is the start of the creation of the library: 106, and the "end value" is the end of the previous sentence of 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 whether the data has been recovered successfully:

11mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | bin_test |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

11mysql > select * from bin_test.abc

+-+ +

| | id | name |

+-+ +

| | 1 | zhangsan |

| | 2 | lisi |

| | 3 | wangwu |

+-+ +

3 rows in set (0.00 sec)

3) restore successfully

[summary]: mysql backup and bin-log log

Backup data:

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

-l: read lock (can only be read, not updated)

-F: flush logs, which can regenerate new log files, including log-bin logs, of course

View the binlog log:

Mysql > show master status

If there is a large amount of data, back up the data before importing:

Mysql-uroot-p123456 bin_test-v-f 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;# closes the current binary log file and creates a new file. The name of the new binary log file adds 1 to the number of the current binary file.

Read the details of the above operation instructions on the use of Mysqlbinlog logs to recover database data, and whether you have gained anything. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.