mysql二进制日志

mysql二进制日志

• Mysql二进制日志记录了所有对mysql的修改事件，包括增删改查和对表结构的修改
• Mysql二进制日志记录的都是成功执行的语句，没有成功执行，回滚了的sql和语法错误的sql是不会写入这里的

binlog工具

binlog二进制日志的格式STATEMENT

基于段的格式 binglog_formart = STATEMENT
基于SQL语句的复制（SBR）
这种方式，记录的是执行的语句

优点：

• 日志记录量相对较少，节约磁盘和网络I/O
• 并不强制要求主从数据库的表定义相同
• 相比基于行的复制更为灵活

缺点：

• 必须要记录上下文信息，以保证语句在主服务器和从服务器上执行的结果相同
• 对于特定的函数如UUID()、user() 这些非确定性函数还是无法复制，如果使用这些函数，可能导致主从服务器上的数据不一致
• 对于存储过程，触发器，自定义函数产生的修改也可能造成主从数据的不一致
• 相比于基于行的复制，在从服务器上执行时需要更多的锁

#确定当前使用的是什么binglog格式
show variables like 'binlog_format';

#显示binlog日志
show binary logs;

#刷新binlog，这会产生一个新的binlog文件，方便测试
flush logs

#查看产生的binglog,使用mysqlbinlog命令。不要使用vim
mysqlbinlog mysql-bin.000053

mysqlbinlog -vv mysql-bin.000053

binlog二进制日志的格式ROW（建议的方式）

基于行的复制（RBR）
mysql5.7版本后的默认格式

基于行的格式 binglog_formart = ROW，这种格式记录了数据表中的增删改查行的信息

row格式可以避免mysql复制中出现的主从不一致的问题

同一SQL语句修改了10000条的情况下，基于段的格式（statement）只会记录这个SQL语句，而基于行的格式（row）会有10000条记录分别记录每一行的数据修改

优点：

• 使mysql主从复制更加安全
• 对每一行的数据修改比基于段的复制更高效，降低主从复制的延迟
• 误操作而修改了数据库中的数据，同时又没有备份可以恢复时，我们可以通过分析二进制日志，对日志中记录的数据修改操作做反向处理的方式来达到恢复数据的目的
• 可以应用于任何SQL的复制包括非确定函数、存储过程等
• 可以减少从服务器锁的使用，增加并发性能

缺点：

• 记录日志量较大，需要通过再配置一个参数控制记录的方式，binlog_row_image = [FULL | MINIMAL | NOBLOB]
• 要求主从服务器上的表结构完全相同（包括字段顺序），否则可能会中断主从复制
• 无法在从服务器上单独执行触发器

#查看binlog_row_image的方式,推荐使用MINIMAL ，只记录修改的列
SHOW VARIABLES LIKE 'binlog_row_image'

binlog二进制日志的格式MIXED

根据SQL语句由操作系统决定选择基于段（statement）或行（row）的格式记录日志

binlog二进制日志记录方式的选择

建议选择 row 或mixed

注意：
使用binlog二进制日志记录方式row时，需要再设置一个参数 binlog_row_image = MINIMAL