binlog 数据恢复(linux)

跳跳小强

已于 2022-10-23 09:40:55 修改

阅读量1.7k

点赞数 1

分类专栏： mysql 文章标签： linux 数据库 mysql

于 2022-10-02 10:29:59 首次发布

本文链接：https://blog.csdn.net/qq_55316925/article/details/127140282

版权

mysql 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍MySQL Binlog日志的记录模式、开启方法及常用指令，并通过具体案例演示如何利用Binlog进行数据恢复，包括误删数据恢复、基于位置和时间点的数据恢复。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

友善的导航

binlog 数据恢复

binlog 数据恢复

binlog是mysql用来记录数据库表结构变更以及表数据修改的的二进制日志，它只会记录表的变更操作，但不会记录select和show这种查询操作。

使用场景

数据恢复- 误删数据之后可以通过mysqlbinlog工具恢复数据
审计- 可以通过二进制日志中的信息进行审计，判断是否对数据库进行注入攻击

本文主要针对数据恢复这个用途进行一个示例分析

binlog文件类型：

索引文件（文件名后缀为.index）用于记录哪些日志文件正在被使用
日志文件（文件名后缀为.00000*）记录数据库所有的DDL(create,drop,alter)和DML(除了数据查询语句)语句事件。

最有用的就是这个binlog日志文件，里边记录的是对数据库的各种操作，你一定会很好奇binlog是怎么记录数据库操作的。其实binlog了给我们提供了3种记录模式：

row模式：记录修改语句和修改的所有数据—推荐模式
statement模式：仅记录语句，即show和select之外的语句
mixed模式：综合上述两种记录方式

记录模式	优点	缺点
ROW	能清楚记录每一个行数据的修改细节	批量操作，会产生大量的日志，尤其是alter table会让日志文件大小暴涨
STATEMENT	日志量小，减少磁盘IO，提升存储和恢复速度	在某些情况下会导致主从数据不一致，比如Sql语句中有last_insert_id()、now()等函数。
MIXED	准确性强，文件大小适中	当binlog format 设置为mixed时，普通复制不会有问题，但是级联复制在特殊情况下会binlog丢失。

开启binlog以及常用指令

#1.查看是否开启了binlog
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.000 sec)

#2.如若未开启，需要配置配置文件将其开启
┌──(root💀kali)-[~]
└─# vim /etc/mysql/mariadb.conf.d/50-server.cnf

请添加图片描述

#3.重启mysql，继续查看状态
┌──(root💀kali)-[~]
└─# service mariadb restart  
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.001 sec)
#4.查看文件是否生成
┌──(root💀kali)-[~]
└─# ll /var/lib/mysql

请添加图片描述
注：

centos系统进行操作时，使用如下方案

#1.修改配置文件mariadb-server.cnf
[root@black_bat my.cnf.d]# ll
total 20
-rw-r--r--. 1 root root   41 Apr 27 02:42 auth_gssapi.cnf
-rw-r--r--. 1 root root  295 May 17  2021 client.cnf
-rw-r--r--. 1 root root  763 Nov  6  2021 enable_encryption.preset
-rw-r--r--. 1 root root 1654 Sep 21 23:18 mariadb-server.cnf
-rw-r--r--. 1 root root  232 Nov  6  2021 mysql-clients.cnf
#2.文件内部的mysqld内部添加如下语句
server_id=2        #mysql5.7版本开启binlog强制需要添加该参数
log_bin=mysql-bin  #表示开启binlog并指定binglog文件名
binlog_format=ROW  #默认
expire_logs_days=7 #binlog保留天数
#3.重启服务，找不到服务就展示所有查看一下，不是mysql就是mariadb
[root@black_bat my.cnf.d]# systemctl restart mariadb.service

日志信息查看

#1.查看日志记录类型
MariaDB [(none)]> show variables like '%format%';
+----------------------------+-------------------+
| Variable_name              | Value             |
+----------------------------+-------------------+
| binlog_format              | MIXED             |
| date_format                | %Y-%m-%d          |
| datetime_format            | %Y-%m-%d %H:%i:%s |
| default_week_format        | 0                 |
| innodb_default_row_format  | dynamic           |
| innodb_file_format         |                   |
| mysql56_temporal_format    | ON                |
| time_format                | %H:%i:%s          |
| wsrep_forced_binlog_format | NONE              |
+----------------------------+-------------------+
9 rows in set (0.000 sec)

