MySQLbinlog(二进制日志)解析

binlog 顾名思义就是一种二进制日志&＃xff0c;是一种与innodb引擎中redo/undo log完全不同的日志。它主要是用来记录对mysql数据更新或潜在发生更新的SQL语句&＃xff0c;并以"事务"的形式保存在磁盘中。

binlog 主要作用

• 复制&＃xff1a;MySQL Replication在Master端开启binlog&＃xff0c;Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
• 数据恢复&＃xff1a;通过mysqlbinlog工具恢复数据
• 增量备份

binlog管理

开启binlogmy.cnf配置中设置&＃xff1a;log_bin&＃61;"存放binlog路径目录"。binlog信息查询binlog开启后&＃xff0c;可以在配置文件中查看其位置信息&＃xff0c;也可以在myslq命令行中查看&＃xff1a;

binlog文件开启binlog后&＃xff0c;会在数据目录&＃xff08;默认&＃xff09;生产host-bin.n&＃xff08;具体binlog信息&＃xff09;文件及host-bin.index索引文件&＃xff08;记录binlog文件列表&＃xff09;。当binlog日志写满(binlog大小max_binlog_size&＃xff0c;默认1G),或者数据库重启才会生产新文件&＃xff0c;但是也可通过手工进行切换让其重新生成新的文件&＃xff08;flush logs&＃xff09;&＃xff1b;另外&＃xff0c;如果正使用大的事务&＃xff0c;由于一个事务不能横跨两个文件&＃xff0c;因此也可能在binlog文件未满的情况下刷新文件。

查看binlog文件列表的SQL语句如下&＃xff1a;

show master status语句可以显示binlog的状态&＃xff0c;包含当前二进制日志文件的状态&＃xff0c;正在写入的二进制文件&＃xff0c;及当前position等信息。

reset master语句用于清空binlog日志文件。

binlog 内容

默认情况下binlog日志是二进制格式&＃xff0c;无法直接查看。可使用两种方式进行查看&＃xff0c;下面我分别列举一下&＃xff01;

第一种是使用mysqlbinlog工具&＃xff0c;用法&＃xff1a;mysqlbinlog: /usr/bin/mysqlbinlog  mysql-bin.000007。

mysqlbinlog是mysql官方提供的一个binlog查看工具&＃xff0c;也可使用–read-from-remote-server从远程服务器读取二进制日志&＃xff0c;还可使用–start-position –stop-position、–start-time&＃61; –stop-time精确解析binlog日志。

第二种是直接使用命令行解析。语法如下&＃xff1a;

下面看一个例子&＃xff1a;

binlog 格式

Mysql binlog日志有ROW,Statement,MiXED三种格式&＃xff1b;可通过my.cnf配置文件及 &＃61;&＃61;set global binlog_format&＃61;&＃39;ROW/STATEMENT/MIXED&＃39;&＃61;&＃61; 进行修改&＃xff0c;命令行 &＃61;&＃61;show variables like &＃39;binlog_format&＃39;&＃61;&＃61; 命令查看binglog格式。

• Row level: 仅保存记录被修改细节&＃xff0c;不记录sql语句上下文相关信息优点&＃xff1a;能非常清晰的记录下每行数据的修改细节&＃xff0c;不需要记录上下文相关信息&＃xff0c;因此不会发生某些特定情况下的procedure、function、及trigger的调用触发无法被正确复制的问题&＃xff0c;任何情况都可以被复制&＃xff0c;且能加快从库重放日志的效率&＃xff0c;保证从库数据的一致性 缺点:由于所有的执行的语句在日志中都将以每行记录的修改细节来记录&＃xff0c;因此&＃xff0c;可能会产生大量的日志内容&＃xff0c;干扰内容也较多&＃xff1b;比如一条update语句&＃xff0c;如修改多条记录&＃xff0c;则binlog中每一条修改都会有记录&＃xff0c;这样造成binlog日志量会很大&＃xff0c;特别是当执行alter table之类的语句的时候&＃xff0c;由于表结构修改&＃xff0c;每条记录都发生改变&＃xff0c;那么该表每一条记录都会记录到日志中&＃xff0c;实际等于重建了表。 tip: – row模式生成的sql编码需要解码&＃xff0c;不能用常规的办法去生成&＃xff0c;需要加上相应的参数(–base64-output&＃61;decode-rows -v)才能显示出sql语句; – 新版本binlog默认为ROW level&＃xff0c;且5.6新增了一个参数&＃xff1a;binlog_row_image&＃xff1b;把binlog_row_image设置为minimal以后&＃xff0c;binlog记录的就只是影响的列&＃xff0c;大大减少了日志内容
• Statement level: 每一条会修改数据的sql都会记录在binlog中优点&＃xff1a;只需要记录执行语句的细节和上下文环境&＃xff0c;避免了记录每一行的变化&＃xff0c;在一些修改记录较多的情况下相比ROW level能大大减少binlog日志量&＃xff0c;节约IO&＃xff0c;提高性能&＃xff1b;还可以用于实时的还原&＃xff1b;同时主从版本可以不一样&＃xff0c;从服务器版本可以比主服务器版本高 缺点&＃xff1a;为了保证sql语句能在slave上正确执行&＃xff0c;必须记录上下文信息&＃xff0c;以保证所有语句能在slave得到和在master端执行时候相同的结果&＃xff1b;另外&＃xff0c;主从复制时&＃xff0c;存在部分函数&＃xff08;如sleep&＃xff09;及存储过程在slave上会出现与master结果不一致的情况&＃xff0c;而相比Row level记录每一行的变化细节&＃xff0c;绝不会发生这种不一致的情况
• Mixedlevel level: 以上两种level的混合使用经过前面的对比&＃xff0c;可以发现ROW level和statement level各有优势&＃xff0c;如能根据sql语句取舍可能会有更好地性能和效果&＃xff1b;Mixed level便是以上两种leve的结合。不过&＃xff0c;新版本的MySQL对row level模式也被做了优化&＃xff0c;并不是所有的修改都会以row level来记录&＃xff0c;像遇到表结构变更的时候就会以statement模式来记录&＃xff0c;如果sql语句确实就是update或者delete等修改数据的语句&＃xff0c;那么还是会记录所有行的变更&＃xff1b;因此&＃xff0c;现在一般使用row level即可。
• 选取规则如果是采用 INSERT&＃xff0c;UPDATE&＃xff0c;DELETE 直接操作表的情况&＃xff0c;则日志格式根据 binlog_format 的设定而记录 如果是采用 GRANT&＃xff0c;REVOKE&＃xff0c;SET PASSWORD 等管理语句来做的话&＃xff0c;那么无论如何都采用statement模式记录

参考资料

• https://dev.mysql.com/doc/internals/en/binary-log-versions.html
• http://www.php.cn/mysql-tutorials-361643.html
• https://www.jianshu.com/p/c16686b35807
• https://www.cnblogs.com/jackluo/p/3336585.html
• http://www.cnblogs.com/hustcat/archive/2009/12/19/1627525.html