第七篇：MySQL数据库日志管理

数据库日志

常见的服务层数据库日志

服务层：记录数据库的运行状态、逻辑操作、数据变更历史

引擎层：保证事务的原子性、持久性，实现多版本并发控制（redo log、undo log）

服务层日志	解释说明
general_log	表示查询日志（通用日志），默认日志状态处于关闭，可以进行在线调整配置作用：记录了客户端从会话连接开始，执行过的所有SQL语句信息；
log_error	表示错误日志（运行日志），默认日志状态处于激活作用：记录了数据库服务启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息；
log_bin	表示二进制日志（binlog日志），默认日志状态处于激活（8.0之后）记录了所有的DDL语句和DML语句，但是不包括数据库查询语句；作用：可以实现数据恢复(增量恢复) / 可以实现数据同步
slow_query_log	表示慢查询日志，默认日志状态处于关闭记录了所有执行时间超过参数long_query_time设置值并且扫描记录数小min_examined_row_limit的所有SQL语句的日志。作用：关注并优化慢查询语句，可以减少对磁盘IO的消耗

通用日志（general_log）⭐⭐

说明：企业环境中，因日志量巨大，建议按需开启此功能，支持在线动态调整

mysql> select @@general_log;  -- 通用日志是否开启参数
mysql> select @@general_log_file;  -- 通用日志存储路径参数
mysql > set global general_log=1;  -- 临时修改日志默认状态（激活日志）
[root@db01 ~]# vim /etc/my.cnf  -- 永久修改
[mysqld]
general_log=OFF  -- 默认日志功能处于关闭，建议在需要做调试工作时（功能测试、语句审计）可以打开；
general_log_file=/data/3306/logs/general/general01.log  -- 指定日志存储路径
  -- 该目录得存在，且所有者改为mysql用户

错误日志（log_error）⭐⭐⭐

说明：企业真实环境，日志处于默认激活记录状态，可以使用错误日志信息做故障诊断，记录错误信息级别为note warning error；

当数据库无法启动时，且错误日志中没有对应的信息：99.9%是因为数据库库服务配置文件书写错误/数据库服务没有正常初始化

# 修改日志存储路径（只能进行永久配置）
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/data/3306/logs/mysql3306.err  # 该目录得存在，且所有者改为mysql用户

