【MySQL】binlog数据恢复

最新推荐文章于 2024-05-22 15:56:29 发布
最新推荐文章于 2024-05-22 15:56:29 发布
阅读量486 收藏 1
点赞数 1
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40042416/article/details/134645454
版权

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
@琴酒**
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql--使用binlog 来恢复数据
ylx814056815的博客
08-22 211
mysql–使用binlog 来恢复数据 一、数据准备 CREATE TABLE `course2` ( `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '课程id', `name` varchar(10) DEFAULT NULL COMMENT '课程名称', `tid` bigint(0) DEFAULT NULL COMMENT '教师id', PRIMARY KEY (`id`) USING BTREE ) ENGINE = I
数据库binlog恢复数据
狂躁的辣条的博客
04-07 330
数据库binlog恢复数据 由于不想影响生产库的数据，所以我们将binlog 日志迁移到其他机器上进行恢复数据 将mysql的所有数据复制出来 docker cp b3cb037be179:/var/lib/mysql/ /home 重新命名后 然后另外创建一个mysql 进行查看数据 vim docker-compose.yaml version: '3' services: mysql: restart: always image: mysql:5.7.30 conta
binlog恢复数据
开心就好
04-28 6095
文章目录mysql之binlog查看是否开启binlog日志：查找最新的binlog:查询偏移量：数据恢复（执行报错）：查看官方文档：binlog生成sql：grep搜索sql语句：binlog解析mysqlbinlog 常用参数：开启 binlog 日志my.ini配置：其中 binlog-format 有三种选项：其他：恢复单条记录：恢复单表记录：恢复数据库记录：sql语法在线检查： mysq...
mysql误删后使用binlog恢复数据
最新发布
m0_70208894的博客
05-22 1200
（这里我们是要测试binlog恢复数据的使用，所以就日志文件都放到一个全新binlog文件中方便查询使用，如果是实际恢复数据的话，就要一个一个的在这些binlog文件中找自己要的那部分文件了。数据恢复：通过恢复误删操作之后的操作记录，可以将误删的数据重新插入到数据库中，还原到误删之前的状态。这将重新执行误删操作之后的操作，从而还原到误删前的数据状态。假设我们的数据库是7天一备份，然后binlog的过期时间是大于7天的，那么通过备份的数据库+binlog文件就能够恢复数据库到达7天内的任意一个时间点的状态。
mysql通过binlog来恢复被删除的数据库
小韭菜的博客
06-30 1140
mysql通过binlog来恢复被删除的数据库
利用MySQL的binlog恢复误删的数据库
qq_45228323的博客
05-21 1008
备份数据mysqldump - uroot - p123456 test - l - F '/tmp/test.sql' - l:读锁（只能读取，不能更新） - F:即flush logs，可以重新生成新的日志文件，当然包括log - bin日志查看binlog日志导入之前备份数据mysql - uroot - p t - v - f < / mnt / t . sql - v查看导入的详细信息 - f是当中间遇到错误时，可以skip过去，继续执行下面的语句恢复binlog-file二进制日志文件。
mysql利用binlog恢复数据
helam的专栏
10-20 439
binlog日志对于mysql数据库来说是十分重要的，在数据丢失或错误变更的紧急情况下，我们往往用binlog日志功能进行数据恢复（定时全备份+binlog日志恢复增量数据部分） 一、简单了解binlog MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。 - DDL Data Definition Language 数据库定义语言 主要的
mysql binlog日志恢复【亲测有效，有例子实测】
03-22
2. **数据恢复**：当发生数据丢失或错误时，可以通过mysqlbinlog工具解析binlog文件并应用相应的操作来恢复数据。这在灾难恢复或误操作后恢复数据时非常有用。 以下是如何在MySQL中配置和使用binlog的具体步骤： 1...
mysql.binlog数据恢复
12-14
这是一个eclipse安装插件，在oxygen下测试是可用的。...操作：1、获取binlog文件 2、选中要处理的部分 3、选择菜单“jhh->binlog->”下对应的操作 4、会在相同目录下生成sql文件（文件名相同，加了sql后缀）
mysql如何利用binlog进行数据恢复详解
12-16
MySQL的二进制日志（Binary Log，简称binlog）是数据库系统中至关重要的功能，主要用于数据恢复和主从同步。在本篇文章中，我们将深入探讨如何利用binlog进行数据恢复，以及binlog的基本配置和格式。 首先，当发生...
使用binlog日志恢复MySQL数据库删除数据的方法
01-11
在实际的数据恢复过程中，假设我们发现数据被删除，可以按照以下步骤操作： 1. 确认binlog在删除操作发生时已经开启。 2. 找到最近的备份文件，确定备份时间点。 3. 使用`mysqlbinlog`找出从备份时间点到删除操作...
MySQL数据库恢复(使用mysqlbinlog命令)
09-11
binlog是通过记录二进制文件方式来备份数据，然后在从二进制文件将数据恢复到某一时段或某一操作点。
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
08-04
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
Mysql 通过 binlog日志 恢复数据（数据搞丢看过来）
乌龟的专栏
11-17 1162
Mysql 通过 binlog日志 恢复数据（数据搞丢看过来）
使用binlog日志恢复数据
彭淦淦的博客
04-07 370
5.1 问题 利用binlog恢复库表，要求如下： 启用binlog日志 创建db1库tb1表，插入3条记录 删除tb1表中刚插入的3条记录 使用mysqlbinlog恢复删除的3条记录 5.2 步骤 实现此案例需要按照如下步骤进行。 步骤一：启用binlog日志 1）调整/etc/my.cnf配置，并重启服务 [root@dbsvr1 ~]# vim /etc/my.cnf [mysqld]...
MySQL——通过binlog恢复数据
热门推荐
醉酒的戈多的专栏
04-07 4万+
二进制日志(binnary log)以事件形式记录了对MySQL数据库执行更改的所有操作。binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE、DROP等）以及表数据修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。
MySQL数据库binlog恢复_烂泥：通过binlog恢复mysql数据库
weixin_42309866的博客
01-28 547
在上一篇文章，我们讲解了有关mysql的binlog日志的基础知识。这篇文章，我们来讲解如何通过mysql的binlog日志来恢复数据库。在使用binlog日志来恢复数据库之前，我们有一些前提工作需要做。步骤如下：1、创建新的数据库ailanni2、创建新的表ilannitable3、向表ilannitable插入数据4、刷新mysqlbin日志5、完整备份ailanni数据库6、删除表ilann...
MySQL使用binlog操作恢复数据
chenc梧高的博客
03-16 508
文章目录一 、binlog简介二、binlog的格式1）Statement2）Row3）Mixed三、配置和查看binlog格式1）修改binlog格式并配置MySQL日志文件2）查看binlog的格式四、创建数据库并备份数据1）创建数据库2） 创建完毕，刷新binlog文件五、使用备份恢复数据库并使用binlog来完成回滚操作1）进入数据库并插入数据库2）查看数据3）update修改数据库，模拟...
MYSQL使用binlog日志恢复数据
好吃的油炸代码
08-23 4767
注意事项：本教程目的在于，你的MYSQL采用了binlog日志，且产生了binlog日志文件，使用日志文件恢复数据。
mysql binlog 数据恢复
08-21
MySQL的binlog是以事件形式记录了对数据库执行更改的所有操作。在数据恢复方面，可以使用mysqlbinlog工具来恢复数据。 首先，你可以使用mysqlbinlog工具来解析和转换二进制日志文件。使用命令"mysqlbinlog --start-datetime="开始时间" --stop-datetime="结束时间" --database=数据库名 binlog文件名 | mysql -u用户名 -p密码"，其中开始时间和结束时间是你想要恢复的时间范围，数据库名是你要恢复的数据库，用户名和密码是连接数据库所需的凭据。这个命令会将binlog文件中指定时间范围内的操作转换为SQL语句并执行，从而恢复数据。 另外，在每次重启MySQL服务时，也会自动刷新binlog日志。此外，使用mysqldump备份数据时，如果加上-F选项，也会刷新binlog日志。所以，如果你想要恢复到最新的binlog状态，可以重启MySQL服务或者进行一次完整的备份。 需要注意的是，binlog只能记录数据库的更改操作，而不能记录表结构的变更。如果你的目标是恢复表结构的变更，可以考虑使用其他工具或备份文件来恢复。 总之，通过使用mysqlbinlog工具以及重启MySQL服务或进行完整的备份，你可以对MySQL的binlog进行数据恢复。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [mysql binlog日志恢复【亲测有效，有例子实测】](https://download.csdn.net/download/Jay_Fred/87602788)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] - *2* *3* [MySQL binlog 数据恢复](https://blog.csdn.net/qq_39550368/article/details/126901112)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值