MySQL：binlog的开启和数据恢复

测试环境

Mac OS 10.11.6
mysql 5.7.21
python 3.6.5

开启binlog

1、检查是否开启binlog

mysql>show variables like '%log_bin%';

2、开启binlog
mysql的配置文件路径查找优先级为
/etc/my.cnf
/etc/mysql/my.cnf
/usr/local/etc/my.cnf

通过Homebrew安装的my.cnf放在/usr/local/etc/中

$ vim my.cnf

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

3、重启服务
$ mysql.server restart

4、再次检查是否开启binlog

5、查看日志文件：

$ cd /usr/local/var/mysql
$ ls

数据恢复

0、几个术语
数据查询语言DQL
数据操纵语言DML
数据定义语言DDL
数据控制语言DCL

1、MySQL闪回原理
delete操作，delete -> insert
insert操作，insert -> delete
update操作，交换SET和WHERE

恢复实战

1、确认binlog已开启
2、数据准备
(1) 建表

mysql> use demo
mysql> CREATE TABLE `user` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(20) DEFAULT '',
  `age` tinyint(3) DEFAULT '0',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表测试';

(2) 插入数据

mysql> INSERT INTO `user` (`name`, `age`)
VALUES 
    ('小王', 21), 
    ('小张', 22), 
    ('小红', 22), 
    ('小楠', 23), 
    ('小柱', 25);

(3) 查看数据

mysql> select * from user;
+----+--------+------+---------------------+
| id | name   | age  | create_time         |
+----+--------+------+---------------------+
|  1 | 小王   |   21 | 2019-01-21 18:18:41 |
|  2 | 小张   |   22 | 2019-01-21 18:18:41 |
|  3 | 小红   |   22 | 2019-01-21 18:18:41 |
|  4 | 小楠   |   23 | 2019-01-21 18:18:41 |
|  5 | 小柱   |   25 | 2019-01-21 18:18:41 |
+----+--------+------+---------------------+

(4) 删除数据

mysql> delete from user;

(5)确认数据被删除

mysql> select * from user;
Empty set (0.00 sec)

3、数据恢复
（1）下载所需工具

> git clone https://github.com/danfengcao/binlog2sql.git 
> cd binlog2sql
> pip install -r requirements.txt

(2)查看目前的binlog

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       467 |
| mysql-bin.000002 |       732 |
| mysql-bin.000003 |      4574 |
+------------------+-----------+

最新的biglog是 mysql-bin.000003

（3）查找删除语句

$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'123456' -ddemo -tuser --start-file='mysql-bin.000003' --start-datetime='2019-01-21 18:10:00' --stop-datetime='2019-01-21 18:25:00' > raw.sql

$ more raw.sql

字段说明

-h127.0.0.1  # 数据库地址
-P3306  # 端口号
-uroot # 用户名
-p'123456'  # 密码
-ddemo  # 数据库名称
-tuser  # 表名称
--start-file='mysql-bin.000003'  # binlog日志文件
--start-datetime='2019-01-21 18:10:00' # 开始时间
--stop-datetime='2019-01-21 18:25:00'  # 结束时间

找到DELETE语句中类似如下字段，回忆一下时间是否差不多

#start 4228 end 4543 time 2019-01-21 18:20:15

（4）生成回滚语句

$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'123456' -ddemo -tuser --start-file='mysql-bin.000003' --start-position=4228 --stop-position=4543 -B > rollback.sql

$ more rollback.sql

(5) 执行回滚操作
其实就是重新插入数据

$ mysql -h127.0.0.1 -P3306 -uroot -p'123456' < rollback.sql

(6) 检查数据恢复情况

mysql> select * from user;
+----+--------+------+---------------------+
| id | name   | age  | create_time         |
+----+--------+------+---------------------+
|  1 | 小王   |   21 | 2019-01-21 18:18:41 |
|  2 | 小张   |   22 | 2019-01-21 18:18:41 |
|  3 | 小红   |   22 | 2019-01-21 18:18:41 |
|  4 | 小楠   |   23 | 2019-01-21 18:18:41 |
|  5 | 小柱   |   25 | 2019-01-21 18:18:41 |
+----+--------+------+---------------------+

参考

1. Mac OS下安装配置mysql8.0
2. MySQL闪回原理与实战
3. mysql误删数据快速恢复