使用mysqlbinlog恢复数据

1、登录MySQL，查看binlog日志的状态

登录MySQL后，输入show variables like ‘%log_bin%’；查看到binlog日志为OFF关闭状态;

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | off                             |
| log_bin_basename                |                                 |
| log_bin_index                   |                                 |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+
6 rows in set (0.00 sec)

2、开启MySQL binlog日志

在MySQL的my.cnf配置文件的[mysqld]块下方添加

[root@localhost /]*# vim /etc/my.cnf
server_id=1
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30

重启MySQL后生效

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

日志默认记录在/var/lib/mysql/目录下。

3、根据binlog文件直接恢复数据

3.1、执行：FLUSH LOGS;

刷新日志是为了实验内容更直观，更容易观察到整个实验过程的内容。

我看到网上许多文章有在用REST MASTER;而未说明此命令的严重性

这条命令会删除所有日志文件，并将文件名和记录点进行重置归零，99%的情况下是用不到这条命令的

删除日志可以用PURGE MASTER LOGS…这样保险一点

3.2、新日志文件已经生成，先观察一下内容，有几个点需要了解

查看二进日日志文件命令：mysqlbinlog mysql-bin.000001

# at 4

# 180903 16:19:12 server id 1  end_log_pos 123 CRC32 0xe03659b3  Start: binlog v 4, server v 5.7.22-log created 180903 16:19:12

先看上边两个箭头：

# at 4（事件开始点）

# 180903 16:19:12 （代表的是时间）

server id 1（主备复制时需要为每个MYSQL数据库指定唯一的SERVER ID，我的未配置，默认是1）

end_log_pos 123（事件结束点）

再看下边两个箭头：

# at 123（事件开始点，和上边的事件结束点是对应的）

end_log_pos 154（事件结束点）

at 4 和 at 123之间的内容就是事件内容

3.3、模拟业务场景，建表，插入数据，最后将某个表删除；为了真实，我建了两个库，同时向不同的库写入内容，最后将其中一个库中的某个表删除。

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)

mysql> create database t1;
Query OK, 1 row affected (0.03 sec)

mysql> create database t2;
Query OK, 1 row affected (0.00 sec)

mysql> use t1;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> use t2;
Database changed
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2 values (3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values (4);
Query OK, 1 row affected (0.01 sec)

mysql> use t1;
Database changed
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (2);
Query OK, 1 row affected (0.01 sec)

mysql> use t2;
Database changed
mysql> insert into t2 values(20);
Query OK, 1 row affected (0.01 sec)

mysql> use t1;
Database changed
mysql> insert into t1 values(10);
Query OK, 1 row affected (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> use t2;
Database changed
mysql> insert into t2 values(222);
Query OK, 1 row affected (0.01 sec)

mysql>

建立T1、T2库，建立T1、T2表。

向T1插入数据：1、2、10

向T2插入数据：3、4、20、222

模拟场景，删除T1表，T2库T2表业务还在继续运行

现在将要通过日志将T1表进行恢复。

首先要先找到那个删除命令的日志点：

mysqlbinlog master-bin.000014|grep -5a "DROP TABLE"

看到#AT 2439 （记下这个数字）

在这个事件点执行的DROP TABLE操作。

由于日志文件内不只有T1库的日志，还有T2库的日志，一会只取T1数据库的日志

而且还只取2439日志点之前的日志，再进行重新应用

如果把2439的日志取的话，再应用时数据库会重新建库建表，插数据， 还会执行这条删表语句。

mysqlbinlog mysql-bin.000001 -d t1  --skip-gtids --stop-position=2439>test.sql
-d：参数是指定某个数据库日志

命令意思是将mysql-bin.000001日志文件内的T1数据库日志，事件点2439之前的日志，输出到test.sql

 tail test.sql

看看文件最后几行

登录数据库：

mysql> use t1;
Database changed

mysql> source test.sql

再查看表内容

这样数据就回来了。

备注

# 此命令用于将截止事件点之前的操作记录生成为sql
mysqlbinlog mysql-bin.000005 -d 数据库名  --skip-gtids --stop-position=截止事件开始点>test.sql

1.首先将binlog文件导出指定时间段的内容：
mysqlbinlog --start-datetime='2017-09-10 00:00:00' --stop-datetime='2017-09-10 01:01:01' -d 库名 binlog二进制文件 > test.sql

2.将指定的表操作内容过滤到指定文件
grep -B3 -w tb_name data.sql |grep -v  '^--$' >tb_name.sql

3、此命令待验证
mysqlbinlog mysql-bin.000005 -d laravel_admin  --skip-gtids --start-datetime='2017-09-10 00:00:00' --stop-datetime='2027-09-10 01:01:01' --stop-position=截止事件开始点>test.sql