mysqlbinlog简单入门实践

一、概述

​ 删库跑路这个词大家看到不会陌生，那万一发生在自己身上怎么办？直接跑？那肯定是不现实的，其实这里有种方法可以恢复误删除的数据，使用binlog可以很轻松的查看你之前的操作，当然前提是打开了这个功能，而且使用了mysqlbinlog ，因为 binlog 文件是二进制的。

​ 这里也简单记录一下防止自己以后手贱误删操作。

​ 环境准备：MySql8.0，navicate15

二、binlog日志

1、binlog介绍与配置

​ binlog 即 Binary Log，它是二进制文件，用来记录数据库写操作的日志。数据库的 insert、delete、update、create、alter、drop 等写入操作都会被 binlog 记录。因此，数据库的主从数据同步通常也是基于 binlog 完成的。

​ 一般情况下，binlog是默认开启的，可以通过脚本查看 binlog 是否开启，也可以查看文件存放地址。

SHOW VARIABLES LIKE 'LOG_BIN%';

​ 如果log_bin参数显示的是OFF说明 binlog 是关闭状态，需要手动开启。开启 binlog 需要修改数据库的my.cnf配置文件，my.cnf文件通常在服务器的/etc目录下。binlog 的日志有三种格式，分别是 STATEMENT、ROW、MIXED。在 mysql5.7.7 版本之前默认使用的是 STATEMENT，之后的版本默认使用的是 ROW。

# 启用binlog并设置binlog日志的存储目录
log_bin = /www/server/data/mysql-bin
# 设置binlog索引存储目录
log_bin_index = /www/server/data/mysql-bin.index
# 30天之前的日志自动删除
expire_logs_days = 30
# 设置binlog日志模式，共有3种模式：STATMENT、ROW、MIXED 
binlog_format = row

2、binlog三种日志格式

• ROW

  binlog 记录的是每一条数据被修改的详细细节。它记录了每条数据修改细节，所以在一些极端情况下也不会出现数据错乱的问题，其中记录的是伪SQL，需要去除注释才能运行。在做数据恢复或主从同步时能很好的保证数据的真实性和一致性，但是磁盘占用会大。

• STATMENT

  日志中记录的是真正的 sql 语句，可以直接拿到数据库运行。因为记录的是 sql 语句和执行语句时的上下文环境，而不是每一条数据。所以它的日志文件会比 ROW 格式的日志文件小一些。但由于记录的只是 sql 语句和上下文的环境，STATEMENT 格式的日志在进行主从数据同步时会有一些不可预估的情况出现，导致数据错乱。比如 sleep()、last_insert_id() 等函数会出现问题。

• MIXED

  MIXED 格式是 STATEMENT 和 ROW 的结合，mysql 会根据具体执行的 sql 语句，来选择合适的日志格式进行记录。MIXED 格式下，在执行普通的 sql 语句时会选 STATEMENT 来记录日志，在遇到复杂的语句或函数操作时会选择 ROW 来记录日志。

3、MySql常用查询

#查看数据库时间
SELECT @@log_timestamps;
#查看当前使用的二进制日志
show master status
#查看二进制日志格式
show VARIABLES like 'binlog_format'
#查看某个日志中的事件
show binlog events in 'mysql-bin.000001';
#查看某个日志中的事件，从5446开始
show binlog events in 'mysql-bin.000001' from 5446 limit 10;
#手动滚动一下二进制日志
flush logs;
#清空日志
reset master;

4、mysqlbinlog命令介绍

​ mysql 数据库的 binlog 文件是二进制的，基本看不懂，使用数据库自带的mysqlbinlog命令可以把二进制文件转换成能看懂的十进制文件。由于数据库的 binlog 文件可能会很大，查看起来会很麻烦，所以mysqlbinlog命令也提供了一些参数可以用来筛选日志。

「mysqlbinlog 语法」

#options：可选参数 
#log-files：文件名称
mysqlbinlog [options] log-files

「options 的常用值」

-d: 根据数据库的名称筛选日志

-o：跳过前N行日志

-r, --result-fil: 把日志输出到指定文件

--start-datetime: 读取指定时间之后的日志，时间格式：yyyy-MM-dd HH:mm:ss

--stop-datetime: 读取指定时间之前的日志，时间格式：yyyy-MM-dd HH:mm:ss

--start-position: 从指定位置开始读取日志

--stop-position: 读取到指定位置停止

--base64-output：在 row 格式下，显示伪 sql 语句

-v, --verbose：显示伪 sql 语句，-v 可以为 sql 语句添加备注

「常用写法」

#查看 test 数据库的日志
mysqlbinlog -d=test mysql-bin.000001
#查看某个时间段内的日志，并解析输出
mysqlbinlog -v --start-datetime "2021-10-09 19:30:00" --stop-datetime "2021-10-09 20:50:00" mysql-bin.000001 > temp.log
#恢复数据，事件的开始位置是 4300，结束位置是 10345
mysqlbinlog --start-position 4300 --stop-position 10345 mysql-bin.000001 | mysql -uroot -p123456 test

三、实践操作

1、数据生成与环境准备

​ 首先flush logs;手动生成一个新的二进制文件，之后新建数据库与存储过程用于生产数据，这里我开启了bin-log已经我的格式是row格式。

#新建数据库
DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `username` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `gender` int(0) NULL DEFAULT 0,
  `age` int(0) NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
)

#新建存储过程用于产生数据
CREATE DEFINER=`root`@`%` PROCEDURE `generation`(IN `startNum` int, IN `endNum` int)
BEGIN
	#Routine body goes here...
	while startNum <= endNum do   
    insert into test_user(username,gender,age) values(startNum,rand()*2 ,RAND()*100);   
    set startNum=startNum+1;  
end while;  
END

​ 随机产生1w条数据，并进行删除操作

#调用存储过程
CALL generation(1,10000);
#当做误删操作
DELETE FROM test_user WHERE age > 60;

2、数据恢复

找到二进制文件路径，使用mysqlbinlog工具解析，并把它输出到temp.log文件，若文件过大，可以进行简单的过滤。

mysqlbinlog -v mysql-bin.000001 > temp.log
#查看test数据库的数据
mysqlbinlog -v --start-datetime "2021-10-10 09:00:00" --stop-datetime "2021-10-10 09:50:00" mysql-bin.000001 | grep test

​ 最后的文件如图所示，可以查看到每一条语句的具体的操作记录，使用工具替换即可复原数据，sql 执行完以后，被误删除的数据就恢复回来了。

四、数据库操作的几点建议

• 研发人员不能直连生产库
• 登录生产库使用只读账号
• 关闭 autocomit、多人复核
• 修改数据之前先备份
• 设置数据库定期备份

---

参考文章：
https://mp.weixin.qq.com/s/SOI4Fh3g1DcYi59BcHpIFg

赏

谢谢你请我吃糖果

支付宝