Operation instructions for using Mysqlbinlog logs to recover database data
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.
