mysql binlog的三种格式及区别

简单概括总结

1、三种格式：row、statement、mixed

2、区别：row格式文件比较大，statement比较小，row格式保存的是一行一行的数据，statement保存的是sql语句，mixed格式介于二者之间，statement容易丢数据，row格式则不会

3、statement容易丢数据原因是，有时候，SQL语句里面会用到一些函数，比如说取当前日期的函数sysdate，你要是用statement，binlog里同步过去的就是这个带有函数的SQL语句，而主库的当前日期，和binlog同步到slave上的当前日期，肯定是有差异的，这样两条数据就不一致了，所以这样同步的数据，就会有问题

4、row是直接把表插入到备份库中，statement是导出主库语句后，导入到备份库中，存在时间差。

每种格式的概括

STATEMENT

记录的是执行的SQL语句

优点:
日志记录量相对较小, 节约磁盘及网络IO

缺点:
可能造成MySQL复制的主备服务器数据不一致
必须记录上下文信息, 以保证语句在从服务器上执行结果相同
对于特定函数如 UUID(), user() 这种非确定性函数是无法正确复制

ROW

记录的是每一行数据的修改, MySQL5.7+的默认ROW格式.

优点:
可以避免MySQL复制中出现主从不一致的问题
对每一行数据的修改比STATEMENT模式高效
可在误删改数据后, 同时无备份可以恢复时, 通过分析binlog日志进行反向处理达到恢复数据目的

缺点:
由于记录每一行数据的修改, 所以日志量比较大
可通过binlog_row_image=FULL | MINIMAL | NOBLOB 设置日志记录的方式.

FULL: 记录行中所有列修改前后的数据.
MINIMAL: 记录行中所有列修改前的数据+被修改列修改后的数据.
NOBLOB: 记录行中所有列修改前的数据+(未对行中TEXT和BLOB类型列修改时, 记录TEXT和BLOB类型以外的列的数据.)

MIXED

混合STATEMENT和ROW两种格式, MySQL会根据执行的SQL语句自动选择.
一般的复制使用STATEMENT格式，对于STATEMENT格式无法复制的操作使用ROW格式.

如何选择binlog日志格式?
在同一个IDC机房中, 建议使用MIXED或ROW格式, 当使用ROW格式时, 建议设置binlog_row_image=MINIMAL

原文链接：https://blog.csdn.net/moxiaotang/article/details/124665108

知乎：https://zhuanlan.zhihu.com/p/65743530