【MySQL】binlog数据恢复

binlog概述

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。binlog 是逻辑日志，记录的是这个语句的原始逻辑。

查看binlog相关参数

show variables like '%log_bin%';

MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的。上图中log_bin的值是OFF就代表binlog是关闭状态，打开binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。

开启binlog

在配置文件中的[mysqld]部分增加如下配置：

# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=mysql-binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1
# 其他配置
binlog_format = row # 日志文件格式，下面会详细解释
expire_logs_days = 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

binlog_format 可以设置binlog日志的记录格式

• STATEMENT： 基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。
• ROW： 基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。
• MIXED： 混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

重启数据库后我们再去看data数据目录会多出两个文件，第一个就是binlog日志文件，第二个是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。

再次运行show variables like '%log_bin%';

log_bin：binlog日志是否打开状态
log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

biglog数据恢复

示例表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `create_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES (1, 'zhangsan', 25, '2023-11-10 10:42:14');
INSERT INTO `user` VALUES (2, 'wanglei', 23, '2023-11-10 10:42:32');

第一步： user表中再插入2条数据

INSERT INTO `user` VALUES (3, 'ceshi', 26, '2023-11-27 15:24:46');
INSERT INTO `user` VALUES (4, 'ceshi2', 20, '2023-11-27 15:24:59');

第二步： 删除ceshi、ceshi2这两条数据

DELETE FROM `user` WHERE id in (3,4);

第三步： 可以用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容。

首先进入MySQL的data目录，执行以下命令可以查询到binlog日志内容（我这里的日志文件是mysql-binlog.000001）。

mysqlbinlog --no-defaults -v --base64-output=decode-rows mysql-binlog.000001

查出来的binlog日志文件内容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#231127 15:15:04 server id 1  end_log_pos 123 CRC32 0xe2386638  Start: binlog v 4, server v 5.7.35-log created 231127 15:15:04 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#231127 15:15:04 server id 1  end_log_pos 154 CRC32 0x8fa78a64  Previous-GTIDs
# [empty]
# at 154
#231127 15:24:44 server id 1  end_log_pos 219 CRC32 0x4fbeb3af  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#231127 15:24:44 server id 1  end_log_pos 291 CRC32 0x6624a049  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1701069884/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=2097152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#231127 15:24:44 server id 1  end_log_pos 344 CRC32 0x2f918688  Table_map: `test`.`user` mapped to number 111
# at 344
#231127 15:24:44 server id 1  end_log_pos 394 CRC32 0x315b331e  Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `test`.`user`
### SET
###   @1=3
###   @2='ceshi'
###   @3=26
###   @4=NULL
# at 394
#231127 15:24:44 server id 1  end_log_pos 425 CRC32 0xb8321e42  Xid = 18
COMMIT/*!*/;
# at 425
#231127 15:24:48 server id 1  end_log_pos 490 CRC32 0x58c8ae2b  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 490
#231127 15:24:48 server id 1  end_log_pos 562 CRC32 0x0c7ca1e3  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1701069888/*!*/;
BEGIN
/*!*/;
# at 562
#231127 15:24:48 server id 1  end_log_pos 615 CRC32 0xfc294813  Table_map: `test`.`user` mapped to number 111
# at 615
#231127 15:24:48 server id 1  end_log_pos 686 CRC32 0x4ae8d2b3  Update_rows: table id 111 flags: STMT_END_F
### UPDATE `test`.`user`
### WHERE
###   @1=3
###   @2='ceshi'
###   @3=26
###   @4=NULL
### SET
###   @1=3
###   @2='ceshi'
###   @3=26
###   @4='2023-11-27 15:24:46'
# at 686
#231127 15:24:48 server id 1  end_log_pos 717 CRC32 0x79b19082  Xid = 21
COMMIT/*!*/;
# at 717
#231127 15:25:01 server id 1  end_log_pos 782 CRC32 0xfee1225a  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 782
#231127 15:25:01 server id 1  end_log_pos 854 CRC32 0x46e269a7  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1701069901/*!*/;
BEGIN
/*!*/;
# at 854
#231127 15:25:01 server id 1  end_log_pos 907 CRC32 0x48d00e0f  Table_map: `test`.`user` mapped to number 111
# at 907
#231127 15:25:01 server id 1  end_log_pos 963 CRC32 0x606fa283  Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `test`.`user`
### SET
###   @1=4
###   @2='ceshi2'
###   @3=20
###   @4='2023-11-27 15:24:59'
# at 963
#231127 15:25:01 server id 1  end_log_pos 994 CRC32 0x699d3676  Xid = 24
COMMIT/*!*/;
# at 994
#231127 15:30:37 server id 1  end_log_pos 1059 CRC32 0xe3e1102e         Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1059
#231127 15:30:37 server id 1  end_log_pos 1131 CRC32 0x77864565         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1701070237/*!*/;
BEGIN
/*!*/;
# at 1131
#231127 15:30:37 server id 1  end_log_pos 1184 CRC32 0xf290d665         Table_map: `test`.`user` mapped to number 111
# at 1184
#231127 15:30:37 server id 1  end_log_pos 1260 CRC32 0xfe6635ec         Delete_rows: table id 111 flags: STMT_END_F
### DELETE FROM `test`.`user`
### WHERE
###   @1=3
###   @2='ceshi'
###   @3=26
###   @4='2023-11-27 15:24:46'
### DELETE FROM `test`.`user`
### WHERE
###   @1=4
###   @2='ceshi2'
###   @3=20
###   @4='2023-11-27 15:24:59'
# at 1260
#231127 15:30:37 server id 1  end_log_pos 1291 CRC32 0xb85a735a         Xid = 72
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

第四步： 数据恢复

上面的内容，明显可以看出sql语句的执行情况。

找到我们之前插入的数据，每条sql的上下都有BEGIN和COMMIT，我们找到第一条sql，BEGIN后的第一个文件位置标识 at 291(这是文件的位置标识)，再找到第二条sql，COMMIT后面第一个文件位置标识 at 994

我们可以根据文件位置标识来恢复数据，首先要切换到MySQL的data目录下，执行如下命令：

# -u数据库账号 -p数据库密码  -v 要恢复的数据库名称（我这里是test数据库）
mysqlbinlog  --no-defaults --start-position=291 --stop-position=994 --database=test mysql-binlog.000001 | mysql -uroot -p123456 -v test

执行上面的命令后，就可以查询到被删除的数据了

补充数据恢复命令

# 补充一个根据时间来恢复数据的命令，我们找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1674833544，再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1674833663，转成datetime格式
mysqlbinlog  --no-defaults --start-datetime="2023-1-27 23:32:24" --stop-datetime="2023-1-27 23:34:23" --database=test mysql-binlog.000001 | mysql -uroot -p123456 -v test

如果要恢复大量数据，比如程序员经常说的删库跑路的话题，假设我们把数据库所有数据都删除了要怎么恢复了，如果数据库之前没有备份，所有的binlog日志都在的话，就从binlog第一个文件开始逐个恢复每个binlog文件里的数据，这种一般不太可能，因为binlog日志比较大，早期的binlog文件会定期删除的，所以一般不可能用binlog文件恢复整个数据库的。

一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据。

备份数据库一般可以用mysqldump 命令工具

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

mysql -u root test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test

代码007普通

打赏 收藏 海报 链接