200字范文 > mysql binlog elk_利用MySQL的binlog日志文件恢复数据库

mysql binlog elk_利用MySQL的binlog日志文件恢复数据库

时间：2020-10-26 09:16:10

一、新建一个数据库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.02sec)

mysql> insert into test(id,name)values(2,'kevin');

Query OK, 1 row affected (0.02sec)

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.08sec)

mysql>

此时数据库已经被删除

mysql>show databases;

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

| Database |

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

| information_schema |

| hl_central_sms_deduction |

| mysql |

| performance_schema |

| sys |

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

5 rows in set (0.00sec)

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#07 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#07 9:53:14 server id 693306 end_log_pos 483 CRC32 0x2616f602 Query thread_id=133963 exec_time=0 error_code=0SET TIMESTAMP=1586224394/*!*/;

create database test CHARSET utf8

/*!*/;

# at 483#07 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;

#07 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#07 10:07:01 server id 693306 end_log_pos 1683 CRC32 0x5f1c948b Query thread_id=133963 exec_time=0 error_code=0SET TIMESTAMP=1586225221/*!*/;

drop database test

/*!*/;

# at 1683#07 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;