教你MySQL Binlog实用攻略

系统管理员 2022-03-28 12:10 119阅读 0赞

> 本文由云+社区发表

### **1.概述** ###

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

作用主要有：

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

### **2.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集群的高可用、负载均衡和读写分离都是基于复制来实现的；从5.6开始复制有两种实现方式，基于binlog和基于GTID（全局事务标示符）；本文接下来将介绍基于binlog的一主一从复制；其复制的基本过程如下：

a.Master将数据改变记录到二进制日志(binary log)中
        b.Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
        c.Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。
            返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
        d.Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的
            文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容
        e.Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行

接下来使用实例演示基于binlog的主从复制：

a.配置master
            主要包括设置复制账号，并授予REPLICATION SLAVE权限，具体信息会存储在于master.info文件中，及开启binlog；
            mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
            mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';
            mysql> show variables like "log_bin";
                +---------------+-------+
                | Variable_name | Value |
                +---------------+-------+
                | log_bin       | ON    |
                +---------------+-------+
            查看master当前binlogmysql状态：mysql> show master status;
                +------------------+----------+--------------+------------------+-------------------+
                | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
                +------------------+----------+--------------+------------------+-------------------+
                | mysql-bin.000003 |      120 |              |                  |                   |
                +------------------+----------+--------------+------------------+-------------------+
            建表插入数据：
                CREATE TABLE `tb_person` (
                   `id` int(11) NOT NULL AUTO_INCREMENT,
                   `name` varchar(36) NOT NULL,                           
                   `address` varchar(36) NOT NULL DEFAULT '',    
                   `sex` varchar(12) NOT NULL DEFAULT 'Man' ,
                   `other` varchar(256) NOT NULL ,
                   PRIMARY KEY (`id`)
                 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
    
                 insert into tb_person  set name="name1", address="beijing", sex="man", other="nothing";
                 insert into tb_person  set name="name2", address="beijing", sex="man", other="nothing";
                 insert into tb_person  set name="name3", address="beijing", sex="man", other="nothing";
                 insert into tb_person  set name="name4", address="beijing", sex="man", other="nothing";
        b.配置slave
            Slave的配置类似master，需额外设置relay_log参数，slave没有必要开启二进制日志，如果slave为其它slave的master，须设置bin_log
        c.连接master
            mysql> CHANGE MASTER TO
               MASTER_HOST='10.108.111.14',
               MASTER_USER='test',
               MASTER_PASSWORD='123456',
               MASTER_LOG_FILE='mysql-bin.000003',
               MASTER_LOG_POS=120;
        d.show slave status;
            mysql> show slave status\G
            *************************** 1. row ***************************
                           Slave_IO_State:   ---------------------------- slave io状态，表示还未启动
                              Master_Host: 10.108.111.14  
                              Master_User: test  
                              Master_Port: 20126  
                            Connect_Retry: 60   ------------------------- master宕机或连接丢失从服务器线程重新尝试连接主服务器之前睡眠时间
                          Master_Log_File: mysql-bin.000003  ------------ 当前读取master binlog文件
                      Read_Master_Log_Pos: 120  ------------------------- slave读取master binlog文件位置
                           Relay_Log_File: relay-bin.000001  ------------ 回放binlog
                            Relay_Log_Pos: 4   -------------------------- 回放relay log位置
                    Relay_Master_Log_File: mysql-bin.000003  ------------ 回放log对应maser binlog文件
                         Slave_IO_Running: No
                        Slave_SQL_Running: No
                      Exec_Master_Log_Pos: 0  --------------------------- 相对于master从库的sql线程执行到的位置
                    Seconds_Behind_Master: NULL
            Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running为NO说明slave还没有开始复制过程。
        e.启动复制
            start slave
        f.再次观察slave状态
            mysql> show slave status\G
            *************************** 1. row ***************************
                           Slave_IO_State: Waiting for master to send event -- 等待master新的event
                              Master_Host: 10.108.111.14
                              Master_User: test
                              Master_Port: 20126
                            Connect_Retry: 60
                          Master_Log_File: mysql-bin.000003
                      Read_Master_Log_Pos: 3469  ---------------------------- 3469  等于Exec_Master_Log_Pos，已完成回放
                           Relay_Log_File: relay-bin.000002                    ||
                            Relay_Log_Pos: 1423                                ||
                    Relay_Master_Log_File: mysql-bin.000003                    ||
                         Slave_IO_Running: Yes                                 ||
                        Slave_SQL_Running: Yes                                 ||
                      Exec_Master_Log_Pos: 3469  -----------------------------3469  等于slave读取master binlog位置，已完成回放
                    Seconds_Behind_Master: 0
            可看到slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master=0。Relay_Log_Pos增加，意味着一些事件被获取并执行了。
    
            最后看下如何正确判断SLAVE的延迟情况，判定slave是否追上master的binlog：
            1、首先看 Relay_Master_Log_File 和 Maser_Log_File 是否有差异；
            2、如果Relay_Master_Log_File 和 Master_Log_File 是一样的话，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，对比SQL线程比IO线程慢了多少个binlog事件；
            3、如果Relay_Master_Log_File 和 Master_Log_File 不一样，那说明延迟可能较大，需要从MASTER上取得binlog status，判断当前的binlog和MASTER上的差距；
            4、如果以上都不能发现问题，可使用pt_heartbeat工具来监控主备复制的延迟。
    
        g.查询slave数据，主从一致
            mysql> select * from tb_person;
                +----+-------+---------+-----+---------+
                | id | name  | address | sex | other   |
                +----+-------+---------+-----+---------+
                |  5 | name4 | beijing | man | nothing |
                |  6 | name2 | beijing | man | nothing |
                |  7 | name1 | beijing | man | nothing |
                |  8 | name3 | beijing | man | nothing |
                +----+-------+---------+-----+---------+
    关于mysql复制的内容还有很多，比如不同的同步方式、复制格式情况下有什么区别，有什么特点，应该在什么情况下使用....这里不再一一介绍。

### **6.恢复** ###

### **7.总结** ###

本文简要介绍binlog原理及其在恢复、复制中的使用方法；更多深入分析可参考mysql官方文档及相关博客。

### **8.参考** ###

*  *[https://dev.mysql.com/doc/int...][https_dev.mysql.com_doc_int...]*
 *  *[http://www.php.cn/mysql-tutor...][http_www.php.cn_mysql-tutor...]*
 *  *[https://www.jianshu.com/p/c16...][https_www.jianshu.com_p_c16...]*
 *  *[https://www.cnblogs.com/jackl...][https_www.cnblogs.com_jackl...]*
 *  *[http://www.cnblogs.com/hustca...][http_www.cnblogs.com_hustca...]*

**此文已由作者授权腾讯云+社区在各渠道发布**

**获取更多新鲜技术干货，可以关注我们[腾讯云技术社区-云加社区官方号及知乎机构号][-]**

[https_dev.mysql.com_doc_int...]: https://dev.mysql.com/doc/internals/en/binary-log-versions.html
[http_www.php.cn_mysql-tutor...]: http://www.php.cn/mysql-tutorials-361643.html
[https_www.jianshu.com_p_c16...]: https://www.jianshu.com/p/c16686b35807
[https_www.cnblogs.com_jackl...]: https://www.cnblogs.com/jackluo/p/3336585.html
[http_www.cnblogs.com_hustca...]: http://www.cnblogs.com/hustcat/archive/2009/12/19/1627525.html
[-]: https://www.zhihu.com/org/teng-xun-yun-ji-zhu-she-qu/activities