mysql使用binlog日志恢复mysql数据_centos mysql启动binlog日志-CSDN博客

本文链接：https://blog.csdn.net/smallfishy/article/details/119042363

并非完全原创，但是自我感觉将其中许多的易错点，坑都着重强调了一下。

---------------------------------------------------------

知识补充，可直接略过

---------------------------------------------------------

环境介绍：Centos8.4+Mysql8.0.25

1、开启mysql binlog日志（仅供参考）

show variables like '%log%bin%';

vi /etc/my.cnf

在mysqld区域内添加如下内容：

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

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

expire_logs_days = 10(表示此日志保存时间为10天)

重启mysqld,再次查看binlog日志开启状态为ON

show variables like '%log%bin%';

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

3、查看所有binlog日志文件列表:show master logs;

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

5、查看binlog文件内容，使用查看工具mysqlbinlog来查看(cat/vi/more都是无法打开的)

mysqlbinlog /var/lib/mysql/binlog.000003

6、方便查看命令：

show binlog events in 'binlog.000003’;

7、指定查询,从pos点308开始查询，如下：

show binlog events in 'mysql-bin.000003' from 308;

---------------------------------------------------------

利用binlog日志恢复mysql数据

---------------------------------------------------------

1、确认是否已开启BIN-LOG

show variables like '%log_bin%';

2、查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值

show master status;

3、背景描述：

数据库qq下里面有一张表ID

4、现在将此数据库备份到本地(模拟每周的备份情况)，备份命令如下

mysqldump -uroot -p'&@j5ggpBP6@PJ8#k' --databases qq > qq.sql

5、数据备份之前或者之后执行flush logs重新生成一个binlog日志用来记录备份之后的所有增删改操作(重新生成日志更好找pos点)，由于业务需求，现在对表进行插入

6、由于操作失误，误删除了数据库，所有数据都不见了。此时可以通过binlog日志恢复数据，由于之前有做了数据库备份，所以可以先将备份的数据导入进去，剩下缺少的就是备份之后操作所产生的内容(备份之后执行了插入内容以及更改内容)，先恢复备份的数据：创建库qq并选择库(use qq)，通过命令source /root/qq.sql将数据库内容导入，然后执行

7、上面恢复的数据只是截止到备份时间的数据，剩下缺少的数据可以通过binlog日志来恢复。

通过下面的查询，我们知道最后一条指令应该在

执行

mysqlbinlog --start-position=308--stop-position=825 --database=qq /root/binlog.000012 | /usr/bin/mysql -uroot -p'&@j5ggpBP6@PJ8#k' -v qq

注意：

Event_type事件类型解释：

QUERY_EVENT：与STATEMENT模式处理相同，存储的是SQL，主要是一些与数据无关的操作，eg: begin、drop table、truncate table 等；
TABLE_MAP_EVENT：记录了下一条事件所对应的表信息，在其中存储了数据库名和表名；(test.a_view)
WRITE_ROWS_EVENT：操作类型为insert；（insert）
UPDATE_ROWS_EVENT：操作类型为update；（update）
DELETE_ROWS_EVENT：操作类型为delete；（delete）
XID_EVENT，用于标识事务提交。

所以，上面例子中恢复应该是从 308 - 825 。

所以，308-825才是完整的操作流程。

8、同样可以使用时间戳来恢复数据。

先把数据删掉，使用mysqlbinlog binlog.000012 查看开始和结束时间点

/usr/bin/mysqlbinlog --start-datetime="2021-07-23 6:06:17" --stop-datetime="2021-07-23 6:07:24" --database=qq /root/binlog.000012 | /usr/bin/mysql -uroot -p'&@j5ggpBP6@PJ8#k' -v qq

注意：

关于时间点，需要说明的是，可以稍微往前或往后一点点。比如，我尝试了多次 "2021-07-23 6:06:17" - "2021-07-23 6:06:39" ，都是只有插入，没有更新操作。

于是，改成了 "2021-07-23 6:06:17" - "2021-07-23 6:07:24" 才得到想要的结果。