腾讯云 CDB : 深入解析 MySQL binlog-深蓝源码网

时间: 2020-09-03 00:08:26 人气: 2287 评论: 0

1.概述

binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以"事务"的形式保存在磁盘中；

作用主要有：

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

2.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文件未满的情况下刷新文件
mysql> show binary logs; //查看binlog文件列表,
+------------------+-----------+
| 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 |
+------------------+-----------+

查看binlog的状态：show master status可查看当前二进制日志文件的状态信息，显示正在写入的二进制文件，及当前position
 mysql> show master status;
 +------------------+----------+--------------+------------------+-------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
 | mysql-bin.000007 |      120 |              |                  |                   |
 +------------------+----------+--------------+------------------+-------------------+

reset master 清空binlog日志文件

3.binlog内容

默认情况下binlog日志是二进制格式，无法直接查看。可使用两种方式进行查看：

    a. mysqlbinlog: /usr/bin/mysqlbinlog  mysql-bin.000007
        - mysqlbinlog是mysql官方提供的一个binlog查看工具，
        - 也可使用–read-from-remote-server从远程服务器读取二进制日志，
        - 还可使用--start-position --stop-position、--start-time= --stop-time精确解析binlog日志
        
        截取位置1190-1352 binlog如下：
        ***************************************************************************************
        # at 1190   //事件的起点
        #171223 21:56:26 server id 123  end_log_pos 1190 CRC32 0xf75c94a7 	Intvar
        SET INSERT_ID=2/*!*/;
        #171223 21:56:26 server id 123  end_log_pos 1352 CRC32 0xefa42fea 	Query	thread_id=4	exec_time=0	error_code=0
        SET TIMESTAMP=1514123786/*!*/;              //开始事务的时间起点 (每个at即为一个event)
        insert into tb_person  set name="name__2", address="beijing", sex="man", other="nothing"  //sql语句
        /*!*/;
        # at 1352
        #171223 21:56:26 server id 123  end_log_pos 1383 CRC32 0x72c565d3 	Xid = 5 //执行时间，及位置戳，Xid:事件指示提交的XA事务
        ***************************************************************************************
    
    b.直命令行解析
        SHOW BINLOG EVENTS
            [IN 'log_name'] //要查询的binlog文件名
            [FROM pos]  
            [LIMIT [offset,] row_count]  
       
        1190-135如下：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 */

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

5.复制

复制是mysql最重要的功能之一，mysql集群的高可用、技术沙龙教程文章热点综合

分享链接