• 欢迎访问web前端中文站,JavaScript,CSS3,HTML5,web前端demo
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏web前端中文站吧

MySQL之binlog(二进制日志)解析教程

JAVA web前端中文站 2年前 (2018-02-01) 703次浏览 已收录 0个评论

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

后面会详细写下有关binlog 的复制binlog 恢复教程

更多精彩内容请看 web 前端中文站
http://www.lisa33xiaoq.net 可按 Ctrl + D 进行收藏

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 模式记录

参考资料

【注:本文源自网络文章资源,由站长整理发布】


web 前端中文站 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:MySQL 之 binlog(二进制日志)解析教程
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址