MYSQL bin_log 开启及数据恢复

参考博客:

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。

MYSQL bin_log 开启及数据恢复

1:如何开启bin_log

1.1:查看是否开启bin_log

MYSQL bin_log 开启及数据恢复

 1.2:修改mysql配置文件,开启bin_log

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

MYSQL bin_log 开启及数据恢复

  注意点:

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

(2)mysql-bin-log 为日志文件前缀,生成的日志文件格式为 mysql-bin-log.000001 。

(2)重启mysql,在检查bin_log 是否开启

MYSQL bin_log 开启及数据恢复 MYSQL bin_log 开启及数据恢复

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

2:如何恢复数据

(1)方便测试,可以导入一下数据:

MYSQL bin_log 开启及数据恢复
###建库:
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命令

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

2.1:单恢复某个表

(1)导入数据,然后删除表 tb_user

MYSQL bin_log 开启及数据恢复

 (2)进入bin_log 日志文件夹,使用命令查看 SQL 日志。

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

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

 部分截图:

MYSQL bin_log 开启及数据恢复

 因为binlog_format 格式是ROW,因此在Mysql 中使用命令 show binlog events in 'mysql-bin-log.000002'  是无法看到sql的。MYSQL bin_log 开启及数据恢复

 可以看到在 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

 部分截图:

MYSQL bin_log 开启及数据恢复 MYSQL bin_log 开启及数据恢复

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

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

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

MYSQL bin_log 开启及数据恢复

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

可以看到:

MYSQL bin_log 开启及数据恢复 MYSQL bin_log 开启及数据恢复

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

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

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

因此特意提醒,留心!

我复现一下:

1:我flush logs 生成新的log 文件,重新导入数据,然后直接删库(与2.2的前置操作一样)

log 日志:

MYSQL bin_log 开启及数据恢复

 

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

报错:提示库不存在

MYSQL bin_log 开启及数据恢复

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

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

MYSQL bin_log 开启及数据恢复

 此时我已不在 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 

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

MYSQL bin_log 开启及数据恢复

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

(3)再次尝试恢复表结构和数据

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

执行结果:成功

MYSQL bin_log 开启及数据恢复

5:总结原因

(1):恢复库结构的时候,可以附带参数 --database=back_master 但不能在 -v 后面带 back_master

(2):恢复表结构和数据的时候,不可以附带参数 --database=back_master,但是 -v 后面可以带 back_master

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

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

日志对比:

MYSQL bin_log 开启及数据恢复

 

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

MYSQL bin_log 开启及数据恢复

隔了大概半个小时生效了

MYSQL bin_log 开启及数据恢复

咱也不知道为啥。。。

上一篇:主要股东近3年净买入排名


下一篇:Oracle本地网络分表模拟分区裁剪