MySQL二进制日志分析

二进制日志除了同步外，还有哪些用途

除了用于数据同步和复制，二进制日志还有以下几个用途：

恢复数据：二进制日志可以用于恢复数据，尤其是在意外数据损坏、误操作或系统故障等情况下。通过重新执行二进制日志中的SQL语句，可以将数据库恢复到特定时间点的状态。
数据备份：二进制日志可以作为数据库备份的一部分。通过备份二进制日志，可以实现增量备份，只备份最新的修改操作，而不需要备份整个数据库。这样可以节省存储空间和备份时间。
审计和安全：二进制日志记录了数据库中执行的所有SQL语句，包括数据修改操作和查询语句。这使得二进制日志成为审计数据库活动和安全分析的重要工具。通过分析二进制日志，可以了解谁在何时执行了哪些操作，并跟踪数据库的历史记录。
性能分析：二进制日志可以用于性能分析和故障排除。通过分析二进制日志，可以了解数据库中的查询模式、频率和执行时间。这有助于识别慢查询、瓶颈和性能问题，并优化数据库的性能。

需要注意的是，二进制日志的生成会对数据库的性能产生一定的开销。因此，在考虑使用二进制日志时，需要权衡数据保护和性能之间的平衡，并根据具体需求进行配置和管理。

如何通过二进制日志恢复数据

要通过二进制日志恢复数据，你需要按照以下步骤进行操作：

确保数据库已经停止：在开始恢复数据之前，确保数据库处于停止状态。这是为了避免在恢复过程中出现数据不一致的情况。
备份现有数据：在进行数据恢复之前，最好先备份当前的数据库数据。这样，在恢复过程中出现任何问题时，可以回滚到原始状态。
找到要恢复的起始点：通过查看二进制日志文件，找到要恢复的起始点。可以使用mysqlbinlog工具来解析二进制日志文件，并找到要开始恢复的位置。你可以根据时间戳、位置或特定事件来确定起始点。
恢复数据：使用mysqlbinlog工具将二进制日志应用到数据库中。可以通过以下命令来执行恢复操作：

mysqlbinlog binlog_file | mysql -u username -p

其中，binlog_file是要恢复的二进制日志文件，username是数据库用户名。执行该命令后，mysqlbinlog将解析二进制日志并将其中的SQL语句发送给MySQL服务器进行执行。

检查恢复结果：在数据恢复完成后，可以检查数据库中的数据是否正确恢复。验证恢复后的数据是否与预期一致，确保没有发生数据丢失或数据不一致的情况。

请注意，二进制日志的恢复过程是一个敏感操作，需要谨慎操作。确保在进行恢复之前理解和确认每个步骤，以及备份数据以防止意外情况发生。

最好是在备份时通过 --flush-log 等参数（不同版本，参数不一样）刷新binlog日志，

几个比较重要的参数及说明

--start-position=4 # 通过pos值来选定开始范围
--stop-position=326 # 通过pos值来选定结束范围
--start-datetime="2024-10-10 09:00:00" # 通过时间来选定开始时间范围
--stop-datetime="2024-10-10 16:00:00" # 通过时间来选定结束时间范围
--set-charset=utf8 # 编码格式，尽量跟原库一致，避免某些字符乱码
-v # 可以显示sql具体内容，方便找想要的sql

从binlog中提取出指定范围的记录

binlog转换之后的格式大概如下

# at 154
#200106 14:12:18 server id 1  end_log_pos 326     Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1578322338/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
CREATE TABLE test (id INT) ENGINE=InnoDB/*!*/;

在这个例子中，at 154 表示这个事件开始的位置是 154，end_log_pos 326 表示这个事件结束的位置是 326。

如果只想使用部分binlog内容，不能直接删减通过mysqlbinlog转换后的文件，会导致mysql无法正常识别。可以通过以下方法进行截取。

# 从pos 154到pos 326
mysqlbinlog --start-position=154 --stop-position=326 --set-charset=utf8 -v /path/binlog-file > /path/to/filtered-binlog.sql

# 从pos 154 到文件结尾
mysqlbinlog --start-position=154 --set-charset=utf8 -v /path/binlog-file > /path/to/filtered-binlog.sql

# 从文件开头到pos 326
mysqlbinlog --stop-position=326 --set-charset=utf8 -v  /path/binlog-file > /path/to/filtered-binlog.sql

从binlog中提取出指定库的记录

# at 154
#200106 14:12:18 server id 1  end_log_pos 326     Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1578322338/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
CREATE TABLE test (id INT) ENGINE=InnoDB/*!*/;

# 从pos 154到pos 326
mysqlbinlog --start-position=154 --stop-position=326  --database=dbname --set-charset=utf8 -v /path/binlog-file > /path/to/filtered-binlog.sql

# 导入
mysql -u root -p --default-character-set=utf8 < /path/to/filtered-binlog.sql

有时指定的pos范围可能事务并没有完结，会报如下错误

WARNING: The range of printed events ends with a row event or a table map event that does not have the STMT_END_F flag set. This might be because the last statement was not fully written to the log, or because you are using a --stop-position or --stop-datetime that refers to an event in the middle of a statement. The event(s) from the partial statement have not been written to output.

可以用 --start-datetime和--stop-datetime来指定时间范围，mysqlbinlog会根据事务完成情况在选择的时间内导出完整的sql

 mysqlbinlog mysql-bin.005503 --stop-datetime="2024-10-10 16:00:00" --database=abc --set-charset=utf8 -v  > abc_20241010_0300-1600_2.sql

二进制日志的分析

可以使用mysqlbinlog工具结合其他脚本或程序进行解析和处理。以下是一种基本的方法：

使用mysqlbinlog工具将二进制日志文件转换为可读的文本格式：

mysqlbinlog binlog_file > binlog_file.txt

这将把binlog_file的内容转换为文本格式并保存到binlog_file.txt文件中。

分析文本格式的二进制日志文件：

通过分析文本文件，你可以查看其中的事件类型、SQL语句、事务信息等。你可以手动解析文本文件，或者编写脚本来提取感兴趣的信息。

例如，你可以使用脚本语言（如Python、Perl等）编写脚本，使用正则表达式或其他方法提取SQL语句、事务信息等，并进行进一步的处理和分析。

请注意，二进制日志的格式和事件类型相当复杂，因此解析和分析二进制日志需要一定的经验和理解。你可能需要仔细研究MySQL官方文档中有关二进制日志格式和事件类型的说明，以便正确解析日志文件。

恢复过程中的错误处理

ERROR 1609 (HY000): The BINLOG statement of type Table_map was not preceded by a format description BINLOG statement.

跟下面的'max_allowed_packet'问题同样的解决方案

ERROR 1153 (08S01) at line xx : Got a packet bigger than 'max_allowed_packet' bytes

该问题是因为max_allowed_packet值太小导致的，可以通过临时修改或者修改my.cnf配置来实现。具体修改大小根据实际环境来定，以下为1G为例。

改my.cnf

[mysqldump]
max_allowed_packet=1024M

临时修改，服务重启后失效。执行如下sql

SET GLOBAL max_allowed_packet=1073741824;  -- 设置为 1GB

上一页MySQL事件函数定时任务下一页MySQL回收表空间

最后更新于1年前