MYSQL bin_log 开启及数据恢复

2022-08-13 19:33:39

参考博客：

A：https://www.jianshu.com/p/55b0d52edca2

B：https://www.cnblogs.com/martinzhang/p/3454358.html

C：https://www.cnblogs.com/xxoome/p/9802684.html

本文基于Mysql 5.7.27。

1：如何开启bin_log

1.1：查看是否开启bin_log

1.2：修改mysql配置文件，开启bin_log

（1）我的配置文件在 /etc/my.cnf,也可以使用 whereis my.cnf 查看位置。

　　注意点：

（1）binlog_format 有三种格式，分别是STATEMENT、ROW、MIXED。自5.7.7之后默认为ROW 格式。具体区别可自行百度之。　

（2）mysql-bin-log 为日志文件前缀，生成的日志文件格式为 mysql-bin-log.000001 。

（2）重启mysql，在检查bin_log 是否开启

　　上述则表示已成功开启bin_log 了，后面开始进行数据恢复测试。

2：如何恢复数据

（1）方便测试，可以导入一下数据：

###建库：
CREATE DATABASE `back_master` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

### 建表
CREATE TABLE `back_master`.`tb_user`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
### 插入数据
insert into tb_user(name) values('wew'),('1233'),('sdsa'),('wqewq');

测试sql

（2）常用binlog日志操作命令
1.查看所有binlog日志列表
　　mysql> show master logs;

2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
　　mysql> show master status;

3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件
　　mysql> flush logs;
　注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4.重置(清空)所有binlog日志
　　mysql> reset master;

（3）查看log日志，并展现出sql语句。

1.按时间
　　/usr/bin/mysqlbinlog -v --base64-output=decode-rows mysql-bin-log.000007 \
　　--start-datetime="2020-05-11 17:00:00" \
　　--stop-datetime="2020-05-11 18:00:00"
2.按节点
　　/usr/bin/mysqlbinlog -v --base64-output=decode-rows mysql-bin-log.000001 \
　　--start-position=454 \
　　--stop-position=665

3. 在mysql 中显示某个文件的具体几条

　　show binlog events in 'mysql-bin-log.000001' from 1 limie 10 ;

（4）binlog_format=Row 模式下查看sql命令

2.1：单恢复某个表

（1）导入数据，然后删除表 tb_user

（2）进入bin_log 日志文件夹，使用命令查看 SQL 日志。

我们在删除tb_user 之前，日志是放在 msysql-bin-log.000002 中的，因此我们查看这个文件的日志即可。

命令：/usr/bin/mysqlbinlog -v --base64-output=decode-rows mysql-bin-log.000002

部分截图：

因为binlog_format 格式是ROW，因此在Mysql 中使用命令 show binlog events in 'mysql-bin-log.000002' 是无法看到sql的。

可以看到在 pos=1044 之后进行了表删除操作，因此我们要恢复数据的话，mysqlbinlog 读取的节点应该在 pos=665 和 pos=1044 之间。

命令：/usr/bin/mysqlbinlog --start-position=454 --stop-position=1044 mysql-bin-log.000002 | mysql -uroot -pZgq@123456 -v back_master

部分截图：

可以看到数据被成功恢复了。

2.2：删库之后进行数据恢复

本人重新导入数据，并把本次实验日志写到 mysql-bin-log.00005 文件中。所以可以看到：

执行命令：/usr/bin/mysqlbinlog --stop-position=1033 mysql-bin-log.000005 | mysql -uroot -pZgq@123456 -v

可以看到：

库结构、表结构、数据都得到回复了。

2.2.1：为什么我要把删除和删表分开描述

那为什么我要单独废话再讲一遍？因为笔者在这里走了弯路。明明一句命令就可以搞定，因为操作不当，走了弯路，

因此特意提醒，留心！

我复现一下：

1：我flush logs 生成新的log 文件，重新导入数据，然后直接删库（与2.2的前置操作一样）

log 日志：

2：使用命令 /usr/bin/mysqlbinlog --stop-position=1033 mysql-bin-log.000005 | mysql -uroot -pZgq@123456 -v back_master

报错：提示库不存在

3：使用命令 /usr/bin/mysqlbinlog --stop-position=1033 --database=back_master mysql-bin-log.000008 | mysql -uroot -pZgq@123456 -v

执行到最后报错：提示表不存在

此时我已不在 mysql-bin-log.000008 中写入日志了，因此我再次删库以方便第四次测试

4：使用命令

（1）恢复库结构

　　/usr/bin/mysqlbinlog --start-position=219 --stop-position=454 --database=back_master mysql-bin-log.000008 | mysql -uroot -pZgq@123456 -v

执行结果：成功

（2）恢复表结构及数据

　　/usr/bin/mysqlbinlog --start-position=454 --stop-position=1033 --database=back_master mysql-bin-log.000008 | mysql -uroot -pZgq@123456 -v

执行到最后报错：提示表不存在

显然恢复表结构和表数据失败了。换个思路，执行命令：

（3）再次尝试恢复表结构和数据

　　/usr/bin/mysqlbinlog --start-position=454 --stop-position=1033 mysql-bin-log.000008 | mysql -uroot -pZgq@123456 -v back_master

执行结果：成功

5：总结原因

（1）：恢复库结构的时候，可以附带参数 --database=back_master 但不能在 -v 后面带 back_master

（2）：恢复表结构和数据的时候，不可以附带参数 --database=back_master，但是 -v 后面可以带 back_master

但凡我再聪明点也不会吃这个亏了啊。。

2.2.2：binlog_format由Row改Statement后不立即生效

日志对比:

设置为statement 之后没有立即生效

隔了大概半个小时生效了

咱也不知道为啥。。。

码农公寓