面试题：MySQL二进制日志格式与数据恢复及复制的深度探究

存储过程和函数：主库执行存储过程或函数时，Mixed模式下某些情况可能以Statement格式记录日志，而从库执行时因环境差异（如版本、配置）导致结果不同。例如，主从库的SQL_MODE设置不同，存储过程中使用了依赖于SQL_MODE的函数，主库记录的SQL在从库执行结果有别。
不确定函数：如NOW()、RAND()等不确定函数，在主库执行时记录的是函数值，从库执行时函数值可能不同。Mixed模式下若以Statement格式记录包含这些函数的语句，从库重放时会产生差异。
触发器：主库执行触发器相关操作时，Mixed模式下记录日志可能无法准确重现从库的执行顺序或环境，导致从库数据不一致。例如，主库触发器中使用了NEW或OLD关键字引用行数据，从库执行时由于复制延迟等原因，行数据状态可能与主库执行时不同。
主从配置差异：主从库的字符集、数据类型定义等配置不同，Mixed模式下主库记录的日志在从库执行可能因数据类型转换等问题出现不一致。比如主库是UTF8编码，从库是GBK编码，插入含有特殊字符的文本时可能出现乱码或数据截断，导致数据不一致。

检查主从库配置：
- 对比主从库的MySQL版本，确保版本一致或在兼容范围内。可通过SELECT VERSION();查看版本号。
- 检查主从库的SQL_MODE设置，使用SELECT @@sql_mode;查看并确保一致。
- 确认主从库的字符集和校对规则，通过SHOW VARIABLES LIKE 'character_set_%';和SHOW VARIABLES LIKE 'collation_%';查看并保持一致。
查看主从复制状态：
- 在主库上执行SHOW MASTER STATUS;获取二进制日志文件名和位置。
- 在从库上执行SHOW SLAVE STATUS \G;，重点检查Seconds_Behind_Master（复制延迟时间）、Last_Errno（最后错误编号）、Last_Error（最后错误信息）。若Seconds_Behind_Master较大，可能存在复制延迟导致数据不一致；若Last_Errno不为0，根据Last_Error提示排查错误。
分析二进制日志：
- 使用mysqlbinlog工具分析主库二进制日志，查看记录的语句，尤其关注包含不确定函数、存储过程、触发器相关语句。例如，mysqlbinlog --start-datetime="2024-01-01 00:00:00" --stop-datetime="2024-01-02 00:00:00" master-bin.000001 > binlog.txt，然后查看binlog.txt内容。
- 对比主从库执行相同事务前后的数据状态，可通过导出数据（如mysqldump），使用工具（如diff）对比数据差异，定位导致不一致的具体操作。

调整日志格式：
- 考虑将主库日志格式改为Row格式，在主库配置文件（如my.cnf）中设置binlog_format = ROW，重启MySQL服务使配置生效。Row格式记录的是数据行的变化，可避免因SQL语句执行环境差异导致的不一致问题。
处理不确定函数：
- 对于使用了不确定函数（如NOW()、RAND()）的SQL语句，修改为确定的方式。例如，将INSERT INTO table_name (create_time) VALUES (NOW());改为SET @current_time = NOW(); INSERT INTO table_name (create_time) VALUES (@current_time);，这样在主从库执行结果一致。
同步主从环境：
- 确保主从库的MySQL版本、SQL_MODE、字符集、校对规则等配置完全一致。对于因配置差异导致的数据不一致，根据具体情况调整从库配置，重启MySQL服务。
处理存储过程和触发器：
- 对于存储过程和触发器，尽量避免使用依赖于主库特定环境的代码。若不可避免，可在从库创建与主库相同的函数和存储过程，并确保其实现逻辑在主从库一致。对于触发器，仔细检查主从库执行顺序和数据状态，必要时修改触发器逻辑以适应主从复制环境。

星途面试题库