文章目录
一、概述
删库跑路这个词大家看到不会陌生,那万一发生在自己身上怎么办?直接跑?那肯定是不现实的,其实这里有种方法可以恢复误删除的数据,使用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