MySQL:binlog的开启和数据恢复
测试环境
Mac OS 10.11.6
mysql 5.7.21
python 3.6.5
开启binlog
1、检查是否开启binlog
mysql>show variables like '%log_bin%';
- 1
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
- 1
- 2
- 3
- 4
- 5
- 6
3、重启服务
$ mysql.server restart
4、再次检查是否开启binlog
5、查看日志文件:
$ cd /usr/local/var/mysql
$ ls
- 1
- 2
数据恢复
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='用户表测试';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
(2) 插入数据
mysql> INSERT INTO `user` (`name`, `age`)
VALUES ('小王', 21), ('小张', 22), ('小红', 22), ('小楠', 23), ('小柱', 25);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
(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 |
+----+--------+------+---------------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
(4) 删除数据
mysql> delete from user;
- 1
(5)确认数据被删除
mysql> select * from user;
Empty set (0.00 sec)
- 1
- 2
3、数据恢复
(1)下载所需工具
> git clone https://github.com/danfengcao/binlog2sql.git
> cd binlog2sql
> pip install -r requirements.txt
- 1
- 2
- 3
(2)查看目前的binlog
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 467 |
| mysql-bin.000002 | 732 |
| mysql-bin.000003 | 4574 |
+------------------+-----------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
最新的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
- 1
- 2
- 3
- 4
- 5
字段说明
-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' # 结束时间
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
找到DELETE语句中类似如下字段,回忆一下时间是否差不多
#start 4228 end 4543 time 2019-01-21 18:20:15
- 1
(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
- 1
- 2
- 3
- 4
- 5
(5) 执行回滚操作
其实就是重新插入数据
$ mysql -h127.0.0.1 -P3306 -uroot -p'123456' < rollback.sql
- 1
(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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
参考
文章来源: pengshiyu.blog.csdn.net,作者:彭世瑜,版权归原作者所有,如需转载,请联系作者。
原文链接:pengshiyu.blog.csdn.net/article/details/86582155
- 点赞
- 收藏
- 关注作者
评论(0)