# 模拟故障日志应用
[root@db01 ~]# ll /data/3306/data/ibdata1 
-rw-r----- 1 mysql mysql 12582912 Nov 16 17:46 /data/3306/data/ibdata1
[root@db01 ~]# chmod 000 /data/3306/data/ibdata1
[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL............................... SUCCESS!
Starting MySQL......................................... ERROR!
[root@oldboyxiaoq ~]# tail -20 /data/3306/logs/mysql3306.err
    一堆报错信息
    根据错误日志的错误提示信息，进行错误信息进行分析，从而排查故障可能出现的原因；

二进制日志（log_bin）⭐⭐⭐⭐⭐

日志信息基本配置

log_bin: 此参数为 OFF 表示未启用二进制日志功能
sql_log_bin: 此参数为 ON 时表示当前会话执行SQL语句会被记录到二进制日志（需全局 log_bin 已开启）
server_id：# MySQL服务器设置的一个全局唯一编号，如同其身份证，用于在主从复制中唯一标识每个实例，\
             使其能识别并忽略自身产生的事件，从而从根本上防止数据循环复制
log_bin_basename=/data/3306/binlog/binlog # 定义二进制日志的存储路径

# 配置信息简写方式：开启数据库binlog日志记录功能
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=6
log_bin=/data/3306/binlog/mysql-bin  # 该目录得存在，且所有者改为mysql用户(系统可自行拆分)
# log_bin=binlog  # 只是设置日志名称信息，日志会自动保存到数据库服务指定的数据目录中；

# 配置文件修改后需要重启数据库服务，加载配置文件改动的信息：
[root@db01 ~]# /etc/init.d/mysqld restart 
[root@db01 ~]# ll -h /data/3306/binlog/binlog*
-rw-rw----. 1 mysql mysql 245 6月  24 02:19 /data/3306/binlog/binlog.00000N
-rw-rw----. 1 mysql mysql   16 6月  24 02:19 /data/3306/binlog/binlog.index

日志信息信息扩展配置

sync_binlog： 表示刷新日志到磁盘的策略（主从同步过程的双一标准）
mysql> select @@sync_binlog;  -- 1-- 在进行主从同步过程的双一标准的其中一个1的信息配置，主要是控制缓冲区里的binlog日志信息如何刷写到磁盘中；
-- 此参数信息是有三种方式进行配置的：
-- 参数信息配置0：表示由操作系统缓存自己决定，什么时候刷新日志到磁盘中；
-- 参数信息配置1：表示每次事务提交，立即刷新日志到磁盘中；(此方式配置更安全)
-- 参数信息配置N：表示每组事务提交，当满足N次事务提交则触发，确定刷新日志到磁盘中的频次；（可以有效减少IO性能损耗）
-- 参数官方资料链接：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html

binlog_format： 定义binlog日志的格式信息
mysql> select @@binlog_format;
+------------------------+
| @@binlog_format        |
+------------------------+
| ROW                    |
+------------------------+

SBR与RBR记录的优缺点分析：（面试常见问题）

DDL、DCL：无论在那种模式下，binlog中记录的永远都是sql语句
DML：SBR中记录的是sql语句，RBR中记录的是行的底层变化(二进制数据)

特性	statement（SBR：基于语句的复制）	row（RBR：基于行的复制）
记录内容	原始的 SQL 语句	行的底层变化（二进制数据）
核心优点	可读性强：binlog是SQL语句，便于人工阅读和审计日志量小：批量操作仅记录一条语句	数据一致性极高：复制的是结果，绝对可靠并发性能更好：所需锁粒度更细
核心缺点	数据一致性风险：对非确定性操作主从可能不一致复制可能更慢：从库需重新执行可能很复杂的SQL	可读性差：binlog是二进制行数据，需工具解析日志量大：批量操作会产生巨大日志
安全性	较低	非常高
性能影响	主库上持有锁的时间可能更长	网络带宽和磁盘I/O压力可能更大

world.t1表中的数据（id字段：1，2，3，4）

# 当在statement模式中执行update语句：update world.t1 set id=10 where id<4
日志中记录的内容为：update world.t1 set id=10 where id<4
导出的日志重新执行会进行全表的操作

# 当在ROW模式中执行update语句：update world.t1 set id=10 where id<4
[root@db01 ~]# mysqlbinlog --base64-output=DECODE-ROWS -v /data/3306/binlog/mysql-bin.000011 
    -- base64-output：控制mysqlbinlog是否将二进制事件以Base64编码形式输出或直接解码为可读文本
    -- DECODE-ROWS：强制将ROW模式的二进制事件解读成为SQL语句
    UPDATE `world`.`t1` WHERE @1=1 SET @1=10
    UPDATE `world`.`t1` WHERE @1=2 SET @1=10
    UPDATE `world`.`t1` WHERE @1=3 SET @1=10
 从库重新执行时会直接走索引
 
# 对数据一致性的影响
  当主从库出现了数据不一致时：例如主库world.t1表中有三条id小于4的数据（id=1,id=2,id=3,id=4），
  从库中world.t1表中有两条id小于4的数据（id=2,id=3,id=4）
  直接statement日志的sql语句进行会出现数据不一致问题

日志信息查看方法

可以通过查看方式，获取binlog日志里的数据信息，一般在数据库启动时，日志记录功能就开启了；

可以利用日志中记录信息，将数据库服务的数据信息恢复到指定的时间点，同时也可以支持主从数据复制（在其它机器上回放日志）；

对于binlog日志信息的查看，主要目的是为了日后日志事件信息的截取操作；

确定数据库binlog日志数量

mysql> show binary logs;  -- 查看使用的binlog日志数量
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       552 | No        |
+------------------+-----------+-----------+
mysql> flush logs;  -- 执行flush刷新命令，从而生成新的binlog日志文件(实现日志切割功能）
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       599 | No        |
| mysql-bin.000002 |       156 | No        |
+------------------+-----------+-----------+

确认数据库binlog日志状态

 -- 模拟数据服务有修改操作
mysql> create database test_binlog;  --（binary logs文件大小发生变化）
mysql> select * from world.city limit 1;  --（binary logs文件大小不发生变化）

-- 查看获取当前使用的binlog日志情况，以及产生的日志量字节大小；
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| binlog.000002  |      362 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
  -- position：记录日志事件变化的位置点
  -- Binlog_Do_DB：白名单设置信息（主从时使用）
     指定对哪些数据库记录变更信息事件
  -- Binlog_Ignore_DB：黑名单设置信息（主从时使用）
     指定不对那些数据库记录变更信息事件
  -- Executed_Gtid_Set：设置全局事务编号功能（将每个事务都设置一个编号）
     实现基于事务ID自动化主从复制，无需依赖易错的日志文件名和位置点，从根本上保证了数据一致性

查看数据库binlog日志信息

-- binlog日志信息是以事件方式进行记录的，所以日志查看过程是查看事件信息
-- 一般binlog日志的前两行，表示日志格式头信息（日志简单的描述信息）
-- 一般binlog日志中的query信息，就是对数据库的操作语句，其中包含了创建数据库的语句；

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                     |
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         6 |         125 | Server ver: 8.0.26, Binlog ver: 4        |
| mysql-bin.000002 | 125 | Previous_gtids |         6 |         156 |                                          |
| mysql-bin.000002 | 156 | Anonymous_Gtid |         6 |         233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |
| mysql-bin.000002 | 233 | Query          |         6 |         362 | create database test_binlog /* xid=28 */ |
+------------------+-----+----------------+-----------+-------------+------------------------------------------+

具体binlog事件记录信息分析

列信息	解释说明
Log_name	表示指定查看的binlog日志文件名称信息
Pos	表示binlog日志事件开始的位置点，用于截取二进制日志信息标识
End_log_pos	表示binlog日志事件结束的位置点，用于截取二进制日志信息标识
Info	表示binlog中具体的事件内容信息

-- binlog日志文件156之前的内容是可以忽略的，表示是日志文件的头格式内容信息
-- binlog日志文件已事件形式进行记录，主要关注两个at内容之间的信息，即表示的是一个事件信息；
-- binlog日志中一个事件的开始，就表示上一个事件的结束，在binlog中记录的事件日志信息是连续的；

mysqlbinlog /data/3306/binlog/mysql-bin.000005

筛选数据库binlog日志事件

说明：在实际生产环境中，若binlog日志量比较大时，需要快速过滤关键日志事件行，可以使用以上查看日志方法；

# 模拟生成binlog日志事件信息
  mysql> flush logs;
  mysql> source /root/world-db/world.sql;
  mysql> drop database world;
  mysql> source /root/world-db/world.sql;

# 获取删除数据库的事件信息：
  # 筛选数据库日志方式一：命令行中使用grep
    [root@db01 ~]# mysql -uroot -proot -e "show binlog events in 'mysql-bin.000004';" |grep 'drop database'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    mysql-bin.000004 735434 Query 6 735541 drop database world /* xid=16072 */
    -- 获取指定事件信息产生的起点位置和终点位置信息；

  # 筛选数据库日志方式二：
    mysql> pager less 
    -- 在数据库中定义pager功能（使其拥有筛选能力），数据库连接会话退出即失效；
    mysql> show binlog events in 'mysql-bin.000004'; -- 此时查看日志事件信息具有了翻页功能
    /drop database  -- 进行过滤筛选
    | mysql-bin.000004 |  722789 | Query | 1 | 722896 | drop database world /* xid=5363 */

  # 筛选数据库日志方式三
    mysql> pager grep "drop database"
    PAGER set to 'grep "drop database"'  -- 表示开启数据库pager的过滤功能
    mysql> show binlog events in 'mysql-bin.000004';
    | mysql-bin.000004 | 722789 | Query | 1 | 722896 | drop database world /* xid=5363 */ 
        -- 再次查看binlog事件信息时，只过滤显示删除数据库的操作事件日志：

数据库数据异常恢复

在实际生成环境中，可以利用binlog日志记录的信息截取，实现数据库异常情况下的数据信息恢复功能；

# 数据库异常环境准备
mysql> flush logs;  -- 切换新的binlog日志文件做模拟数据恢复
mysql> create database bindb;
mysql> use bindb;
mysql> create table t1 (id int);
mysql> begin;
mysql> insert into t1 values(1);  -- 事务未提交不会记录到binlog
mysql> commit;
mysql> drop database bindb;  -- 模拟破坏操作，删除数据库

# 截取建库开始到删库之前的所有操作所对应的binlog日志信息
mysql> show binlog events in 'mysql-bin.000005';  -- 查看截取日志信息事件区域范围
[root@oldboyxiaoq ~]# mysqlbinlog --start-position=233 --stop-position=1162 \
                      /data/3306/binlog/mysql-bin.000005 >/tmp/bin.sql
    -- 依据binlog日志的position号码，即可获取到想要恢复数据信息；

# 根据截取的日志信息，进行数据库服务数据恢复
mysql> set sql_log_bin=0;
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；
mysql> source /tmp/bin.sql

# 查看确认数据信息是否恢复
mysql> use bindb;
mysql> show tables;
mysql> select * from t1;

日志信息滚动切割

# 方法一：数据库中操作： flush logs;
# 方法二：命令行操作
    [root@db01 ~]# mysqladmin -uroot -proot flush-logs
    mysqladmin 是 MySQL 官方提供的用于在命令行中执行数据库管理操作（如检查状态、重启服务、刷新日志等）的客户端工具
# 方式三：重启数据库服务
    systemctl restart mysqld
# 方式四：设置binlog日志切割时的存储容量上限
    select @@max_binlog_size;

日志信息清理方法

-- 自动清理
  select @@binlog_expire_logs_seconds;  -- 按照秒进行binlog日志清理（超过则自动清理）
  select @@expire_logs_days;  -- 按照天进行binlog日志清理（超过则自动清理）
-- 手动清理
  mysql> purge binary logs to 'mysql-bin.000007';
    -- 删除指定文件之前的所有binlog文件（不包含这个文件）
  mysql> purge binary logs before '2025-09-16 10:10:10'
    -- 删除指定时间点之前的所有文件（不包含这个时间点的日志）

说明：在对数据库服务日志信息进行清理时，最好使用数据库服务自带的清理工具进行清理，不建议使用rm做日志清理；

rm删除无法更新数据库内部指针，会导致数据库继续向已删除的“幽灵”文件写入数据，引发服务崩溃

日志信息的远程备份（实时备份）

利用专门的存储服务器，将数据库服务中binlog日志进行同步备份

# 创建一个root远程用户
mysql> create user root@'%' identified by 'root';
mysql> grant all privileges on *.* to root@'%';

# 在另外一台日志备份服务器中创建目录
[root@db01 /]# mkdir -p /binlog_backup/
[root@db01 /]# cd /binlog_backup/  在备份服务器的binlog_backup目录下操作
mysqlbinlog -R --host=10.0.0.51 --user=root --password=root \
--port=3306 --raw --stop-never mysql-bin.000008 &

# bug：由于是后台运行，由于会话断开，会关闭mysqlbinlog这个进程，从而不能实现数据的同步
screen方式/nohup方式实现后台永久运行
nohup mysqlbinlog -R --host=10.0.0.51 --user=root --password=root --port=3306 --raw --stop-never mysql-bin.000008 &

远程备份命令参数说明：

参数信息	解释说明
-R --read-from-remote-server	指示mysqlbinlog工具远程mysql服务器上直接读取binlog
-h --host	指定binlog日志文件存储服务器地址
-u --user=name	指定binlog日志服务器连接用户信息
-p --password[=name]	指定binlog日志服务器连接密码信息
--raw	指定binlog日志信息记录二进制信息
--stop-never	指定binlog日志信息将会一直备份记录
日志名	代表从哪个binlog日志开始进行备份

慢日志（slow_log）⭐⭐

慢日志以文本形式记录数据库中执行时间过长的语句

慢日志信息基本配置

mysql> select @@slow_query_log;  -- 表示是否激活慢日志记录功能（默认处于关闭"0"）
mysql> select @@slow_query_log_file;  -- 慢查询日志存储路径
mysql> select @@long_query_time;  -- 指定超过多长时间为慢查询日志
mysql> select @@log_queries_not_using_indexes;  -- 识别因没有走索引而实现的慢查询信息
    -- 就算没有超过long_query_time这个时间，只要没有走索引，还是会被记录的

慢日志信息分析方法

[root@db01 ~]# mysqldumpslow -s -t 3 /data/3306/data/db01-slow.log
-- 按照慢查询语句的重复执行次数(c)进行排序(-s)，取出其中靠前(t)的前三名慢查询语句
-s c：按慢查询出现的总次数排序（count）
-s t：按每条慢查询的总执行使劲按排序（time）
-s at：按慢查询的平均执行时间排序（average query time）
-s l：按慢查询的总锁定时间排序（lock time）
-s al：按慢查询的平均锁定时间排序（average lock time）
-s r：按慢查询发送给客户端的总行数排序（rows sent）
-s ar：按慢查询平均每次发送的行数排序（average rows sent）

数据库数据异常恢复（痛点情况）⭐⭐⭐⭐⭐

数据库误删（建库语句所在日志丢失）⭐⭐⭐⭐⭐

项目背景：一个数据库三年前就创建了，但是日志信息只记录一个月，这个库被误删除了
解决方案：
- A计划：最近一次全备+全备之后，误删除之前所有binlog，进行一同恢复（全备数据+增量数据）
- B计划：利用延时从库，进行数据恢复

所需日志跨越多个文件，如何进行日志信息的截取⭐⭐⭐⭐⭐

解决方案：

A计划：只有position号的方式，可以进行分段截取，进行分段恢复数据；

B计划：根据Datatime时间信息方式，可能会出现准确性不高的情况（因为每一秒可能有多个事件产生）；

C计划：启用GTID（全局事务ID）方式，无论跨越多少个日志文件，每个事务操作的事件ID信息都是唯一且递增的（5.6+引入）；

GTID概念介绍⭐⭐⭐

GTID（global transation id）称为全局事务（事件）ID，标识binlog日志记录的唯一性,保证日志恢复时的一致性，并且具备”幂等性”；

幂等性：确保同一操作首次执行后，后续重复操作自动跳过

GTID信息的表示方式

表现形式	关键列	解释说明
server_uuid:N	server_uuid	表示数据库初始化启动之后，自动生成的随机数信息（唯一的）
	N	表示第几个相关的事务或事件信息，会不断进行自增

server_uuid信息查看

mysql> select @@server_uuid;
  7afe4f8c-5e36-11ed-b083-000c29d44f34  -- 表示数据库每次初始化之后自动生成，不建议手工进行修改；

[root@oldboyxiaoq ~]# cat /data/3306/data/auto.cnf
[auto]
server-uuid=7afe4f8c-5e36-11ed-b083-000c29d44f34
-- 在数据库的数据目录文件中也可以查询到

GTID功能作用：

利用GTID方式管理binlog，实质上就是对于数据库的每个事务产生事件信息打上唯一标识信息（id号）；

利用GTID方式管理binlog，主要目的是处理数据库主从问题，解决主从数据库的数据一致性问题；

标识事务的唯一性，保证日志恢复时的一致性，并且具备”幂等性”；

GTID功能配置

mysql> select @@gtid_mode;  -- 是否开启gtid信息功能
    -- MySQL 5.7+ 引入匿名GTID，是系统为保障复制一致性而自动维护的内部机制
mysql> select @@enforce_gtid_consistency;  -- 设置是否开启GTID强制一致性功能
    -- OFF：表示事务允许违反 GTID 一致性
    -- ON：表示事务不允许违反 GTID 一致性，有相关 SQL 会直接返回异常
    -- WARN：表示事务允许违反 GTID 一致性，但会将警告信息记录到 ERROR LOG
  -- 主要是对创建或删除临时表/创建临时表查看有影响
    create/drop temporary table  -- 创建临时表并查询
    create temporary table ... select 语法
mysql> select @@log_slave_updates;  -- 确认是否让从库也具有记录binlog日志的功能

基于GTID方式对binlog进行管理（利用GTID实现日志截取）

新binlog文件中的GTID编号集合会延续上一个文件，保持连续记录

# 此实验时建立在已经修改了对应GTID的参数
[root@db01 ~]# cat /etc/my.cnf 
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=on

# 刷新新的binlog日志进行操作并准备操作数据
mysql> flush logs;  -- 生成新的binlog日志信息
mysql> create database gtdb;
mysql> use gtdb;
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> begin;
mysql> insert into t1 values(2);
mysql> insert into t1 values(3);
mysql> commit;
mysql> drop database gtdb;  -- 模拟误删除

# 获取需要保留的事务编号信息（19dd4a42-6d59-11f0-9bb7-000c29e3dc70:3-6）
mysql> show binlog events in 'mysql-bin.000012';
  -- 截取开始建库到删库前的所有操作对应的gtid编号

# 数据恢复
[root@db01 ~]# mysqlbinlog --include-gtids='19dd4a42-6d59-11f0-9bb7-000c29e3dc70:3-6' \
/data/3306/binlog/mysql-bin.000012>/tmp/gtid.sql
[root@db01 ~]# ll /tmp/gtid.sql
-rw-r--r-- 1 root root 6481 Sep 13 15:03 /tmp/gtid.sql
mysql> set sql_log_bin=0;  -- 根据日志信息，进行数据库数据恢复
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；恢复后别忘在改为1；
mysql> source /tmp/gtid.sql
ERROR 1049 (42000): Unknown database 'gtdb'
ERROR 1046 (3D000): No database selected
-- 报错原因：因为GTID截取的日志恢复数据时具有幂等性（binlog中已经记录了3-7的GTID事件信息）

# 解决方法
-- skip-gtids：导出binlog时移除其中的GTID信息
[root@db01 ~]# mysqlbinlog --skip-gtids \
--include-gtids='19dd4a42-6d59-11f0-9bb7-000c29e3dc70:3-6' \
/data/3306/binlog/mysql-bin.000012>/tmp/gtid02.sql
-- exclude-gtids：导出binlog时排除指定的GTID集合对应的事务日志
[root@db01 ~]# mysqlbinlog --exclude-gtids='19dd4a42-6d59-11f0-9bb7-000c29e3dc70:3-6' \
--include-gtids='19dd4a42-6d59-11f0-9bb7-000c29e3dc70:3-6' \
/data/3306/data/mysql-bin.000012 >/tmp/gtid03.sql
-- 跨多日志文件信息截取
[root@db01 ~]# mysqlbinlog --skip-gtids \
--include-gtids='19dd4a42-6d59-11f0-9bb7-000c29e3dc70:3-6' \
/data/3306/data/mysql-bin.000011  \
/data/3306/data/mysql-bin.000012 /data/3306/data/mysql-bin.000013 >/tmp/gtid04.sql

从日志文件中恢复单库、单表、或者部分行数据信息⭐⭐⭐⭐⭐

可以利用命令单独截取某个数据库的日志信息；mysqlbinlog -d 数据库日志 >输出文件
可以借助第三方工具实现单表或部分数据恢复；binlog2sql（python） 过滤指定表数据或过滤指定表的部分数据；

单库日志截取：mysqlbinlog -d 数据库名⭐⭐⭐⭐⭐

-- 创建一个test1数据库，并在数据库中创建了一个表，在表中插入了一些数据信息
mysql> create database test1;
mysql> use test1;
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);

-- 创建一个test2数据库，并在数据库中创建了一个表，在表中插入了一些数据信息
mysql> create database test2;
mysql> use test2;
mysql> create table t2 (id int);
mysql> insert into t2 values(1);
mysql> insert into t2 values(2);

-- 通过操作不同的数据库，以及不同的数据表，实现binlog日志事件信息的交叉
mysql> use test1;
mysql> insert into t1 values(3);
mysql> insert into t1 values(4);
mysql> use test2;
mysql> insert into t2 values(3);
mysql> insert into t2 values(4);

数据准备

-- 模拟破坏性操作，删除数据库
mysql> drop database test1;

-- 数据库异常情况恢复操作
mysql> show binlog events in 'mysql-bin.000012';
    -- 查询到创建数据库test1的起始position=1384，执行删除数据库test1的起始position=4419

-- 依据binlog日志的position号码，获取到想要恢复数据信息，并利用-d参数导出指定数据库相关数据；
[root@db01 ~]# mysqlbinlog --skip-gtids --start-position=1384 --stop-position=4419 \
-d test1 /data/3306/binlog/mysql-bin.000012 >/tmp/test1.sql
    -- 如果开启了gtid功能则加入--skip-gtids参数

借助第三方工具(binlog2sql.py)实现单表或部分行数据恢复⭐⭐⭐⭐⭐

可以友好的展示或者管理二进制日志信息（binlog）
进而可以过滤出单独表的信息、表中指定行的信息
可以快速的实现DML操作语句的闪回功能，即实现通过日志信息翻转方式，进行数据信息的恢复
binlog2sql工具是模拟了一个从库，进行日志信息分析，需要保证数据库服务启动状态，且不支持离线方式分析日志内容

# 下载第三方日志分析工具
[root@db01 ~]# pip3 install mysql-replication==0.22

# 部署第三方工具运行环境
[root@xiaoq ~]# yum install -y python3
[root@db01 ~]# pip3 install PyMySQL==0.9.3
[root@db01 ~]# pip3 install mysql-replication==0.22

# 在指定数据库中创建多个数据表
mysql> use test1;
mysql> create table t11 (id int);
mysql> insert into t11 values (1),(2);

# 利用日志信息工具分析查看
[root@db01 ~]# cd /root/binlog2sql/binlog2sql/
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -proot -d test1 -t t1 \
--start-file='mysql-bin.000012'
可以使用--sql-type参数过滤DML类型语句信息，一般常见的过滤是isert update delete

数据库日志信息工具回滚操作（生成指定时间回滚语句-闪回操作）⭐⭐⭐⭐⭐

假设在某个企业的应用场景中，有3000万行数据，占用200G的存储空间，其中误删除了10行数据信息，请问如何进行恢复数据？

python3 binlog2sql.py -uroot -proot -d 数据库名 -t 表名 --sql-type=DML操作类型 --start-file=日志文件名 -B

# 误删除操作语句反转操作
[root@db01 binlog2sql]#  python3 binlog2sql.py -uroot -proot -d test1 -t t1 --sql-type=delete --start-file='mysql-bin.000012'
    DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 4605 end 4770 time 2025-09-13 19:55:34
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -proot -d test1 -t t1 --sql-type=delete --start-file='mysql-bin.000012' -B 
  INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 4605 end 4770 time 2025-09-13 19:55:34

# 误修改操作语句反转操作
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -proot -d test1 -t t1 --sql-type=update --start-file='mysql-bin.000012'
    UPDATE `test1`.`t1` SET `id`=5 WHERE `id`=4 LIMIT 1; #start 4880 end 5060 time 2025-09-13 20:03:02
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -proot -d test1 -t t1 --sql-type=update --start-file='mysql-bin.000012' -B
    UPDATE `test1`.`t1` SET `id`=4 WHERE `id`=5 LIMIT 1; #start 4880 end 5060 time 2025-09-13 20:03:02

# 误插入操作语句反转操作
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -proot -d test1 -t t1 --sql-type=insert --start-file='mysql-bin.000012'
    INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 3321 end 3486 time 2025-09-13 16:22:06
    INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3596 end 3761 time 2025-09-13 16:22:06
[root@db01 binlog2sql]# python3 binlog2sql.py -uroot -proot -d test1 -t t1 --sql-type=insert --start-file='mysql-bin.000012' -B
    DELETE FROM `test1`.`t1` WHERE `id`=4 LIMIT 1; #start 3596 end 3761 time 2025-09-13 16:22:06
    DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 3321 end 3486 time 2025-09-13 16:22:06

posted on 2025-09-11 21:47 猿小姜阅读(22) 评论(0) 收藏举报