binlog

mysql binlog的三种格式简单概括总结

三种格式：row、statement、mixed

区别：row格式文件比较大，statement比较小，row格式保存的是一行一行的数据，statement保存的是sql语句，mixed格式介于二者之间，statement容易丢数据，row格式则不会。statement容易丢数据原因是，有时候，SQL语句里面会用到一些函数，比如说取当前日期的函数sysdate，你要是用statement，binlog里同步过去的就是这个带有函数的SQL语句，而主库的当前日期，和binlog同步到slave上的当前日期，肯定是有差异的，这样两条数据就不一致了，所以这样同步的数据，就会有问题。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格式？

//查看binlog是否开启
show variables like 'log_bin%;
//查看binlog格式
show global variables like "binlog_format%";

生产环境

log_bin=ON

binlog_format=ROW

binlog_row_image=FULL