mysql数据恢复日志路径_mysql的binlog日志记录和数据恢复

左手的ㄟ右手 2023-01-12 04:22 151阅读 0赞

1、binlog开启

1)、检查是否开启 binlog

注意：开启binlog后mysqld服务需要重启，请提前做好准备工作！

show variables like '%log\_bin%';

![c4ac63b399ef7776521c4cea082ebe43.png][]

2)、开启 binlog：

编辑 my.cnf 文件，在\[mysqld\]下面添加下面三个配置：

server-id = 1 \# (单个节点 id)

log-bin= /var/lib/mysql/mysql-bin \# 位置一般和 mysql 库文件所在位置一样

expire\_logs\_days = 10 \#表示此日志保存时间为 10 天,重启 mysqld

\# 再次查看 binlog 日志开启状态为 ON

![08f1f7976be147d7f42518974218187e.png][]

3)、参数说明：

Binlog 日志包括两类文件；第一个是二进制索引文件(后缀名为.index),第二个为日志文件(后缀名为.00000\*),记录数据库所有的 DDL 和 DML(除了查询语句 select)语句事件

4)、查看所有 binlog 日志文件列表:show master logs; 或者 SHOW BINARY LOGS;

![7197714e7ab4291f72179261eb25c6e0.png][]

5)、查看最后一个 binlog 日志的编号名称及其最后一个操作事件 pos 结束点的值：show master status;

![07bc3be4b79cc4d1280e7926f7adc8bf.png][]

6)、Flush logs 刷新日志，此刻开始产生一个新编号的 binlog 文件，例如：

![eb46e39fd188b5b69d8e43fd0e95e162.png][]

我们看到 mysql-bin.000004 这个是刚刚生成的。

注意：每当 mysqld 服务重启时，会自动执行刷新 binlog 日志命令，mysqldump 备份数据时加-F 选项也会刷新 binlog 日志

7)、清空所有 binlog 日志命令:reset master; (慎用！！！)

8)、查看 biblog 内容：

使用 mysqlbinlog binlog\_files

![8d8a4ea3a5b395a4e479a8cbd357472e.png][]

该方式不好观察，使用 show binlog events in 'ysql-bin.000004'; 这种形式可以方便的观察内容。从指定索引位置开始查看： show binlog events in 'mysql-bin.000004' from 3183;

![7d8d98c8f80568322176788ec8aab2c4.png][]

show binlog events in 'mysql-bin.000004' from 3183 limit 2,4; 分页查询

![90152732260cec449f7922b2205adf6c.png][]

2、使用 binlog 恢复数据

mysqlbinlog 的使用说明：

https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html

先使用 flush logs;新创建 binlog 文件，然后创建表，新增数据。

测试数据为：