#2.显示日志文件信息
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.000 sec)

#3.查看日志详细信息
  #mysql内部查看
show binlog events in 'name'\G
  #全局调用配套工具mysqbinlog 
  mysqlbinlog 文件路径 
例：mysqlbinlog mysql-bin.000001
#4.显示最新的日志
show master status;

示例：查看日志详细信息

MariaDB [(none)]> show binlog events in 'mysql-bin.000001' \G;

请添加图片描述
另一种查看方式

┌──(root💀kali)-[/var/lib/mysql]
└─# mysqlbinlog mysql-bin.000001

请添加图片描述

日志信息刷新

从此刻开始刷新一个日志文件。

在重启数据库是这个命令是回自动执行的；另外在mysqldump备份数据的时候 -F选项也会刷新binglog日志

flush logs;

示例：

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.000 sec)

MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       375 |
| mysql-bin.000002 |       371 |
+------------------+-----------+
2 rows in set (0.000 sec)

日志重置

reset master;

清空当前所有日志，重新生成一个新的01号log文件，其效果等同于删除当前所有binlog文件+flush
示例：

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       375 |
| mysql-bin.000002 |       371 |
+------------------+-----------+
2 rows in set (0.000 sec)

MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.000 sec)

数据恢复

下面使用自建环境进行测试;

环境搭建

#1.创建测试库
MariaDB [(none)]> create database test;
#2.创建测试表
MariaDB [(none)]> use test;
MariaDB [test]> create table member(
    -> id int(10) unsigned not null primary key auto_increment,
    -> name varchar(20) not null,
    -> age int(3) not null default 18,
    -> works varchar(50) not null
    -> );
#3.测试表插入数据
MariaDB [test]> insert into member (name,works) values ('wangbo','twgx'),('sushi','dingfengbo'),('baijuyi','chg');
#4.测试结果
MariaDB [test]> select * from member;
+----+---------+-----+------------+
| id | name    | age | works      |
+----+---------+-----+------------+
|  1 | wangbo  |  18 | twgx       |
|  2 | sushi   |  18 | dingfengbo |
|  3 | baijuyi |  18 | chg        |
+----+---------+-----+------------+
3 rows in set (0.000 sec)

场景模拟

1.首先，我们每天凌晨4点对于mysql会有一次全量备份，这里使用mysqldump进行演示。

#文件备份
mysqldump -uroot -proot -B -F -x --master-data=2 test|gzip > /tmp/test_$(date +'%Y%m%d').sql.gz
#查看备份
ll /tmp

参数说明：
-B：指定数据库
-F：刷新日志
-R：备份存储过程
-x(小写)：锁表
-master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件以及其位置信息
-X：以xml文件格式保存数据，这样保存的数据直接导入是导不进去的！！！

那么我们需要思考一下，此时的数据会备份到哪里去

MariaDB [test]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1047 |
| mysql-bin.000002 |       385 |
+------------------+-----------+
2 rows in set (0.000 sec)

MariaDB [test]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      385 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

因为新生成了一份日志文件，即就是4点前的操作已经备份进了gzip文件，而四点后的所有操作都会记录到binlog文件内部。即就是此处的02文件内部。
2.九点我们到公司后对数据进行了一些修改

MariaDB [test]> insert into member (name,works) values ('libai','jingyesi');
Query OK, 1 row affected (0.001 sec)

MariaDB [test]> select * from member;
+----+---------+-----+------------+
| id | name    | age | works      |
+----+---------+-----+------------+
|  1 | wangbo  |  18 | twgx       |
|  2 | sushi   |  18 | dingfengbo |
|  3 | baijuyi |  18 | chg        |
|  4 | libai   |  18 | jingyesi   |
+----+---------+-----+------------+
4 rows in set (0.000 sec)

MariaDB [test]> update member set name='litaibai' where id=4;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> update member set name='sudongpo' where id=2;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from member;
+----+----------+-----+------------+
| id | name     | age | works      |
+----+----------+-----+------------+
|  1 | wangbo   |  18 | twgx       |
|  2 | sudongpo |  18 | dingfengbo |
|  3 | baijuyi  |  18 | chg        |
|  4 | litaibai |  18 | jingyesi   |
+----+----------+-----+------------+
4 rows in set (0.000 sec)

