MySQL 中的保险,binlog 日志格式种类和优缺点总结

SQL herman 149浏览
公告:“业余草”微信公众号提供免费CSDN下载服务,关注业余草微信公众号,添加作者微信:xmtxtt,发送下载链接帮助你免费下载!
本博客日IP超过1300,PV 1800 左右,急需赞助商。
打开支付宝首页搜“567452957”领红包,间接赞助博主,谢谢!

任何一个系统,或者项目,我们都要提供 3 高支持。比如,Java 中出现异常了,那么 Try-catch-finally 就相当于一份“保险”,而 MySQL 中 binlog 日志就相当于一份“保险”。

MySQL 中 binlog 日志有 ROW,Statement,MiXED 三种格式。

MySQL 的 binlog 日志

这 3 种格式分别解释如下:

  • Row level: 仅保存记录被修改细节,不记录 sql 语句上下文相关信息。
  • Statement level: 每一条会修改数据的 sql 都会记录在 binlog 中。
  • Mixed level:以上两种 leve 的结合。

Row 格式的优点

能非常清晰的记录下每行数据的修改细节,不需要记录上下文相关信息,因此不会发生某些特定情况下的 procedure、function、及 trigger 的调用触发无法被正确复制的问题,任何情况都可以被复制,且能加快从库重放日志的效率,保证从库数据的一致性。

Row 格式的缺点

由于所有的执行的语句在日志中都将以每行记录的修改细节来记录,因此,可能会产生大量的日志内容,干扰内容也较多;比如一条 update 语句,如修改多条记录,则 binlog 中每一条修改都会有记录,这样造成 binlog 日志量会很大,特别是当执行 alter table 之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中,实际等于重建了表。

注意,row 模式生成的 sql 编码需要解码,不能用常规的办法去生成,需要加上相应的参数(–base64-output=decode-rows -v)才能显示出 sql 语句; – 新版本 binlog 默认为 ROW level,且 5.6 新增了一个参数:binlog_row_image;把 binlog_row_image 设置为 minimal 以后,binlog 记录的就只是影响的列,大大减少了日志内容。

Statement 格式的优点

只需要记录执行语句的细节和上下文环境,避免了记录每一行的变化,在一些修改记录较多的情况下相比 ROW level 能大大减少 binlog 日志量,节约 IO,提高性能;还可以用于实时的还原;同时主从版本可以不一样,从服务器版本可以比主服务器版本高。

Statement 格式的缺点

为了保证 sql 语句能在 slave 上正确执行,必须记录上下文信息,以保证所有语句能在 slave 得到和在 master 端执行时候相同的结果;另外,主从复制时,存在部分函数(如 sleep)及存储过程在 slave 上会出现与 master 结果不一致的情况,而相比 Row level 记录每一行的变化细节,绝不会发生这种不一致的情况。

Mixedlevel level

ROW 和 Statement 这两种 level 的混合使用经过前面的对比,可以发现 ROW level 和 statement level 各有优势,如能根据 sql 语句取舍可能会有更好地性能和效果;Mixed level 便是以上两种 leve的结合。不过,新版本的 MySQL 对 row level 模式也被做了优化,并不是所有的修改都会以 row level 来记录,像遇到表结构变更的时候就会以 statement 模式来记录,如果sql语句确实就是 update或者 delete 等修改数据的语句,那么还是会记录所有行的变更;因此,现在一般使用 row level 即可。

选取规则

如果是采用 INSERT,UPDATE,DELETE 直接操作表的情况,则日志格式根据 binlog_format 的设定而记录 如果是采用 GRANT,REVOKE,SET PASSWORD 等管理语句来做的话,那么无论如何都采用 statement 模式记录。

具体配置,可以在 my.cnf 文件中配置,或者使用下面的语句进行配置或修改:

set global binlog_format='ROW';
set global binlog_format='STATEMENT';
set global binlog_format='MIXED';

查看配置的话,可以使用下面的命令进行:

show variables like '%binlog_format%';

有了 MySQL 的 binlog 后,我们就可以做集群,复制,恢复的操作。

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加QQ1群:135430763(2000人群已满),QQ2群:454796847(已满),QQ3群:187424846(已满)。QQ群进群密码:xttblog,想加微信群的朋友,可以微信搜索:xmtxtt,备注:“xttblog”,添加助理微信拉你进群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作可添加助理微信进行沟通!

本文原文出处:业余草: » MySQL 中的保险,binlog 日志格式种类和优缺点总结