依据binlog恢复数据

目录

• 一、binlog简介
• 二 、binlog相关参数
  • log-bin
  • binlog_format
  • binlog_do_db
  • binlog_ignore_db
  • expire_logs_days
  • max_binlog_size
  • log_bin_trust_function_creators
  • sync_binlog
• 三、恢复数据(demo)
• 补充
  • 运行一个开启binlog模式的mysql(docker)
    • 1.查看当前mysql是否开启binlog模式
    • 2.运行一个docker挂载数据卷的mysql容器
    • 3.开启binlog日志
    • 4.重启mysql生效

参考资料地址1: 解析MySQL binlog

一、binlog简介

binlog即binary log，二进制日志文件。它记录了数据库所有执行的DDL和DML语句（除了数据查询语句select、show等），以事件形式记录并保存在二进制文件中。

binlog主要有两个应用场景

一是用于复制，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

二是用于数据恢复，例如还原备份后，可以重新执行备份后新产生的binlog，使得数据库保持最新状态。

binlog日志可以选择三种模式，分别是 STATEMENT、 ROW、 MIXED，下面简单介绍下这三种模式：

• STATEMENT：基于SQL语句的复制，每一条会修改数据的sql语句会记录到binlog中。该模式下产生的binlog日志量会比较少，但可能导致主从数据不一致。
• ROW：基于行的复制，不记录每一条具体执行的SQL语句，仅需记录哪条数据被修改了，以及修改前后的样子。该模式下产生的binlog日志量会比较大，但优点是会非常清楚的记录下每一行数据修改的细节，主从复制不会出错。
• Mixed：混合模式复制，以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
• binlog模式在MySQL 5.7.7之前，默认为 STATEMENT，在之后的版本中，默认为ROW。这里建议采用ROW模式，因为ROW模式更安全，可以清楚记录每行数据修改的细节。

二 、binlog相关参数

binlog默认情况下是不开启的，不过一般情况下初始化的时候建议在配置文件中增加log-bin参数来开启binlog。

log-bin

配置文件中增加log-bin配置

[mysqld]
log-bin = binlog

不指定路径默认在data目录下，也可以指定路径

[mysqld]
log-bin = /data/mysql/logs/binlog

查看数据库是否开启了binlog

show variables like ‘log_bin%’;
开启binlog后，还需注意一些与binlog相关的参数，下面简单介绍下相关参数：

binlog_format

设置binlog模式，建议设为ROW。

binlog_do_db

此参数表示只记录指定数据库的二进制日志，默认全部记录，一般情况下不建议更改。

binlog_ignore_db

此参数表示不记录指定的数据库的二进制日志，同上，一般不显式指定。

expire_logs_days

此参数控制二进制日志文件保留天数，默认值为0，表示不自动删除，可设置为0~99。可根据实际情况设置，比如保留15天或30天。MySQL8.0版本可用binlog_expire_logs_seconds参数代替。

max_binlog_size

控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。此参数的最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。一般情况下可采取默认值。

log_bin_trust_function_creators

当二进制日志启用后，此参数就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。建议设置为1。

sync_binlog

控制MySQL服务端将二进制日志同步到磁盘的频率，默认值为1。
设置为0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新；
设置为1，表示每次事务提交，MySQL都会把binlog刷下去，这是最安全的设置，但由于磁盘写入次数增加，可能会对性能产生负面影响；
设置为n，其中n为0或1以外的值，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，将Binlog文件缓存刷新到磁盘。
推荐设置为1，出于性能考虑也可酌情调整。

三、恢复数据(demo)

准备一个开启binlog模式的数据库,具体操作见补充

常用命令

-- 查看bin-log是否开启
show variables like '%log_bin%';
-- 查看所有binlog日志文件列表
show master logs;
-- 刷新日志，此刻开始产生一个新编号的binlog文件
Flush logs;
-- 清空所有binlog日志命令
reset master;
-- 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值：
show master status; 
-- 查看(mysql-bin.000002)binlog
show binlog events in 'mysql-bin.000002';
--查看binlog模式
show variables like 'binlog_format';
-- 指定位点查询，比如从pos为746开始查询
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746;
-- 指定位点分页查询
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746 LIMIT 0,5

创建一个test1数据库

为排除干扰,我们先清空之前的binlog日志(只在自己的mysql测试)

-- 查看bin-log是否开启
show variables like '%log_bin%';
-- 清空所有binlog日志命令
reset master;

准备基础数据

建一个user表,并插入四条数据

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT 1 COMMENT '年龄',
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES (1, '张三', 18, '');
INSERT INTO `user` VALUES (2, '李四', 19, '');
INSERT INTO `user` VALUES (3, '王五', 12, '');
INSERT INTO `user` VALUES (4, '赵四', 199, '');

-- 查看所有binlog日志文件列表
show master logs;
-- 查看(mysql-bin.000001)binlog
show binlog events in 'mysql-bin.000001';

如图

在之后的操作记录在新的日志文件中,并分析

-- 刷新日志，此刻开始产生一个新编号的binlog文件
Flush logs;

恢复删除的数据

插入这两条数据后

INSERT INTO `user` VALUES (5, '小青', 12, '');
INSERT INTO `user` VALUES (6, '小白', 19, '');

误删除这两条数据(不小心)

查看binlog日志可以看到(注意两条插入语句的pos)

show binlog events in 'mysql-bin.000002';

查看user表确实删除了

恢复数据操作(在指定库中恢复指定位点间的操作)

进入mysql容器内部

docker exec -it de9e411314b3 /bin/bash

恢复删除的数据

/usr/bin/mysqlbinlog  --start-position=1609 --stop-position=1822 --database=test1  /var/lib/mysql/mysql-bin.000002 | /usr/bin/mysql -uroot -p123456 -v test1

注

/usr/bin/mysqlbinlog 为binlog命令

--start-position=219为恢复的开始位置

--stop-position=706为恢复的结束位置

--database=test指定数据库为demo_test

/var/lib/mysql/mysql-bin.000002 为binlog日志

| /usr/bin/mysql -uroot -p123456 -v test 通过管道连接数据库，并通过-v显示详细信息

控制台无报错

看到表数据也恢复了

补充

运行一个开启binlog模式的mysql(docker)

1.查看当前mysql是否开启binlog模式

-- 查看bin-log是否开启
show variables like '%log_bin%';

如果log_bin的值为OFF是未开启，为ON是已开启

2.运行一个docker挂载数据卷的mysql容器

需提前在宿主机目录下创建一个文件用于保存mysql的数据集,/mydata/mysql/data

mkdir -p /mydata/mysql/data

基于mysql:5.7镜像

docker run -d -p 3306:3306 --privileged=true --name=mysql5.7 -v /mydata/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7 \
> --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

注: --privileged=true 是为了解决权限问题,参考资料地址: https://blog.csdn.net/weixin_34370110/article/details/114166390

3.开启binlog日志

docker exec mysql5.7 bash -c "echo 'log-bin=/var/lib/mysql/mysql-bin' >> /etc/mysql/mysql.conf.d/mysqld.cnf"

docker exec mysql5.7 bash -c "echo 'server-id=123454' >> /etc/mysql/mysql.conf.d/mysqld.cnf"

4.重启mysql生效

docker restart mysql5.7