一、数据库中存在什么日志1. 错误日志默认存在位置在数据目录中文件名为主机名称.err[rootdb02 data]# ll /data/3306/data/db02.err -rw-r----- 1 mysql mysql 2694183 10月 21 10:51 db02.err设置生成错误日志文件vim /etc/my.cnflog_error/tmp/mysql3306.err -- 永久设置日志作用记录数据库服务运行情况通过错误信息可以分析数据库服务故障原理。2. 通用日志默认不存在需要手动开启。设置生成通用日志文件vim /etc/my.cnfgeneral_logON -- 是否开启通用日志记录功能 general_log_file/data/3306/data/general.log -- 设置日志存储路径和名称验证日志文件生成ll /data/3306/data/general.log -rw-r----- 1 mysql mysql 867 6月 26 18:07 /data/3306/data/general.log日志作用审计日志/开发人员做调式使用的日志。具体包括可以记录登录数据库的用户信息是否登录成功可以做记录可以记录登录成功后用户在数据库中操作行为DDL、DCL、DML、DQL;数据备份mysqldump 做的数据备份流程和原理也体现在日志中。3.慢查询日志作用记录慢查询数据语句信息利用及早发现的慢查询语句做优化可以提高数据库服务性能。设置默认在数据库中不会创建慢查询日志需手动配置开启。慢查询日志相关参数说明参数作用slow_query_log确认是否创建慢查询日志slow_query_log_file确认慢查询日志存储路径和日志名称long_query_time确认语句执行多长时间算慢查询log_queries_not_using_indexes在查询数据信息时没有利用索引的查询# 配置慢查询日志 [rootdb01 ~]# vim /etc/my.cnf slow_query_logon slow_query_log_file/tmp/slow.log long_query_time0.001 log_queries_not_using_indexeson 配置项说明 1.slow_query_logon开启慢查询日志功能对应确认是否创建慢查询日志 2.slow_query_log_file/tmp/slow.log设置慢查询日志的存储路径和日志名称 3.long_query_time0.001设置语句执行多长时间算慢查询此处设置为 0.001 秒 4.log_queries_not_using_indexeson开启记录功能在查询数据信息时没有利用索引的查询也会被记录。4.二进制日志二进制日志的作用可以用于数据恢复历史数据、实时增量数据可以用于主从同步主数据库insert→binlog→ 从数据库insert。配置开启binlog日志1. 先创建binlog存储目录 [rootdb01 ~]# mkdir -p /data/3306/log 2. 给目录授予mysql用户权限 [rootdb01 ~]# chown -R mysql.mysql /data/3306/log 3. 配置文件增加binlog日志 [rootdb01 ~]# vim /etc/my.cnf log_bin/data/3306/log/xiaoQ-binlog mysql show variables like %log_bin%; -------------------------------------------------------------------- | Variable_name | Value | -------------------------------------------------------------------- | log_bin | ON | | log_bin_basename | /data/3306/log/mysql-binlog | | log_bin_index | /data/3306/log/mysql-binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | -------------------------------------------------------------------- 相关参数 参数 作用 log_bin_basename 指定日志存储路径和名称 log_bin_index 指定日志索引信息路径和名称 查询当前 MySQL 实例中与二进制日志Binary Log简称 Binlog相关的系统变量配置。 1. log_bin ON 含义表示当前 MySQL 实例的二进制日志功能是否开启。 说明ON 代表已开启OFF 代表未开启。这是 MySQL 非常重要的一个全局开关开启后数据库所有的数据修改操作如 INSERT、UPDATE、DELETE 等都会按顺序记录到二进制日志文件中常用于主从复制和数据恢复。 2. log_bin_basename /data/3306/log/mysql-binlog 含义二进制日志文件的基础路径和名称前缀。 说明当 MySQL 生成新的二进制日志文件时会以这个值为基础。例如它会在该路径下生成 mysql-binlog.000001、mysql-binlog.000002 等文件。日志文件正是存放在 /data/3306/log/ 目录下。 3. log_bin_index /data/3306/log/mysql-binlog.index 含义二进制日志索引文件的存放路径。 说明这个 .index 文件是一个普通的文本文件。里面记录了当前 MySQL 所有正在使用的二进制日志文件如 000001, 000002 等的完整路径清单MySQL 通过它来追踪有哪些日志文件。 4. log_bin_trust_function_creators OFF 含义是否信任创建存储函数/触发器的用户。 说明当开启了 Binlog 后为了防止主从复制时由于函数执行结果不一致导致主从数据错乱比如用到 NOW() 或 RAND() 函数MySQL 默认OFF会限制普通用户创建存储函数或触发器。只有拥有 SUPER 权限的用户才能创建。如果设为 ON则允许普通用户创建。 5. log_bin_use_v1_row_events OFF 含义是否使用 V1 版本的 Row 格式事件。 说明这是 MySQL 历史遗留的一个版本控制参数。在 MySQL 5.1 版本以后为了支持更丰富的数据类型和功能Row 格式的日志事件升级到了 V2 版本。通常情况下这个值都是 OFF即默认使用 V2 版本不需要修改修改它可能会导致主从版本不兼容。 6. sql_log_bin ON 含义当前会话Session是否将 SQL 操作记录到 Binlog 中。 说明这既是一个全局变量也是一个会话变量。默认是 ON。如果你当前登录的会话不想把某条 SQL 写入 Binlog比如你正在做临时的测试、批量导数据且不想让从库同步你可以执行 SET sql_log_bin 0; 临时关闭当前会话的日志记录执行完操作后再 SET sql_log_bin 1; 打开。查看日志方式一在数据库中查看 binlog 日志信息show binary logs; -- 确认可以查看binlog日志名称 show master status; -- 确认正在记录内容binlog日志名称 show binlog events in 日志名称; -- 查看binlog日志内容 # 日志切割全量备份时先执行一次 mysql flush logs; Query OK, 0 rows affected (0.01 sec) 强制 MySQL 立即关闭当前正在使用的日志文件并生成切换一个新的日志文件。 具体来说这个命令会执行以下三个主要动作 重新生成一个新的二进制日志Binlog 比如你之前在看 mysql-binlog.000001执行完这个命令后MySQL 会停止向 000001 写入并创建一个全新的 mysql-binlog.000002后续的所有数据修改都会记录到新的 000002 文件里。 重新生成一个新的错误日志Error Log如果开启了该功能取决于系统的日志配置。 重新生成一个慢查询日志Slow Query Log如果开启了慢查询日志。 常见使用场景 方便备份与数据恢复 当你准备对数据库进行全量备份时通常会先执行一次 flush logs;。这样可以把备份时刻之前的 Binlog 封存比如 000001备份完成后的新操作记录到新文件000002中。后续如果要用 Binlog 做“基于时间点恢复PITR”边界会非常清晰不会混淆。 主从复制切换 在主从架构中有时候为了让从库重新同步比如重置复制状态或重新指定起点会在主库执行这个命令方便从库定位新的文件位置。 清理磁盘空间 当你的 Binlog 文件占用磁盘过大时如果 000001 不再被使用你可以执行 flush logs; 生成 000002然后用 PURGE BINARY LOGS TO mysql-binlog.000002; 删掉 000001从而释放磁盘空间。 排查问题 当前的日志文件可能非常大了你想通过一个全新的文件来单独观察接下来发生的操作方便排查问题。 # 执行了两次日志切割 mysql show binary logs; ------------------------------------------- | Log_name | File_size | Encrypted | ------------------------------------------- | mysql-binlog.000001 | 873 | No | | mysql-binlog.000002 | 247 | No | | mysql-binlog.000003 | 197 | No | ------------------------------------------- 3 rows in set (0.00 sec) mysql show binlog events in mysql-binlog.000001; ----------------------------------------------------------------------------------------------------- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | ----------------------------------------------------------------------------------------------------- | mysql-binlog.000001 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.36, Binlog ver: 4 | | mysql-binlog.000001 | 126 | Previous_gtids | 1 | 157 | | ----------------------------------------------------------------------------------------------------- 2 rows in set (0.00 sec) 位置点概念 Pos日志信息开始位置点 End_log_pos日志信息结束位置点1. 各列含义解释 Log_name: 当前事件所在的 Binlog 文件名。因为你在查询时指定了 in mysql-binlog.000001所以这里全是一样的。 Pos (Start Position): 该事件在 Binlog 文件中的起始字节位置偏移量。 Event_type: 事件的类型。比如 Query执行的 SQL 语句、Table_map映射表 ID 和表名、Write_rows插入数据、Xid事务提交等。 Server_id: 产生该事件的 MySQL 服务器的唯一 ID。 End_log_pos: 该事件在 Binlog 文件中结束的字节位置。 Info: 具体的事件信息。通常包含了 SQL 语句、GTID 编号或者表 ID 等信息。 2. 重点解释Pos 和 End_log_pos 的关系 这两个字段是理解 Binlog 结构的关键 Pos 是当前事件的起点。 End_log_pos 是当前事件的终点同时也是下一个事件的起点。 3.Binlog 是一个连续的二进制文件所有事件像串珠一样紧挨着写入。你可以观察表格中的数据 第 1 行从 4 开始到 126 结束记录了文件头格式。 第 2 行从 126 开始到 157 结束即上一行的 End_log_pos 等于这一行的 Pos。 以此类推。 当你看到 Xid (COMMIT) 时比如 Pos 为 459End_log_pos 为 490说明这个提交事件占了 31 个字节。紧接着下一个 Gtid 事件就从 490 开始了。 结论End_log_pos - Pos 该事件占用的字节大小。它们首尾相连构成了整个日志文件。 4. 深入解析这份日志记录了什么 从这份日志可以看出你或者某个程序向 testing.student 表中插入Insert了两次数据且开启了 GTID 模式。我们来拆解其中一次完整的事务比如 Pos 157 到 490 这一段 Gtid (Pos 157): 分配了全局事务 ID...f97d83:2表示这是一个新事务。 Query (Pos 236): 记录了 BEGIN表示事务开始。 Table_map (Pos 314): 记录了要操作的表是 table_id: 89 (testing.student)。这里只记录表 ID 和表名不记录具体数据。 Write_rows (Pos 388): 记录了实际的插入数据。注意因为这是 Row 格式的 Binlog具体的列名和数据比如张三、18岁等在底层被转成了二进制这里简略显示为 Write_rows。 Xid (Pos 459): 记录了 COMMIT表示事务提交成功。 结合起来看 在这个表里插入了两次数据形成了两个事务GTID:2 和 GTID:3。 第一个事务记录从 Pos 157 开始到 Pos 490 结束提交。 第二个事务紧挨着从 Pos 490 开始到 Pos 823 结束提交。方式二在命令行中查看 binlog 日志信息# 进入binlog存储路径查看这里使用的是相对路径 [rootdb01 log]# mysqlbinlog mysql-binlog.000003 # The proper term is pseudo_replica_mode, but we use this compatibility alias # to make the statement usable on server versions 8.0.24 and older. /*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/; /*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/; DELIMITER /*!*/; # at 4 #261022 16:17:54 server id 1 end_log_pos 126 CRC32 0x77161edb Start: binlog v 4, server v 8.0.36 created 261022 16:17:54 # Warning: this binlog is either in use or was not closed properly. BINLOG ssbZag8BAAAAegAAAH4AAAABAAQAOC4wLjM2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA CigAAdseFnc /*!*/; # at 126 #261022 16:17:54 server id 1 end_log_pos 197 CRC32 0x185e08c1 Previous-GTIDs # 630b76d3-cc61-11f1-8061-000c29f97d83:2-3 # at 197 #261022 16:26:14 server id 1 end_log_pos 274 CRC32 0xde185fef GTID last_committed0 sequence_number1 rbr_onlyno original_committed_timestamp1792657574462605 immediate_commit_timestamp1792657574462605 transaction_length188 # original_commit_timestamp1792657574462605 (2026-10-22 16:26:14.462605 CST) # immediate_commit_timestamp1792657574462605 (2026-10-22 16:26:14.462605 CST) /*!80001 SET session.original_commit_timestamp1792657574462605*//*!*/; /*!80014 SET session.original_server_version80036*//*!*/; /*!80014 SET session.immediate_server_version80036*//*!*/; SET SESSION.GTID_NEXT 630b76d3-cc61-11f1-8061-000c29f97d83:4/*!*/; # at 274 #261022 16:26:14 server id 1 end_log_pos 385 CRC32 0x0380691e Query thread_id1307 exec_time0 error_code0 Xid 29 SET TIMESTAMP1792657574/*!*/; SET session.pseudo_thread_id1307/*!*/; SET session.foreign_key_checks1, session.sql_auto_is_null0, session.unique_checks1, session.autocommit1/*!*/; SET session.sql_mode1168113696/*!*/; SET session.auto_increment_increment1, session.auto_increment_offset1/*!*/; /*!\C utf8mb4 *//*!*/; SET session.character_set_client255,session.collation_connection255,session.collation_server255/*!*/; SET session.lc_time_names0/*!*/; SET session.collation_databaseDEFAULT/*!*/; /*!80011 SET session.default_collation_for_utf8mb4255*//*!*/; /*!80016 SET session.default_table_encryption0*//*!*/; create database testq /*!*/; # at 385 #261022 16:27:59 server id 1 end_log_pos 462 CRC32 0x6a66a6e3 GTID last_committed1 sequence_number2 rbr_onlyno original_committed_timestamp1792657679159908 immediate_commit_timestamp1792657679159908 transaction_length213 # original_commit_timestamp1792657679159908 (2026-10-22 16:27:59.159908 CST) # immediate_commit_timestamp1792657679159908 (2026-10-22 16:27:59.159908 CST) /*!80001 SET session.original_commit_timestamp1792657679159908*//*!*/; /*!80014 SET session.original_server_version80036*//*!*/; /*!80014 SET session.immediate_server_version80036*//*!*/; SET SESSION.GTID_NEXT 630b76d3-cc61-11f1-8061-000c29f97d83:5/*!*/; # at 462 #261022 16:27:59 server id 1 end_log_pos 598 CRC32 0x661c1e69 Query thread_id1307 exec_time0 error_code0 Xid 31 use testing/*!*/; SET TIMESTAMP1792657679/*!*/; /*!80013 SET session.sql_require_primary_key0*//*!*/; CREATE TABLE testq.q1 (id int,name char(10)) /*!*/; SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/; /*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;1扩展知识DML 语句信息查看方法/记录方法1. 行格式记录binlog_formatROW命令行日志查看DML 语句以行格式记录不能直接查看binlog_formatROW记录行的数据信息设置作用保证数据的一致性。# 解码操作 [rootdb01 log]# mysqlbinlog --base64-outputdecode-rows -vvv mysql-binlog.000003 11 表示在第一列插入数据 12 表示在第二列插入数据 13 表示在第三列插入数据 ### INSERT INTO testq.q3 SET 11 2xx 3xx2. 语句方式记录binlog_formatstatement# 记录语句信息 mysql select binlog_format; ----------------- | binlog_format | ----------------- | STATEMENT | ----------------- 1 row in set, 1 warning (0.00 sec) # 将 MySQL 二进制日志Binlog的记录格式全局修改为 STATEMENT基于 SQL 语句模式。 mysql set global binlog_formatstatement; Query OK, 0 rows affected, 1 warning (0.00 sec) 三种主要格式的对比 你可以把 Binlog 记录数据修改想象成“记录做菜” 1.STATEMENT语句模式你刚设置的 记录方式直接记录菜谱即原封不动的 SQL 语句。 优点日志文件非常小节省磁盘 I/O 和网络带宽主从同步时传的快。 缺点如果 SQL 里有不确定的函数比如 NOW()、RAND()、UUID()主库和从库执行同一句 SQL 得到的时间或结果可能不一样会导致主从数据不一致。 2.ROW行模式MySQL 5.7 之后的默认模式 记录方式记录食材具体变成了什么样即记录每一行数据修改前后的具体内容。 优点绝对安全保证主从数据 100% 一致不会因为函数或环境不同而错乱。 缺点日志文件非常大比如一条 UPDATE 改了 10 万行就会记录 10 万行的变化占用磁盘空间大。 3.MIXED混合模式 记录方式MySQL 自己判断普通的 SQL 用 STATEMENT 记录遇到不安全的函数如 RAND()自动切换成 ROW 记录。DML 语句以语句方式记录可以直接查看内容binlog_formatstatement记录语句信息设置作用方便查看 DML 语句信息但无法保证数据一致性。日志记录方式已经发生改变命令查看日志5.日志切割方式一手动进行日志切割flush logs;方式二自动进行日志切割执行链路 flush logs -- shell脚本 -- crontab 通过设置单个 binlog 文件大小上限达到阈值自动切割 set global max_binlog_size104857600; 104857600 字节 100M4清理日志PS什么 binlog 日志可以清理已经备份的 binlog 日志可以清理binlog 日志中的 DML、DDL、DCL 信息已经保存备份到备份文件此日志可以清理。例如create database、create user、insert等操作产生的日志已经通过mysqldump / xbk备份保存后对应 binlog 即可清理。方式一手动清理 binlog 日志按日志文件清理清理到指定文件之前# 按日志文件清理清理到指定文件之前 purge binary logs to xiaoQ-binlog.000004; # 按时间点清理清理指定时间之前的日志 PURGE BINARY LOGS BEFORE 2026-06-28 19:30:26;方式二自动清理 binlog 日志参数作用binlog_expire_logs_seconds根据历史日志存在的秒数做日志清理expire_logs_days根据历史日志存在的天数做日志清理设置示例1 小时binlog_expire_logs_seconds3600 -- 1小时PS千万不要直接 rm 清理 binlog 日志文件直接rm删除 binlog 日志文件会导致问题关闭数据库服务必须使用上述 purge 或自动过期参数的方式清理。6.备份日志与日志信息截取1备份日志备份服务:10.0.0.52步骤一在备份服务器创建日志备份服务主机/并设置日志存储目录 mkdir /backup/binlog/10.0.0.53/ -p cd /backup/binlog/10.0.0.53/ 步骤二执行 mysqlbinlog 命令实现日志备份,备份53服务上的日志 mysqlbinlog -R --host10.0.0.53 --port3306 --userroot --passwordroot123 --raw --stop-never mysql-binlog.000001 参数说明参数作用-R实现远程备份日志信息功能--raw实时读取日志信息变化将内容同步到远程日志备份服务器上--stop-never日志备份过程采用守护进程方式进行日志备份--raw实时同步示意10.0.0.53 → 10.0.0.52日志切割日志自动实时通步数据库主机 10.0.0.53日志备份主机 10.0.0.52mysql-binlog.000001mysql-binlog.000001insertinsertupdateupdateinsertinsert即主库 binlog 中记录的每条操作insert/update ...都会实时同步到远程日志备份服务器的同名日志文件中。2日志信息截取方式一根据位置点截取信息参数说明--start-position截取日志信息从哪个位置点开始截取--stop-position截取日志信息截取到哪个位置点#在binlog日志存储路径执行 [rootdb01 log]# mysqlbinlog --start-position276 --stop-position476 mysql-binlog.000014 /backup/backup.sql恢复数据方式二根据时间截取信息场景恢复数据库数据库大概在2026-10-10 18:05:00 创建2026-10-10 22:35:00 误操作数据库删除。确定截取时间范围起始时间2026-10-26 16:30:15结束时间2026-10-26 16:31:05范围取在创建之后、误操作之前避开误删除操作。[rootdb01 log]# mysqlbinlog --start-datetime2026-10-26 16:30:00 --stop-datetime2026-10-26 16:32:00 --skip-gtids --base64-outputDECODE-ROWS -v mysql-binlog.000016 /backup/backup01.sql ## mysqlbinlog 参数说明 --start-datetime 只解析指定时间点之后的 binlog 事件如 2026-10-26 16:30:00。需与 MySQL 服务器时区一致。 --stop-datetime 只解析指定时间点之前的 binlog 事件如 2026-10-26 16:32:00。与 start 搭配可精确圈定一个时间窗口。 --skip-gtids 输出结果中不生成 SET GLOBAL.GTID_PURGED 等 GTID 语句防止恢复数据时污染目标实例的 GTID 集合影响主从复制。 --base64-outputDECODE-ROWS 解码 ROW 格式的行变更事件不输出原始 Base64 编码串BINLOG ...。需配合 -v 才能还原为可读内容。 -v--verbose 把 ROW 事件重建为可读的伪 SQL含 WHERE / SET 的列值1、2 为列序号。加 -vv 可额外输出列名等元信息。必须与 --base64-outputDECODE-ROWS 搭配使用。方式三截取指定数据库的数据信息# 切割日志--查看新的binlog日志--创建数据库、表以及插入数据--备份数据--删除数据库--恢复数据 flush logs show master status; CREATE DATABASE IF NOT EXISTS prod_db DEFAULT CHARSET utf8mb4; USE prod_db; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT ); INSERT INTO users (name, age) VALUES (张三, 20), (李四, 25); [rootdb01 log]# mysqlbinlog --databaseprod_db --skip-gtids mysql-binlog.000019 /backup/prod.sql --database 指定只截取某个数据库的数据信息 mysql DROP DATABASE prod_db; mysql source /backup/prod.sql;7.数据恢复1数据库异常数据丢失后修复数据方式方式一借助备份文件binlog恢复数据模拟企业场景步骤一在数据库中存储数据信息 mysql create database abs; Query OK, 1 row affected (0.00 sec) mysql create table abs.t1 (id int,name char(10)); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO abs.t1 (id, name) VALUES (1, 张三); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO abs.t1 (id, name) VALUES (2, 李四); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO abs.t1 (id, name) VALUES (3, 王五); Query OK, 1 row affected (0.00 sec) mysql commit; 步骤二定期对数据库做备份 [rootdb01 backup]# mysqldump -uroot -proot123 -B abs /backup/abs.sql; [rootdb01 log]# mysqldump -uroot -proot123 -B test_db --source-data /backup/test_db.sql; --source-data 这个参数在 MySQL 8.0.26 之前被称为 --master-data。 核心作用它会在导出的 SQL 文件中记录备份开始那一刻的二进制日志binlog文件名和位置Position。 步骤三增加增量数据 CREATE DATABASE IF NOT EXISTS demo_binlog DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE demo_binlog; CREATE TABLE demo_binlog.t1 ( id INT, name CHAR(10) ); INSERT INTO demo_binlog.t1 (id, name) VALUES (1, 张三),(2, 李四),(3, 王五); 步骤四模拟数据库数据损坏 mysql drop database abs; mysql drop database demo_binlog; 步骤五恢复数据 mysql show master status; --查看binlog日志 mysql show binlog events in mysql-binlog.000019; --查看日志内容 # 备份目的是为了查看位置Position因为删除数据的时候未备份demo_binlog数据 [rootdb01 log]# mysqldump -uroot -proot123 -B test_db --source-data /backup/test_db.sql;# 临时关闭blog日志记录避免日志重复记录 mysql set session sql_log_bin0; # 根据日志记录实现增量数据恢复 [rootdb01 log]# mysqlbinlog --start-position3277 --stop-position4098 mysql-binlog.000019 /backup/demo_binlog.sql #恢复数据 mysql source /backup/abs.sql; mysql source /backup/demo_binlog.sql # 打开blog日志记录 mysql set session sql_log_bin1;测试验证数据已恢复2利用数据闪回工具实现数据修复方式二使用binlog2sql数据闪回数据恢复在日常的数据库运维工作中DML数据操作语言误操作是最高频的事故之一。一条手抖执行的DELETE、一次忘记加WHERE条件的UPDATE都可能让生产数据瞬间丢失或错乱。传统手段依赖备份恢复不仅耗时长还会丢失误操作之后的正常数据变更。binlog2sql是一款基于 MySQL binlog 解析的开源工具它能够将二进制日志binlog解析为可读的 SQL 语句并支持反向生成Flashback回滚 SQL从而实现对INSERT、UPDATE、DELETE三类 DML 误操作的精确修复——只回滚误操作不影响其他正常数据。步骤一模拟创建企业环境数据 use abs; select * from student; 步骤二模拟进行DML语句误操作 INSERT INTO student (name, gender, age, grade, major, email, phone) VALUES (林十一, 男, 20, 大二, 计算机科学, linshiyiexample.com, 13800000011); -- 插入误操作 UPDATE student SET gender 男 WHERE age 20; -- 误修改操作 DELETE FROM student WHERE id BETWEEN 6 AND 9; -- 误删除操作 mysql select * from student; ----------------------------------------------------------------------------------------- | id | name | gender | age | grade | major | email | phone | ----------------------------------------------------------------------------------------- | 1 | 张三 | 男 | 20 | 大二 | 计算机科学 | zhangsanexample.com | 13800000001 | | 2 | 李四 | 女 | 19 | 大一 | 软件工程 | lisiexample.com | 13800000002 | | 3 | 王五 | 男 | 21 | 大三 | 人工智能 | wangwuexample.com | 13800000003 | | 4 | 赵六 | 男 | 22 | 大四 | 数据科学 | zhaoliuexample.com | 13800000004 | | 5 | 孙七 | 男 | 20 | 大二 | 网络安全 | sunqiexample.com | 13800000005 | | 10 | 陈二 | 男 | 22 | 大四 | 数据科学 | chenerexample.com | 13800000010 | | 11 | 林十一 | 男 | 20 | 大二 | 计算机科学 | linshiyiexample.com | 13800000011 | ----------------------------------------------------------------------------------------- 步骤三利用数据闪回工具实现误操作数据修复(DML) yum install -y python3 python3-pip pip3 install -r requirements.txt unzip binlog2sql-master.zip -d /opt/module/ [rootdb01 module]# mv requirements.txt binlog2sql-master/ cd binlog2sql-master -- 安装数据闪回工具 # 执行以下sql ALTER USER rootdb01 IDENTIFIED WITH mysql_native_password BY root123; FLUSH PRIVILEGES; # 解析所有 DML [rootdb01 binlog2sql-master]# python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-filemysql-binlog.000019 # 只解析 INSERT 语句 python3 binlog2sql.py -h10.0.0.51 -P3306 -uroot -p123456 -d school -t student --start-filexiaoQ-binlog.000015 --sql-typeinsert # 只解析 INSERT 生成回滚 SQL-B python3 binlog2sql.py -h10.0.0.51 -P3306 -uroot -p123456 -d school -t student --start-filexiaoQ-binlog.000015 --sql-typeinsert -B -- 应用工具实现数据闪回 -- 将误插入数据信息做删除 DELETE FROM abs.student WHERE id11 AND name林十一 AND gender男 AND age20 AND grade大二 AND major计算机科学 AND emaillinshiyiexample.com AND phone13800000011 LIMIT 1;# 查看这个 binlog 里 student 表上所有 UPDATE 的原始语句 python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-filemysql-binlog.000019 --sql-typeupdate # 把 UPDATE 操作反转生成回滚 SQL python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-filemysql-binlog.000019 --sql-typeupdate -B -- 将误修改数据信息做恢复 UPDATE abs.student SET id4, name赵六, gender男, age22, grade大四, major数据科学, emailzhaoliuexample.com, phone13800000004 WHERE id4 AND name赵六 AND gender女 AND age22 AND grade大四 AND major数据科学 AND emailzhaoliuexample.com AND phone13800000004 LIMIT 1; UPDATE abs.student SET id8, name郑十, gender男, age21, grade大三, major计算机科学, emailzhengshiexample.com, phone13800000008 WHERE id8 AND name郑十 AND gender女 AND age21 AND grade大三 AND major计算机科学 AND emailzhengshiexample.com AND phone13800000008 LIMIT 1; UPDATE abs.student SET id10, name陈二, gender男, age22, grade大四, major数据科学, emailchenerexample.com, phone13800000010 WHERE id10 AND name陈二 AND gender女 AND age22 AND grade大四 AND major数据科学 AND emailchenerexample.com AND phone13800000010 LIMIT 1; UPDATE abs.student SET id10, name陈二, gender女, age22, grade大四, major数据科学, emailchenerexample.com, phone13800000010 WHERE id10 AND name陈二 AND gender男 AND age22 AND grade大四 AND major数据科学 AND emailchenerexample.com AND phone13800000010 LIMIT 1; UPDATE abs.student SET id8, name郑十, gender女, age21, grade大三, major计算机科学, emailzhengshiexample.com, phone13800000008 WHERE id8 AND name郑十 AND gender男 AND age21 AND grade大三 AND major计算机科学 AND emailzhengshiexample.com AND phone13800000008 LIMIT 1; UPDATE abs.student SET id4, name赵六, gender女, age22, grade大四, major数据科学, emailzhaoliuexample.com, phone13800000004 WHERE id4 AND name赵六 AND gender男 AND age22 AND grade大四 AND major数据科学 AND emailzhaoliuexample.com AND phone13800000004 LIMIT 1;# 查看这个 binlog 里 student 表上所有 DELETE 的原始记录。 python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-filemysql-binlog.000019 --sql-typedelete # 把 DELETE 操作反转成 INSERT生成回滚 SQL——用来把删掉的数据插回去。 python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-filemysql-binlog.000019 --sql-typedelete -B -- 将误删除数据信息做恢复 INSERT INTO abs.student(id, name, gender, age, grade, major, email, phone) VALUES (9, 刘一, 男, 20, 大二, 人工智能, liuyiexample.com, 13800000009); INSERT INTO abs.student(id, name, gender, age, grade, major, email, phone) VALUES (8, 郑十, 男, 21, 大三, 计算机科学, zhengshiexample.com, 13800000008); INSERT INTO abs.student(id, name, gender, age, grade, major, email, phone) VALUES (7, 吴九, 男, 23, 大四, 软件工程, wujiuexample.com, 13800000007); INSERT INTO abs.student(id, name, gender, age, grade, major, email, phone) VALUES (6, 周八, 女, 19, 大一, 物联网工程, zhoubaexample.com, 13800000006);
一、数据库中存在什么日志1. 错误日志默认存在位置在数据目录中文件名为主机名称.err[rootdb02 data]# ll /data/3306/data/db02.err -rw-r----- 1 mysql mysql 2694183 10月 21 10:51 db02.err设置生成错误日志文件vim /etc/my.cnflog_error/tmp/mysql3306.err -- 永久设置日志作用记录数据库服务运行情况通过错误信息可以分析数据库服务故障原理。2. 通用日志默认不存在需要手动开启。设置生成通用日志文件vim /etc/my.cnfgeneral_logON -- 是否开启通用日志记录功能 general_log_file/data/3306/data/general.log -- 设置日志存储路径和名称验证日志文件生成ll /data/3306/data/general.log -rw-r----- 1 mysql mysql 867 6月 26 18:07 /data/3306/data/general.log日志作用审计日志/开发人员做调式使用的日志。具体包括可以记录登录数据库的用户信息是否登录成功可以做记录可以记录登录成功后用户在数据库中操作行为DDL、DCL、DML、DQL;数据备份mysqldump 做的数据备份流程和原理也体现在日志中。3.慢查询日志作用记录慢查询数据语句信息利用及早发现的慢查询语句做优化可以提高数据库服务性能。设置默认在数据库中不会创建慢查询日志需手动配置开启。慢查询日志相关参数说明参数作用slow_query_log确认是否创建慢查询日志slow_query_log_file确认慢查询日志存储路径和日志名称long_query_time确认语句执行多长时间算慢查询log_queries_not_using_indexes在查询数据信息时没有利用索引的查询# 配置慢查询日志 [rootdb01 ~]# vim /etc/my.cnf slow_query_logon slow_query_log_file/tmp/slow.log long_query_time0.001 log_queries_not_using_indexeson 配置项说明 1.slow_query_logon开启慢查询日志功能对应确认是否创建慢查询日志 2.slow_query_log_file/tmp/slow.log设置慢查询日志的存储路径和日志名称 3.long_query_time0.001设置语句执行多长时间算慢查询此处设置为 0.001 秒 4.log_queries_not_using_indexeson开启记录功能在查询数据信息时没有利用索引的查询也会被记录。4.二进制日志二进制日志的作用可以用于数据恢复历史数据、实时增量数据可以用于主从同步主数据库insert→binlog→ 从数据库insert。配置开启binlog日志1. 先创建binlog存储目录 [rootdb01 ~]# mkdir -p /data/3306/log 2. 给目录授予mysql用户权限 [rootdb01 ~]# chown -R mysql.mysql /data/3306/log 3. 配置文件增加binlog日志 [rootdb01 ~]# vim /etc/my.cnf log_bin/data/3306/log/xiaoQ-binlog mysql show variables like %log_bin%; -------------------------------------------------------------------- | Variable_name | Value | -------------------------------------------------------------------- | log_bin | ON | | log_bin_basename | /data/3306/log/mysql-binlog | | log_bin_index | /data/3306/log/mysql-binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | -------------------------------------------------------------------- 相关参数 参数 作用 log_bin_basename 指定日志存储路径和名称 log_bin_index 指定日志索引信息路径和名称 查询当前 MySQL 实例中与二进制日志Binary Log简称 Binlog相关的系统变量配置。 1. log_bin ON 含义表示当前 MySQL 实例的二进制日志功能是否开启。 说明ON 代表已开启OFF 代表未开启。这是 MySQL 非常重要的一个全局开关开启后数据库所有的数据修改操作如 INSERT、UPDATE、DELETE 等都会按顺序记录到二进制日志文件中常用于主从复制和数据恢复。 2. log_bin_basename /data/3306/log/mysql-binlog 含义二进制日志文件的基础路径和名称前缀。 说明当 MySQL 生成新的二进制日志文件时会以这个值为基础。例如它会在该路径下生成 mysql-binlog.000001、mysql-binlog.000002 等文件。日志文件正是存放在 /data/3306/log/ 目录下。 3. log_bin_index /data/3306/log/mysql-binlog.index 含义二进制日志索引文件的存放路径。 说明这个 .index 文件是一个普通的文本文件。里面记录了当前 MySQL 所有正在使用的二进制日志文件如 000001, 000002 等的完整路径清单MySQL 通过它来追踪有哪些日志文件。 4. log_bin_trust_function_creators OFF 含义是否信任创建存储函数/触发器的用户。 说明当开启了 Binlog 后为了防止主从复制时由于函数执行结果不一致导致主从数据错乱比如用到 NOW() 或 RAND() 函数MySQL 默认OFF会限制普通用户创建存储函数或触发器。只有拥有 SUPER 权限的用户才能创建。如果设为 ON则允许普通用户创建。 5. log_bin_use_v1_row_events OFF 含义是否使用 V1 版本的 Row 格式事件。 说明这是 MySQL 历史遗留的一个版本控制参数。在 MySQL 5.1 版本以后为了支持更丰富的数据类型和功能Row 格式的日志事件升级到了 V2 版本。通常情况下这个值都是 OFF即默认使用 V2 版本不需要修改修改它可能会导致主从版本不兼容。 6. sql_log_bin ON 含义当前会话Session是否将 SQL 操作记录到 Binlog 中。 说明这既是一个全局变量也是一个会话变量。默认是 ON。如果你当前登录的会话不想把某条 SQL 写入 Binlog比如你正在做临时的测试、批量导数据且不想让从库同步你可以执行 SET sql_log_bin 0; 临时关闭当前会话的日志记录执行完操作后再 SET sql_log_bin 1; 打开。查看日志方式一在数据库中查看 binlog 日志信息show binary logs; -- 确认可以查看binlog日志名称 show master status; -- 确认正在记录内容binlog日志名称 show binlog events in 日志名称; -- 查看binlog日志内容 # 日志切割全量备份时先执行一次 mysql flush logs; Query OK, 0 rows affected (0.01 sec) 强制 MySQL 立即关闭当前正在使用的日志文件并生成切换一个新的日志文件。 具体来说这个命令会执行以下三个主要动作 重新生成一个新的二进制日志Binlog 比如你之前在看 mysql-binlog.000001执行完这个命令后MySQL 会停止向 000001 写入并创建一个全新的 mysql-binlog.000002后续的所有数据修改都会记录到新的 000002 文件里。 重新生成一个新的错误日志Error Log如果开启了该功能取决于系统的日志配置。 重新生成一个慢查询日志Slow Query Log如果开启了慢查询日志。 常见使用场景 方便备份与数据恢复 当你准备对数据库进行全量备份时通常会先执行一次 flush logs;。这样可以把备份时刻之前的 Binlog 封存比如 000001备份完成后的新操作记录到新文件000002中。后续如果要用 Binlog 做“基于时间点恢复PITR”边界会非常清晰不会混淆。 主从复制切换 在主从架构中有时候为了让从库重新同步比如重置复制状态或重新指定起点会在主库执行这个命令方便从库定位新的文件位置。 清理磁盘空间 当你的 Binlog 文件占用磁盘过大时如果 000001 不再被使用你可以执行 flush logs; 生成 000002然后用 PURGE BINARY LOGS TO mysql-binlog.000002; 删掉 000001从而释放磁盘空间。 排查问题 当前的日志文件可能非常大了你想通过一个全新的文件来单独观察接下来发生的操作方便排查问题。 # 执行了两次日志切割 mysql show binary logs; ------------------------------------------- | Log_name | File_size | Encrypted | ------------------------------------------- | mysql-binlog.000001 | 873 | No | | mysql-binlog.000002 | 247 | No | | mysql-binlog.000003 | 197 | No | ------------------------------------------- 3 rows in set (0.00 sec) mysql show binlog events in mysql-binlog.000001; ----------------------------------------------------------------------------------------------------- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | ----------------------------------------------------------------------------------------------------- | mysql-binlog.000001 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.36, Binlog ver: 4 | | mysql-binlog.000001 | 126 | Previous_gtids | 1 | 157 | | ----------------------------------------------------------------------------------------------------- 2 rows in set (0.00 sec) 位置点概念 Pos日志信息开始位置点 End_log_pos日志信息结束位置点1. 各列含义解释 Log_name: 当前事件所在的 Binlog 文件名。因为你在查询时指定了 in mysql-binlog.000001所以这里全是一样的。 Pos (Start Position): 该事件在 Binlog 文件中的起始字节位置偏移量。 Event_type: 事件的类型。比如 Query执行的 SQL 语句、Table_map映射表 ID 和表名、Write_rows插入数据、Xid事务提交等。 Server_id: 产生该事件的 MySQL 服务器的唯一 ID。 End_log_pos: 该事件在 Binlog 文件中结束的字节位置。 Info: 具体的事件信息。通常包含了 SQL 语句、GTID 编号或者表 ID 等信息。 2. 重点解释Pos 和 End_log_pos 的关系 这两个字段是理解 Binlog 结构的关键 Pos 是当前事件的起点。 End_log_pos 是当前事件的终点同时也是下一个事件的起点。 3.Binlog 是一个连续的二进制文件所有事件像串珠一样紧挨着写入。你可以观察表格中的数据 第 1 行从 4 开始到 126 结束记录了文件头格式。 第 2 行从 126 开始到 157 结束即上一行的 End_log_pos 等于这一行的 Pos。 以此类推。 当你看到 Xid (COMMIT) 时比如 Pos 为 459End_log_pos 为 490说明这个提交事件占了 31 个字节。紧接着下一个 Gtid 事件就从 490 开始了。 结论End_log_pos - Pos 该事件占用的字节大小。它们首尾相连构成了整个日志文件。 4. 深入解析这份日志记录了什么 从这份日志可以看出你或者某个程序向 testing.student 表中插入Insert了两次数据且开启了 GTID 模式。我们来拆解其中一次完整的事务比如 Pos 157 到 490 这一段 Gtid (Pos 157): 分配了全局事务 ID...f97d83:2表示这是一个新事务。 Query (Pos 236): 记录了 BEGIN表示事务开始。 Table_map (Pos 314): 记录了要操作的表是 table_id: 89 (testing.student)。这里只记录表 ID 和表名不记录具体数据。 Write_rows (Pos 388): 记录了实际的插入数据。注意因为这是 Row 格式的 Binlog具体的列名和数据比如张三、18岁等在底层被转成了二进制这里简略显示为 Write_rows。 Xid (Pos 459): 记录了 COMMIT表示事务提交成功。 结合起来看 在这个表里插入了两次数据形成了两个事务GTID:2 和 GTID:3。 第一个事务记录从 Pos 157 开始到 Pos 490 结束提交。 第二个事务紧挨着从 Pos 490 开始到 Pos 823 结束提交。方式二在命令行中查看 binlog 日志信息# 进入binlog存储路径查看这里使用的是相对路径 [rootdb01 log]# mysqlbinlog mysql-binlog.000003 # The proper term is pseudo_replica_mode, but we use this compatibility alias # to make the statement usable on server versions 8.0.24 and older. /*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/; /*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/; DELIMITER /*!*/; # at 4 #261022 16:17:54 server id 1 end_log_pos 126 CRC32 0x77161edb Start: binlog v 4, server v 8.0.36 created 261022 16:17:54 # Warning: this binlog is either in use or was not closed properly. BINLOG ssbZag8BAAAAegAAAH4AAAABAAQAOC4wLjM2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA CigAAdseFnc /*!*/; # at 126 #261022 16:17:54 server id 1 end_log_pos 197 CRC32 0x185e08c1 Previous-GTIDs # 630b76d3-cc61-11f1-8061-000c29f97d83:2-3 # at 197 #261022 16:26:14 server id 1 end_log_pos 274 CRC32 0xde185fef GTID last_committed0 sequence_number1 rbr_onlyno original_committed_timestamp1792657574462605 immediate_commit_timestamp1792657574462605 transaction_length188 # original_commit_timestamp1792657574462605 (2026-10-22 16:26:14.462605 CST) # immediate_commit_timestamp1792657574462605 (2026-10-22 16:26:14.462605 CST) /*!80001 SET session.original_commit_timestamp1792657574462605*//*!*/; /*!80014 SET session.original_server_version80036*//*!*/; /*!80014 SET session.immediate_server_version80036*//*!*/; SET SESSION.GTID_NEXT 630b76d3-cc61-11f1-8061-000c29f97d83:4/*!*/; # at 274 #261022 16:26:14 server id 1 end_log_pos 385 CRC32 0x0380691e Query thread_id1307 exec_time0 error_code0 Xid 29 SET TIMESTAMP1792657574/*!*/; SET session.pseudo_thread_id1307/*!*/; SET session.foreign_key_checks1, session.sql_auto_is_null0, session.unique_checks1, session.autocommit1/*!*/; SET session.sql_mode1168113696/*!*/; SET session.auto_increment_increment1, session.auto_increment_offset1/*!*/; /*!\C utf8mb4 *//*!*/; SET session.character_set_client255,session.collation_connection255,session.collation_server255/*!*/; SET session.lc_time_names0/*!*/; SET session.collation_databaseDEFAULT/*!*/; /*!80011 SET session.default_collation_for_utf8mb4255*//*!*/; /*!80016 SET session.default_table_encryption0*//*!*/; create database testq /*!*/; # at 385 #261022 16:27:59 server id 1 end_log_pos 462 CRC32 0x6a66a6e3 GTID last_committed1 sequence_number2 rbr_onlyno original_committed_timestamp1792657679159908 immediate_commit_timestamp1792657679159908 transaction_length213 # original_commit_timestamp1792657679159908 (2026-10-22 16:27:59.159908 CST) # immediate_commit_timestamp1792657679159908 (2026-10-22 16:27:59.159908 CST) /*!80001 SET session.original_commit_timestamp1792657679159908*//*!*/; /*!80014 SET session.original_server_version80036*//*!*/; /*!80014 SET session.immediate_server_version80036*//*!*/; SET SESSION.GTID_NEXT 630b76d3-cc61-11f1-8061-000c29f97d83:5/*!*/; # at 462 #261022 16:27:59 server id 1 end_log_pos 598 CRC32 0x661c1e69 Query thread_id1307 exec_time0 error_code0 Xid 31 use testing/*!*/; SET TIMESTAMP1792657679/*!*/; /*!80013 SET session.sql_require_primary_key0*//*!*/; CREATE TABLE testq.q1 (id int,name char(10)) /*!*/; SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/; /*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;1扩展知识DML 语句信息查看方法/记录方法1. 行格式记录binlog_formatROW命令行日志查看DML 语句以行格式记录不能直接查看binlog_formatROW记录行的数据信息设置作用保证数据的一致性。# 解码操作 [rootdb01 log]# mysqlbinlog --base64-outputdecode-rows -vvv mysql-binlog.000003 11 表示在第一列插入数据 12 表示在第二列插入数据 13 表示在第三列插入数据 ### INSERT INTO testq.q3 SET 11 2xx 3xx2. 语句方式记录binlog_formatstatement# 记录语句信息 mysql select binlog_format; ----------------- | binlog_format | ----------------- | STATEMENT | ----------------- 1 row in set, 1 warning (0.00 sec) # 将 MySQL 二进制日志Binlog的记录格式全局修改为 STATEMENT基于 SQL 语句模式。 mysql set global binlog_formatstatement; Query OK, 0 rows affected, 1 warning (0.00 sec) 三种主要格式的对比 你可以把 Binlog 记录数据修改想象成“记录做菜” 1.STATEMENT语句模式你刚设置的 记录方式直接记录菜谱即原封不动的 SQL 语句。 优点日志文件非常小节省磁盘 I/O 和网络带宽主从同步时传的快。 缺点如果 SQL 里有不确定的函数比如 NOW()、RAND()、UUID()主库和从库执行同一句 SQL 得到的时间或结果可能不一样会导致主从数据不一致。 2.ROW行模式MySQL 5.7 之后的默认模式 记录方式记录食材具体变成了什么样即记录每一行数据修改前后的具体内容。 优点绝对安全保证主从数据 100% 一致不会因为函数或环境不同而错乱。 缺点日志文件非常大比如一条 UPDATE 改了 10 万行就会记录 10 万行的变化占用磁盘空间大。 3.MIXED混合模式 记录方式MySQL 自己判断普通的 SQL 用 STATEMENT 记录遇到不安全的函数如 RAND()自动切换成 ROW 记录。DML 语句以语句方式记录可以直接查看内容binlog_formatstatement记录语句信息设置作用方便查看 DML 语句信息但无法保证数据一致性。日志记录方式已经发生改变命令查看日志5.日志切割方式一手动进行日志切割flush logs;方式二自动进行日志切割执行链路 flush logs -- shell脚本 -- crontab 通过设置单个 binlog 文件大小上限达到阈值自动切割 set global max_binlog_size104857600; 104857600 字节 100M4清理日志PS什么 binlog 日志可以清理已经备份的 binlog 日志可以清理binlog 日志中的 DML、DDL、DCL 信息已经保存备份到备份文件此日志可以清理。例如create database、create user、insert等操作产生的日志已经通过mysqldump / xbk备份保存后对应 binlog 即可清理。方式一手动清理 binlog 日志按日志文件清理清理到指定文件之前# 按日志文件清理清理到指定文件之前 purge binary logs to xiaoQ-binlog.000004; # 按时间点清理清理指定时间之前的日志 PURGE BINARY LOGS BEFORE 2026-06-28 19:30:26;方式二自动清理 binlog 日志参数作用binlog_expire_logs_seconds根据历史日志存在的秒数做日志清理expire_logs_days根据历史日志存在的天数做日志清理设置示例1 小时binlog_expire_logs_seconds3600 -- 1小时PS千万不要直接 rm 清理 binlog 日志文件直接rm删除 binlog 日志文件会导致问题关闭数据库服务必须使用上述 purge 或自动过期参数的方式清理。6.备份日志与日志信息截取1备份日志备份服务:10.0.0.52步骤一在备份服务器创建日志备份服务主机/并设置日志存储目录 mkdir /backup/binlog/10.0.0.53/ -p cd /backup/binlog/10.0.0.53/ 步骤二执行 mysqlbinlog 命令实现日志备份,备份53服务上的日志 mysqlbinlog -R --host10.0.0.53 --port3306 --userroot --passwordroot123 --raw --stop-never mysql-binlog.000001 参数说明参数作用-R实现远程备份日志信息功能--raw实时读取日志信息变化将内容同步到远程日志备份服务器上--stop-never日志备份过程采用守护进程方式进行日志备份--raw实时同步示意10.0.0.53 → 10.0.0.52日志切割日志自动实时通步数据库主机 10.0.0.53日志备份主机 10.0.0.52mysql-binlog.000001mysql-binlog.000001insertinsertupdateupdateinsertinsert即主库 binlog 中记录的每条操作insert/update ...都会实时同步到远程日志备份服务器的同名日志文件中。2日志信息截取方式一根据位置点截取信息参数说明--start-position截取日志信息从哪个位置点开始截取--stop-position截取日志信息截取到哪个位置点#在binlog日志存储路径执行 [rootdb01 log]# mysqlbinlog --start-position276 --stop-position476 mysql-binlog.000014 /backup/backup.sql恢复数据方式二根据时间截取信息场景恢复数据库数据库大概在2026-10-10 18:05:00 创建2026-10-10 22:35:00 误操作数据库删除。确定截取时间范围起始时间2026-10-26 16:30:15结束时间2026-10-26 16:31:05范围取在创建之后、误操作之前避开误删除操作。[rootdb01 log]# mysqlbinlog --start-datetime2026-10-26 16:30:00 --stop-datetime2026-10-26 16:32:00 --skip-gtids --base64-outputDECODE-ROWS -v mysql-binlog.000016 /backup/backup01.sql ## mysqlbinlog 参数说明 --start-datetime 只解析指定时间点之后的 binlog 事件如 2026-10-26 16:30:00。需与 MySQL 服务器时区一致。 --stop-datetime 只解析指定时间点之前的 binlog 事件如 2026-10-26 16:32:00。与 start 搭配可精确圈定一个时间窗口。 --skip-gtids 输出结果中不生成 SET GLOBAL.GTID_PURGED 等 GTID 语句防止恢复数据时污染目标实例的 GTID 集合影响主从复制。 --base64-outputDECODE-ROWS 解码 ROW 格式的行变更事件不输出原始 Base64 编码串BINLOG ...。需配合 -v 才能还原为可读内容。 -v--verbose 把 ROW 事件重建为可读的伪 SQL含 WHERE / SET 的列值1、2 为列序号。加 -vv 可额外输出列名等元信息。必须与 --base64-outputDECODE-ROWS 搭配使用。方式三截取指定数据库的数据信息# 切割日志--查看新的binlog日志--创建数据库、表以及插入数据--备份数据--删除数据库--恢复数据 flush logs show master status; CREATE DATABASE IF NOT EXISTS prod_db DEFAULT CHARSET utf8mb4; USE prod_db; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT ); INSERT INTO users (name, age) VALUES (张三, 20), (李四, 25); [rootdb01 log]# mysqlbinlog --databaseprod_db --skip-gtids mysql-binlog.000019 /backup/prod.sql --database 指定只截取某个数据库的数据信息 mysql DROP DATABASE prod_db; mysql source /backup/prod.sql;7.数据恢复1数据库异常数据丢失后修复数据方式方式一借助备份文件binlog恢复数据模拟企业场景步骤一在数据库中存储数据信息 mysql create database abs; Query OK, 1 row affected (0.00 sec) mysql create table abs.t1 (id int,name char(10)); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO abs.t1 (id, name) VALUES (1, 张三); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO abs.t1 (id, name) VALUES (2, 李四); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO abs.t1 (id, name) VALUES (3, 王五); Query OK, 1 row affected (0.00 sec) mysql commit; 步骤二定期对数据库做备份 [rootdb01 backup]# mysqldump -uroot -proot123 -B abs /backup/abs.sql; [rootdb01 log]# mysqldump -uroot -proot123 -B test_db --source-data /backup/test_db.sql; --source-data 这个参数在 MySQL 8.0.26 之前被称为 --master-data。 核心作用它会在导出的 SQL 文件中记录备份开始那一刻的二进制日志binlog文件名和位置Position。 步骤三增加增量数据 CREATE DATABASE IF NOT EXISTS demo_binlog DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE demo_binlog; CREATE TABLE demo_binlog.t1 ( id INT, name CHAR(10) ); INSERT INTO demo_binlog.t1 (id, name) VALUES (1, 张三),(2, 李四),(3, 王五); 步骤四模拟数据库数据损坏 mysql drop database abs; mysql drop database demo_binlog; 步骤五恢复数据 mysql show master status; --查看binlog日志 mysql show binlog events in mysql-binlog.000019; --查看日志内容 # 备份目的是为了查看位置Position因为删除数据的时候未备份demo_binlog数据 [rootdb01 log]# mysqldump -uroot -proot123 -B test_db --source-data /backup/test_db.sql;# 临时关闭blog日志记录避免日志重复记录 mysql set session sql_log_bin0; # 根据日志记录实现增量数据恢复 [rootdb01 log]# mysqlbinlog --start-position3277 --stop-position4098 mysql-binlog.000019 /backup/demo_binlog.sql #恢复数据 mysql source /backup/abs.sql; mysql source /backup/demo_binlog.sql # 打开blog日志记录 mysql set session sql_log_bin1;测试验证数据已恢复2利用数据闪回工具实现数据修复方式二使用binlog2sql数据闪回数据恢复在日常的数据库运维工作中DML数据操作语言误操作是最高频的事故之一。一条手抖执行的DELETE、一次忘记加WHERE条件的UPDATE都可能让生产数据瞬间丢失或错乱。传统手段依赖备份恢复不仅耗时长还会丢失误操作之后的正常数据变更。binlog2sql是一款基于 MySQL binlog 解析的开源工具它能够将二进制日志binlog解析为可读的 SQL 语句并支持反向生成Flashback回滚 SQL从而实现对INSERT、UPDATE、DELETE三类 DML 误操作的精确修复——只回滚误操作不影响其他正常数据。步骤一模拟创建企业环境数据 use abs; select * from student; 步骤二模拟进行DML语句误操作 INSERT INTO student (name, gender, age, grade, major, email, phone) VALUES (林十一, 男, 20, 大二, 计算机科学, linshiyiexample.com, 13800000011); -- 插入误操作 UPDATE student SET gender 男 WHERE age 20; -- 误修改操作 DELETE FROM student WHERE id BETWEEN 6 AND 9; -- 误删除操作 mysql select * from student; ----------------------------------------------------------------------------------------- | id | name | gender | age | grade | major | email | phone | ----------------------------------------------------------------------------------------- | 1 | 张三 | 男 | 20 | 大二 | 计算机科学 | zhangsanexample.com | 13800000001 | | 2 | 李四 | 女 | 19 | 大一 | 软件工程 | lisiexample.com | 13800000002 | | 3 | 王五 | 男 | 21 | 大三 | 人工智能 | wangwuexample.com | 13800000003 | | 4 | 赵六 | 男 | 22 | 大四 | 数据科学 | zhaoliuexample.com | 13800000004 | | 5 | 孙七 | 男 | 20 | 大二 | 网络安全 | sunqiexample.com | 13800000005 | | 10 | 陈二 | 男 | 22 | 大四 | 数据科学 | chenerexample.com | 13800000010 | | 11 | 林十一 | 男 | 20 | 大二 | 计算机科学 | linshiyiexample.com | 13800000011 | ----------------------------------------------------------------------------------------- 步骤三利用数据闪回工具实现误操作数据修复(DML) yum install -y python3 python3-pip pip3 install -r requirements.txt unzip binlog2sql-master.zip -d /opt/module/ [rootdb01 module]# mv requirements.txt binlog2sql-master/ cd binlog2sql-master -- 安装数据闪回工具 # 执行以下sql ALTER USER rootdb01 IDENTIFIED WITH mysql_native_password BY root123; FLUSH PRIVILEGES; # 解析所有 DML [rootdb01 binlog2sql-master]# python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-filemysql-binlog.000019 # 只解析 INSERT 语句 python3 binlog2sql.py -h10.0.0.51 -P3306 -uroot -p123456 -d school -t student --start-filexiaoQ-binlog.000015 --sql-typeinsert # 只解析 INSERT 生成回滚 SQL-B python3 binlog2sql.py -h10.0.0.51 -P3306 -uroot -p123456 -d school -t student --start-filexiaoQ-binlog.000015 --sql-typeinsert -B -- 应用工具实现数据闪回 -- 将误插入数据信息做删除 DELETE FROM abs.student WHERE id11 AND name林十一 AND gender男 AND age20 AND grade大二 AND major计算机科学 AND emaillinshiyiexample.com AND phone13800000011 LIMIT 1;# 查看这个 binlog 里 student 表上所有 UPDATE 的原始语句 python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-filemysql-binlog.000019 --sql-typeupdate # 把 UPDATE 操作反转生成回滚 SQL python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-filemysql-binlog.000019 --sql-typeupdate -B -- 将误修改数据信息做恢复 UPDATE abs.student SET id4, name赵六, gender男, age22, grade大四, major数据科学, emailzhaoliuexample.com, phone13800000004 WHERE id4 AND name赵六 AND gender女 AND age22 AND grade大四 AND major数据科学 AND emailzhaoliuexample.com AND phone13800000004 LIMIT 1; UPDATE abs.student SET id8, name郑十, gender男, age21, grade大三, major计算机科学, emailzhengshiexample.com, phone13800000008 WHERE id8 AND name郑十 AND gender女 AND age21 AND grade大三 AND major计算机科学 AND emailzhengshiexample.com AND phone13800000008 LIMIT 1; UPDATE abs.student SET id10, name陈二, gender男, age22, grade大四, major数据科学, emailchenerexample.com, phone13800000010 WHERE id10 AND name陈二 AND gender女 AND age22 AND grade大四 AND major数据科学 AND emailchenerexample.com AND phone13800000010 LIMIT 1; UPDATE abs.student SET id10, name陈二, gender女, age22, grade大四, major数据科学, emailchenerexample.com, phone13800000010 WHERE id10 AND name陈二 AND gender男 AND age22 AND grade大四 AND major数据科学 AND emailchenerexample.com AND phone13800000010 LIMIT 1; UPDATE abs.student SET id8, name郑十, gender女, age21, grade大三, major计算机科学, emailzhengshiexample.com, phone13800000008 WHERE id8 AND name郑十 AND gender男 AND age21 AND grade大三 AND major计算机科学 AND emailzhengshiexample.com AND phone13800000008 LIMIT 1; UPDATE abs.student SET id4, name赵六, gender女, age22, grade大四, major数据科学, emailzhaoliuexample.com, phone13800000004 WHERE id4 AND name赵六 AND gender男 AND age22 AND grade大四 AND major数据科学 AND emailzhaoliuexample.com AND phone13800000004 LIMIT 1;# 查看这个 binlog 里 student 表上所有 DELETE 的原始记录。 python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-filemysql-binlog.000019 --sql-typedelete # 把 DELETE 操作反转成 INSERT生成回滚 SQL——用来把删掉的数据插回去。 python3 binlog2sql.py -h10.0.0.53 -P3306 -uroot -proot123 -d abs -t student --start-filemysql-binlog.000019 --sql-typedelete -B -- 将误删除数据信息做恢复 INSERT INTO abs.student(id, name, gender, age, grade, major, email, phone) VALUES (9, 刘一, 男, 20, 大二, 人工智能, liuyiexample.com, 13800000009); INSERT INTO abs.student(id, name, gender, age, grade, major, email, phone) VALUES (8, 郑十, 男, 21, 大三, 计算机科学, zhengshiexample.com, 13800000008); INSERT INTO abs.student(id, name, gender, age, grade, major, email, phone) VALUES (7, 吴九, 男, 23, 大四, 软件工程, wujiuexample.com, 13800000007); INSERT INTO abs.student(id, name, gender, age, grade, major, email, phone) VALUES (6, 周八, 女, 19, 大一, 物联网工程, zhoubaexample.com, 13800000006);