3.到了下午六点，唉，哥们手滑删了个数据库

MariaDB [(none)]> drop database test;
Query OK, 1 row affected (0.003 sec)

怎么办呢，此时英俊潇洒的你不慌不忙的找到了这个解决方案

#1.备份当前使用的binlog日志文件
┌──(root💀kali)-[/var/lib/mysql]
└─# cp -v mysql-bin.000002 /tmp
'mysql-bin.000002' -> '/tmp/mysql-bin.000002'

#2.刷新日志文件，将欲恢复部分和新进入的操作语句隔离开
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1047 |
| mysql-bin.000002 |      1158 |
| mysql-bin.000003 |       385 |
+------------------+-----------+
3 rows in set (0.000 sec)
#3.恢复先前的备份zip文件
	#解压文件
	gzip -d /tmp/test_20220930.sql.gz
	#导入数据
	mysql -uroot -proot < /tmp/test_20220930.sql
#查看此时的恢复情况---恢复到了4点以前，但是我们后续进行的操作并没有进行相应的一个备份
MariaDB [test]> select * from member;
+----+---------+-----+------------+
| id | name    | age | works      |
+----+---------+-----+------------+
|  1 | wangbo  |  18 | twgx       |
|  2 | sushi   |  18 | dingfengbo |
|  3 | baijuyi |  18 | chg        |
+----+---------+-----+------------+
3 rows in set (0.000 sec)

#4.分析我们刚刚备份的日志文件
┌──(root💀kali)-[/tmp]
└─# mysqlbinlog mysql-bin.000002

请添加图片描述
我们看到了这条万恶的语句，此时我们需要做的就是将其重定向到一个新sql文件，对错误语句进行删除。重新导入数据库即可

#5..重定向日志文件
┌──(root💀kali)-[/tmp]
└─# mysqlbinlog mysql-bin.000002 > 002.sql
#6.删除错误语句之后进行重新导入
┌──(root💀kali)-[/tmp]
└─# mysql -uroot -proot < 002.sql
#7.查看恢复后的数据---数据恢复完毕，恭喜你，暂时不会被和谐掉
MariaDB [test]> select * from member;
+----+----------+-----+------------+
| id | name     | age | works      |
+----+----------+-----+------------+
|  1 | wangbo   |  18 | twgx       |
|  2 | sudongpo |  18 | dingfengbo |
|  3 | baijuyi  |  18 | chg        |
|  4 | litaibai |  18 | jingyesi   |
+----+----------+-----+------------+
4 rows in set (0.000 sec)

扩展恢复

环境：恢复之前的4点状态

#1.删除数据库
MariaDB [(none)]> drop database test;
#2.导入4点的备份数据
mysql -uroot -proot < test_20220930.sql
#3.查看数据
MariaDB [test]> select * from member;
+----+---------+-----+------------+
| id | name    | age | works      |
+----+---------+-----+------------+
|  1 | wangbo  |  18 | twgx       |
|  2 | sushi   |  18 | dingfengbo |
|  3 | baijuyi |  18 | chg        |
+----+---------+-----+------------+
3 rows in set (0.000 sec)

基于pso区间的恢复

#1.查看详细日志信息
MariaDB [test]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1047 |
| mysql-bin.000002 |      1158 |
| mysql-bin.000003 |      3647 |
+------------------+-----------+
3 rows in set (0.000 sec)

MariaDB [test]> show binlog events in 'mysql-bin.000002';

请添加图片描述

#2.用mysqlbinlog进行恢复
mysqlbinlog --start-position=459 --stop-position=842 --database=test mysql-bin.000002| mysql -uroot -proot -v
#3.查看恢复情况
MariaDB [test]> select * from member;
+----+---------+-----+------------+
| id | name    | age | works      |
+----+---------+-----+------------+
|  1 | wangbo  |  18 | twgx       |
|  2 | sushi   |  18 | dingfengbo |
|  3 | baijuyi |  18 | chg        |
|  4 | litaibai|  18 | jingyesi   |
+----+---------+-----+------------+

基于时间点恢复

mysqlbinlog mysql-bin.000002 --stop-date='回复时间点位置' > resume.sql

注：要看到时间点的话，还是需要使用mysqlbinglog查看日志详细信息