MySQL binlog(二进制日志)解析

SQL herman 888浏览 0评论
公告:“业余草”微信公众号提供免费CSDN下载服务(只下Java资源),关注业余草微信公众号,添加作者微信:xttblog,发送下载链接帮助你免费下载!
本博客日IP超过1800,PV 2600 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:xttblog,之前的微信号好友位已满,备注:返现
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序
视频教程免费领

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

binlog 主要作用

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

binlog管理

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

show variables like '%log_bin%';
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| log_bin                         | ON                                  |
| log_bin_basename                | /var/lib/mysql/3306/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/3306/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                 |
| log_bin_use_v1_row_events       | OFF                                 |
| sql_log_bin                     | ON                                  |
+---------------------------------+-------------------------------------+

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

查看binlog文件列表的SQL语句如下:

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |  10343266 |
| mysql-bin.000004 |  10485660 |
| mysql-bin.000005 |     53177 |
| mysql-bin.000006 |      2177 |
| mysql-bin.000007 |      1383 |
+------------------+-----------+

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

 mysql> show master status;
 +------------------+----------+--------------+------------------+-------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
 | mysql-bin.000007 |      120 |              |                  |                   |
 +------------------+----------+--------------+------------------+-------------------+

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

binlog 内容

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

第一种是使用mysqlbinlog工具,用法:mysqlbinlog: /usr/bin/mysqlbinlog  mysql-bin.000007。

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

第二种是直接使用命令行解析。语法如下:

SHOW BINLOG EVENTS
	[IN 'log_name'] //要查询的binlog文件名
	[FROM pos]  
	[LIMIT [offset,] row_count]

下面看一个例子:

mysql> show binlog events in 'mysql-bin.000007' from 1190 limit 2\G
*************************** 13. row ***************************
   Log_name: mysql-bin.000007
		Pos: 1190
 Event_type: Query  //事件类型
  Server_id: 123
End_log_pos: 1352   //结束pose点,下个事件的起点
	   Info: use `test`; insert into tb_person  set name="name__2", address="beijing", sex="man", other="nothing"
*************************** 14. row ***************************
   Log_name: mysql-bin.000007
		Pos: 1352
 Event_type: Xid
  Server_id: 123
End_log_pos: 1383
	   Info: COMMIT /* xid=51 */

binlog 格式

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

  • Row level: 仅保存记录被修改细节,不记录sql语句上下文相关信息优点:能非常清晰的记录下每行数据的修改细节,不需要记录上下文相关信息,因此不会发生某些特定情况下的procedure、function、及trigger的调用触发无法被正确复制的问题,任何情况都可以被复制,且能加快从库重放日志的效率,保证从库数据的一致性 缺点:由于所有的执行的语句在日志中都将以每行记录的修改细节来记录,因此,可能会产生大量的日志内容,干扰内容也较多;比如一条update语句,如修改多条记录,则binlog中每一条修改都会有记录,这样造成binlog日志量会很大,特别是当执行alter table之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中,实际等于重建了表。 tip: – row模式生成的sql编码需要解码,不能用常规的办法去生成,需要加上相应的参数(–base64-output=decode-rows -v)才能显示出sql语句; – 新版本binlog默认为ROW level,且5.6新增了一个参数:binlog_row_image;把binlog_row_image设置为minimal以后,binlog记录的就只是影响的列,大大减少了日志内容
  • Statement level: 每一条会修改数据的sql都会记录在binlog中优点:只需要记录执行语句的细节和上下文环境,避免了记录每一行的变化,在一些修改记录较多的情况下相比ROW level能大大减少binlog日志量,节约IO,提高性能;还可以用于实时的还原;同时主从版本可以不一样,从服务器版本可以比主服务器版本高 缺点:为了保证sql语句能在slave上正确执行,必须记录上下文信息,以保证所有语句能在slave得到和在master端执行时候相同的结果;另外,主从复制时,存在部分函数(如sleep)及存储过程在slave上会出现与master结果不一致的情况,而相比Row level记录每一行的变化细节,绝不会发生这种不一致的情况
  • Mixedlevel level: 以上两种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模式记录

参考资料

业余草公众号

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

本文原文出处:业余草: » MySQL binlog(二进制日志)解析