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