利用MySQL的binlog日志文件恢复数据库
一、新建一个数据库test 在库里新建一个数据表test ,并插入几条数据
mysql> create database test CHARSET utf8;
Query OK, 1 row affected (0.01 sec)
CREATE TABLE test(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL)ENGINE=INNODB CHARSET=utf8;
mysql> insert into test(id,name)values(1,'andy');
Query OK, 1 row affected (0.02 sec)
mysql> insert into test(id,name)values(2,'kevin');
Query OK, 1 row affected (0.02 sec)
mysql> insert into test(id,name)values(3,'peter');
Query OK, 1 row affected (0.03 sec)
二、删除数据库
mysql> drop database test;
Query OK, 1 row affected (0.08 sec)
mysql>
此时数据库已经被删除
mysql> show databases;
+--------------------------+
| Database |
+--------------------------+
| information_schema |
| hl_central_sms_deduction |
| mysql |
| performance_schema |
| sys |
+--------------------------+
5 rows in set (0.00 sec)
mysql>
三、利用binlog日志进行恢复
1、查看binlog日志，过滤create database'字符串，并显示上下文5行
[root@orderer ~]# mysqlbinlog --base64-output=decode-rows -vvv /home/mysql-5.7.26/data/master-18-69.000021|grep 'create database' -C 5
#200407 9:53:14 server id 693306 end_log_pos 376 CRC32 0x92e06e42 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 376
#200407 9:53:14 server id 693306 end_log_pos 483 CRC32 0x2616f602 Query thread_id=133963 exec_time=0 error_code=0
SET TIMESTAMP=1586224394/*!*/;
create database test CHARSET utf8
/*!*/;
# at 483
#200407 10:00:55 server id 693306 end_log_pos 548 CRC32 0x105bf6f3 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 548
可以看到create database test CHARSET utf8命令开始position号为376，
2、再过滤drop database 语句，并显示上下文5行
[root@orderer home]# mysqlbinlog --base64-output=decode-rows -vvv /home/mysql-5.7.26/data/master-18-69.000021|grep 'drop database' -C 5;
#200407 10:07:01 server id 693306 end_log_pos 1591 CRC32 0xc9f74901 Anonymous_GTID last_committed=6 sequence_number=7 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1591
#200407 10:07:01 server id 693306 end_log_pos 1683 CRC32 0x5f1c948b Query thread_id=133963 exec_time=0 error_code=0
SET TIMESTAMP=1586225221/*!*/;
drop database test
/*!*/;
# at 1683
#200407 10:20:03 server id 693306 end_log_pos 1748 CRC32 0x74d98fe0 Anonymous_GTID last_committed=7 sequence_number=8 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1748
可以看到drop database test 执行的position号为1591,那么我们截取结束的position号设置为1590即可，这样就只截取drop语句之前的所有日志
3、开始位置376，结束位置1590,截取指定数据库test的日志并保存为.sql文件
[root@orderer home]# mysqlbinlog --start-position=376 --stop-position=1590 -d test /home/mysql-5.7.26/data/master-18-69.000021 > /home/binlog_test.sql
[root@orderer home]# ll
四、进入mysql，利用source恢复数据
设置sql_log_bin=0,;#设为0后，在Master数据库上执行的语句都不记录binlog,
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
利用导出来的sql文件恢复数据
mysql> source /home/binlog_test.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
验证结果
mysql> show databases;
+--------------------------+
| Database |
+--------------------------+
| information_schema |
| hl_central_sms_deduction |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------------+
6 rows in set (0.00 sec)
已经看到刚才删除的test数据库了
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.01 sec)
mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from test ;
+----+-------+
| id | name |
+----+-------+
| 1 | andy |
| 2 | kevin |
| 3 | peter |
+----+-------+
3 rows in set (0.00 sec)
查询数据表数据已经恢复。