CREATE TABLE \`emp\` (

\`eid\` int(20) NOT NULL,

\`ename\` varchar(10) DEFAULT NULL,

\`birth\` date DEFAULT NULL,

\`sal\` double DEFAULT NULL,

\`did\` int(20) DEFAULT NULL,

\`passwd\` varchar(20) DEFAULT NULL,

\`degree\` varchar(255) DEFAULT NULL,

\`sex\` int(11) NOT NULL,

PRIMARY KEY (\`eid\`),

KEY \`emp\_name\_sal\` (\`ename\`,\`sal\`) USING BTREE,

KEY \`idx\_birth\` (\`birth\`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO \`bart\`.\`emp\` (\`eid\`, \`ename\`, \`birth\`, \`sal\`, \`did\`, \`passwd\`, \`degree\`, \`sex\`) VALUES

('3', 'bart', '2017-07-03', '10000', '2', '123', '\\'本科\\'', '1');

INSERT INTO \`bart\`.\`emp\` (\`eid\`, \`ename\`, \`birth\`, \`sal\`, \`did\`, \`passwd\`, \`degree\`, \`sex\`) VALUES

('4', 'homer', '2017-07-20', '9000', '2', '123', '硕士', '1');

INSERT INTO \`bart\`.\`emp\` (\`eid\`, \`ename\`, \`birth\`, \`sal\`, \`did\`, \`passwd\`, \`degree\`, \`sex\`) VALUES

('5', 'marge', '2017-07-22', '8000', '3', '123', '本科', '1');

INSERT INTO \`bart\`.\`emp\` (\`eid\`, \`ename\`, \`birth\`, \`sal\`, \`did\`, \`passwd\`, \`degree\`, \`sex\`) VALUES

('6', 'xg', NULL, '8500', '1', NULL, '博士', '0');

INSERT INTO \`bart\`.\`emp\` (\`eid\`, \`ename\`, \`birth\`, \`sal\`, \`did\`, \`passwd\`, \`degree\`, \`sex\`) VALUES

('7', 'xg', NULL, '8500', '1', NULL, '硕士', '1');

INSERT INTO \`bart\`.\`emp\` (\`eid\`, \`ename\`, \`birth\`, \`sal\`, \`did\`, \`passwd\`, \`degree\`, \`sex\`) VALUES

('10', 'aaa', '2017-09-09', '123.456', '1', NULL, '本科', '1');

INSERT INTO \`bart\`.\`emp\` (\`eid\`, \`ename\`, \`birth\`, \`sal\`, \`did\`, \`passwd\`, \`degree\`, \`sex\`) VALUES

('11', 'bbb', NULL, '9000', '1', NULL, '博士', '1');

INSERT INTO \`bart\`.\`emp\` (\`eid\`, \`ename\`, \`birth\`, \`sal\`, \`did\`, \`passwd\`, \`degree\`, \`sex\`) VALUES

('12', 'ccc', NULL, '10000', '2', '123', '硕士', '0');

INSERT INTO \`bart\`.\`emp\` (\`eid\`, \`ename\`, \`birth\`, \`sal\`, \`did\`, \`passwd\`, \`degree\`, \`sex\`) VALUES

('13', 'ddd', NULL, '12000', '3', '123', '本科', '1');

将测试数据导入库中后，查看日志：show master logs;

![61920d80edf8026d49399d4207916a5b.png][]

此时有两个日志，第1个是我们创建表和插入数据之前的，第2 个是记录我们刚刚的操作的。

我们现在故意制造一些“生产事故”，比如：现在不小心把所有的 ename 字段全部更新成了 NULL update emp set ename = NULL;因为已经 commit 所以此时使用 rollback 已经无效。 次数数据已经被损坏，我们需要修复到修改之前的状态。

恢复

通过 pos 恢复

1、 出错之后先 flush logs; 保存当前的 log，生成新的 log 是为了将旧的 log 隔离，方便我们恢复，因为接下来的操作会进入新的 log 中。

![f9ad3c49f75ea6909dab39cb88cdb7d1.png][]

其中标红的就是记录我我们刚刚操作的那个 log。

2、查询日志： show binlog events in 'mysql-bin.000001' ;

![48c0d9a5e5e4b49bc1a2d45603b46b0e.png][]

可以看到红圈标注的是 update 操作的 pos，在他之前有个 begin 事物开始点(pos 是 3745)之后有个 commit 事务提交(pos 是 4501)我们需要恢复没有被更新之前的状态，所以我们的使用 pos 恢复的 stop-postion 就是事务的开始索引位置即 3745，恢复命令为：

/usr/bin/mysqlbinlog --stop-position=3745 --database=bart /var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -uroot -p123456 -v

成功后，查看 emp 发现被置位 NULL 的 ename 字段又恢复了。

通过时间恢复

人工制造一个“事故”，比如：不小心执行了命令：drop database bart; 直接把库删除了(这个命令生产中慎用！)

1、先保护现场，flush logs;

2、查看日志文件: show binlog events;

先创建被删除的数据库 createdatabase bart;、查看日志show binlog events in 'mysql-bin.000001' ; 找到创建表的日志开始位置：

![55284b94fdfdc0bb4fe9a66b63bcde45.png][]

3、在找到修改表之前的位置：

![f1b82e4f9d08801f97aaba8d721cc797.png][]

4、恢复语句：

(1、position 方式)

/usr/bin/mysqlbinlog --start-position=400 --stop-position=3745 --database=bart /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p123456 -v

(2、time 方式)

首先我们使用 mysqlbinlog \[binlog\_files\] 查看日志，找到需要恢复的时间段：

找到创建表的位置：

![f6396c2b99631a854a7204ea80b65059.png][]

找到更新表之前的位置：

![3fb766cdac3e25741a97e2ef647f3720.png][]

使用命令为：

/usr/bin/mysqlbinlog --start-datetime='2019-10-25 17:00:35' --stop-datetime='2019-10-25 17:01:42' --database=bart /var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -uroot -p123456

\-v

发现数据又回来了

[c4ac63b399ef7776521c4cea082ebe43.png]: /images/20221119/ea30640201a844188c4bec23f3fd1de2.png
[08f1f7976be147d7f42518974218187e.png]: /images/20221119/aef6a01e2bfd4808a2a9fb73e1a61d13.png
[7197714e7ab4291f72179261eb25c6e0.png]: /images/20221119/4fa5eab1fd4b4d18a27f122663c073bd.png
[07bc3be4b79cc4d1280e7926f7adc8bf.png]: /images/20221119/dde96f5481724ca7916aa494e97e16e1.png
[eb46e39fd188b5b69d8e43fd0e95e162.png]: /images/20221119/052c6dc777ee45728be9eaf161952bbc.png
[8d8a4ea3a5b395a4e479a8cbd357472e.png]: /images/20221119/2d78238c236348abb24c31aa86825975.png
[7d8d98c8f80568322176788ec8aab2c4.png]: /images/20221119/eb44db07cc65485c8a31dc1426cb2faa.png
[90152732260cec449f7922b2205adf6c.png]: /images/20221119/bd89a414595a4e76b90465e5df173675.png
[61920d80edf8026d49399d4207916a5b.png]: /images/20221119/869de9adb0a84574974c8804428ea723.png
[f9ad3c49f75ea6909dab39cb88cdb7d1.png]: /images/20221119/4b41d0a6bf234f4783445dfa2a7105c5.png
[48c0d9a5e5e4b49bc1a2d45603b46b0e.png]: /images/20221119/df7d52ddf14343efb747d49349ec4b29.png
[55284b94fdfdc0bb4fe9a66b63bcde45.png]: /images/20221119/ba56867a57a546509b961ab2e6909ffd.png
[f1b82e4f9d08801f97aaba8d721cc797.png]: /images/20221119/b93be5c4bc50405f841725ca9777902c.png
[f6396c2b99631a854a7204ea80b65059.png]: /images/20221119/8539a3a3341146319903ff4c3fd89a39.png
[3fb766cdac3e25741a97e2ef647f3720.png]: /images/20221119/ff505a55c9ba407c960263f9fa04b6e1.png

发表评论取消回复

表情：

评论列表（有 0 条评论，151人围观）

还没有评论，来说两句吧...

相关阅读

相关 Mysql-BinLog数据恢复

介绍 binlog,即二进制日志,它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中；它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制（主

雨点打透心脏的1/2处/ 2023年10月02日 18:38/ 0 赞/ 17 阅读

相关 mysql 恢复日志文件_利用 MySql日志文件恢复数据(技巧)

2. 要想通过日志恢复数据库，在你的my.cnf文件里应该有如下的定义，log-bin=mysql-bin，这个是必须的.binlog-do-db=db\_test,这个是指定

桃扇骨/ 2023年01月12日 07:41/ 0 赞/ 202 阅读

相关 mysql数据恢复日志路径_mysql的binlog日志记录和数据恢复

1、binlog开启 1)、检查是否开启 binlog 注意：开启binlog后mysqld服务需要重启，请提前做好准备工作！ show variables like '

左手的ㄟ右手/ 2023年01月12日 04:22/ 0 赞/ 152 阅读

相关 c mysql清理日志_mysql删除binlog日志及使用日志恢复数据的方法

mysql中开启binlog功能，主要是为了方便日后恢复数据。而开启binlog的坏处是日志文件增长速度快，很快占会占满磁盘空间，要定期或手工来删除过大的日志文件。删除

快来打我*/ 2022年10月31日 00:58/ 0 赞/ 201 阅读

相关 mysql 恢复日志文件_教你自动恢复MySQL数据库的日志文件(binlog)

如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。“mysqlb

冷不防/ 2022年10月27日 02:40/ 0 赞/ 205 阅读

相关 Mysql通过binlog日志文件恢复数据

mysqlbinlog 官方操作文档：[https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html][https_dev.

野性酷女/ 2022年10月02日 07:46/ 0 赞/ 223 阅读

相关 mysql binlog恢复数据

常用binlog操作命令： 1、查看所有binlog日志列表 mysql> show master logs; 2、查看当前正在写入的binlog文件 mysql

叁歲伎倆/ 2022年09月25日 01:16/ 0 赞/ 222 阅读

相关 mysql恢复数据库方法binlog日志来恢复数据库

在使用binlog日志来恢复数据库之前，我们有一些前提工作需要做。步骤如下： 1、创建新的数据库ailanni 2、创建新的表ilannitable 3、向表ilanni

- 日理万妓/ 2022年08月14日 01:40/ 0 赞/ 286 阅读

相关 MySQL使用binlog日志做数据恢复

MySQL的binlog日志是MySQL日志中非常重要的一种日志，记录了数据库所有的DML操作。通过binlog日志我们可以进行数据库的读写分离、数据增量备份以及服务器宕机时的

系统管理员/ 2022年06月13日 14:37/ 0 赞/ 204 阅读

相关 Mysql之binlog日志说明及利用binlog日志恢复数据操作记录

众所周知，binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下，我们往往会想到用binlog日志功能进行数据恢复（定时全备份+binlog日志恢复增量数

男娘i/ 2022年05月20日 00:29/ 0 赞/ 448 阅读