美文网首页
深入解析 MySQL binlog

深入解析 MySQL binlog

作者: 技术灭霸 | 来源:发表于2020-03-28 20:22 被阅读0次

    一、介绍

    MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。

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

    作用:

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

    二、binlog管理

    1、开启binlog日志

    vi编辑打开mysql配置文件
    # vi /usr/local/mysql/etc/my.cnf
    在[mysqld] 区块
    设置/添加 log-bin=mysql-bin  确认是打开状态(值 mysql-bin 是日志的基本名或前缀名);
    
    重启mysqld服务使配置生效
    # pkill mysqld
    # /usr/local/mysql/bin/mysqld_safe --user=mysql &
    

    2、reset master 清空binlog日志文件

    三、binlog内容

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

    1、 mysqlbinlog

    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事务
    

    2、直命令行解析

    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 */
    

    四、binlog格式

    Mysql binlog日志有ROW,Statement,MiXED三种格式:

    • statement模式下,每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制。
    • row级别下,不记录sql语句上下文相关信息,仅保存哪条记录被修改。记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大。
    • mixed,一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row。

    可通过my.cnf配置文件及 ==set global binlog_format='ROW/STATEMENT/MIXED'== 进行修改

    命令行 ==show variables like 'binlog_format'== 命令查看binglog格式

    此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录。

    五、使用binlog实现主从复制

    复制是mysql最重要的功能之一,mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的;
    从5.6开始复制有两种实现方式,基于binlog和基于GTID(全局事务标示符);
    本文接下来将介绍基于binlog的一主一从复制;其复制的基本过程如下:

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

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

    1、配置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";
    

    2、配置slave

    Slave的配置类似master,需额外设置relay_log参数,slave没有必要开启二进制日志,如果slave为其它slave的master,须设置bin_log

    3、连接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;
    

    4、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还没有开始复制过程。
    

    5、启动复制

    start slave

    6、再次观察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工具来监控主备复制的延迟。

    7、查询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 |
                +----+-------+---------+-----+---------+
    

    五、使用binlog实现恢复数据

    恢复是binlog的两大主要作用之一,接下来通过实例演示如何利用binlog恢复数据:

    1、首先,看下当前binlog位置

    mysql> show master status;

            +------------------+----------+--------------+------------------+-------------------+
            | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
            +------------------+----------+--------------+------------------+-------------------+
            | mysql-bin.000008 |     1847 |              |                  |                   |
            +------------------+----------+--------------+------------------+-------------------+
    

    2、向表tb_person中插入两条记录:

            insert into tb_person  set name="person_1", address="beijing", sex="man", other="test-1";
            insert into tb_person  set name="person_2", address="beijing", sex="man", other="test-2";
    

    3、记录当前binlog位置:

    mysql> show master status;

            +------------------+----------+--------------+------------------+-------------------+
            | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
            +------------------+----------+--------------+------------------+-------------------+
            | mysql-bin.000008 |     2585 |              |                  |                   |
            +------------------+----------+--------------+------------------+-------------------+
    

    4、查询数据

        mysql> select *  from tb_person where name ="person_2" or name="person_1";
    
            +----+----------+---------+-----+--------+
            | id | name     | address | sex | other  |
            +----+----------+---------+-----+--------+
            |  6 | person_1 | beijing | man | test-1 |
            |  7 | person_2 | beijing | man | test-2 |
            +----+----------+---------+-----+--------+
    

    5、删除一条: delete from tb_person where name ="person_2";

    mysql> select * from tb_person where name ="person_2" or name="person_1";

            +----+----------+---------+-----+--------+
            | id | name     | address | sex | other  |
            +----+----------+---------+-----+--------+
            |  6 | person_1 | beijing | man | test-1 |
            +----+----------+---------+-----+--------+
    

    6、 binlog恢复(指定pos点恢复/部分恢复)

    mysqlbinlog --start-position=1847 --stop-position=2585 mysql-bin.000008 > test.sql

    mysql> source /var/lib/mysql/3306/test.sql

    7、数据恢复完成

    mysql> select * from tb_person where name ="person_2" or name="person_1";

            +----+----------+---------+-----+--------+
            | id | name     | address | sex | other  |
            +----+----------+---------+-----+--------+
            |  6 | person_1 | beijing | man | test-1 |
            |  7 | person_2 | beijing | man | test-2 |
            +----+----------+---------+-----+--------+
    

    8、总结

    恢复,就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已

    七、redo log和binlog区别

    1. redo log是属于innoDB层面,binlog属于MySQL Server层面的,这样在数据库用别的存储引擎时可以达到一致性的要求。
    2. redo log是物理日志,记录该数据页更新的内容;binlog是逻辑日志,记录的是这个更新语句的原始逻辑
    3. redo log是循环写,日志空间大小固定;binlog是追加写,是指一份写到一定大小的时候会更换下一个文件,不会覆盖。
    4. redo log作为异常宕机或者介质故障后的数据恢复使用,binlog可以作为恢复数据使用,主从复制搭建。

    相关文章

      网友评论

          本文标题:深入解析 MySQL binlog

          本文链接:https://www.haomeiwen.com/subject/ycwsuhtx.html