mysql5.7 Replication with Gtid

2017-05-22 18:10:42

测试介绍: 一主一从
运行环境:
centos7.3、mysql5.7.17

理论准备

  • GTID工作原理

    1
    2
    3
    4
    5
    6
    1. master更新数据时，会在事务前产生GTID，一同记录到binlog日志中
    2. slave端的i/o 线程将变更的binlog，写入到本地的relay log中
    3. sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录
    4. 如果有记录，说明该GTID的事务已经执行，slave会忽略
    5. 如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog
    6. 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描

  • GTID相关概念

  1. GTID = server_uuid:transaction_id
    server_uuid由MySQL在第一次启动时自动生成并被持久化到auto.cnf文件里，TID（transaction_id）是一个从1开始的自增计数，表示在这个主库上执行的第n个事务。MySQL会保证事务与GTID之间的1 : 1映射
  2. server_uuid 来源于 auto.cnf
    数据目录下有一个auto.cnf文件就是用来保存server_uuid
  3. GTID: 在一组复制中，全局唯一
    MySQL只要保证每台数据库的server_uuid全局唯一，以及每台数据库生成的transaction_id自身唯一，就能保证GTID的全局唯一性
  4. gtid_executed
    在当前实例上执行过的GTID集合; 实际上包含了所有记录到binlog中的事务。所以，设置set sql_log_bin=0后执行的事务不会生成binlog 事件，也不会被记录到gtid_executed中。执行RESET MASTER可以将该变量置空
  5. gtid_purged
    gtid_purged用于记录已经被清除了的binlog事务集合，它是gtid_executed的子集。只有gtid_executed为空时才能手动设置该变量，此时会同时更新gtid_executed为和gtid_purged相同的值。gtid_executed为空意味着要么之前没有启动过基于GTID的复制，要么执行过RESET MASTER。执行RESET MASTER时同样也会把gtid_purged置空，即始终保持gtid_purged是gtid_executed的子集

  6. gtid_next
    AUTOMATIC:自动生成下一个GTID，实现上是分配一个当前实例上尚未执行过的序号最小的GTID
    ANONYMOUS:设置后执行事务不会产生GTID
    显式指定的GTID:可以指定任意形式合法的GTID值，但不能是当前gtid_executed中的已经包含的GTID，否则，下次执行事务时会报错

    更多详情参考replication-options-gtids

  • 开启GTID的必备条件

    1
    2
    3
    4
    gtid_mode=ON(必选)  
    enforce-gtid-consistency（必选）
    log_bin=ON（可选）--高可用切换，最好设置ON  
    log-slave-updates=ON（可选）--高可用切换，最好设置ON

  • GTID的不足

  1. 无法使用 CREATE TABLE … SELECT statements语句
  2. 无法在事务中使用 CREATE TEMPORARY TABLE
  3. 无法在事务中对非事务存储引擎进行更新
  4. 具体可参考: Restrictions on Replication with GTIDs
复制实现

  • 这里我们的测试环境如下

    1
    2
    3
    4
    5
    6
    7
    master ip: 192.168.1.105
    slave ip : 192.168.1.106
    复制账户:
    root@localhost [(none)] > CREATE USER 'repl'@'192.169.1.%' IDENTIFIED BY 'repl_123456';
    Query OK, 0 rows affected (0.13 sec)
    root@localhost [(none)] > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
    Query OK, 0 rows affected (0.00 sec)

  • 全新开始搭建

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    1. 所有server处于同一状态
    	mysql> SET @@global.read_only = ON;
    2. 关闭所以mysql
        mysqladmin -uusername -p shutdown
    3. 配置my.cnf开启GTID,重启mysql
        root@localhost [(none)] > show global variables like '%gtid%';
        +----------------------------------+-------+
        | Variable_name                    | Value |
        +----------------------------------+-------+
        | binlog_gtid_simple_recovery      | ON    |
        | enforce_gtid_consistency         | ON    |
        | gtid_executed                    |       |
        | gtid_executed_compression_period | 1000  |
        | gtid_mode                        | ON    |
        | gtid_owned                       |       |
        | gtid_purged                      |       |
        | session_track_gtids              | OFF   |
        +----------------------------------+-------+
        8 rows in set (0.01 sec)
        
        root@localhost [(none)] > show variables like '%gtid_next%';
        +---------------+-----------+
        | Variable_name | Value     |
        +---------------+-----------+
        | gtid_next     | AUTOMATIC |
        +---------------+-----------+
        1 row in set (0.01 sec)
    	
    或者
       mysqld --gtid-mode=ON --log-bin --enforce-gtid-consistency &
    	
    4. change master 
       mysql> change master to
             master_host=host,
             master_port=port,
             master_user=username,
             master_password=password,
             master_auto_position=1;
       mysql> start slave;
       
       root@localhost [(none)] > CHANGE MASTER TO  MASTER_HOST='192.168.1.105',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl_123456',MASTER_AUTO_POSITION=1;
    	Query OK, 0 rows affected, 2 warnings (0.16 sec)
       root@localhost [(none)] > start slave;
    	Query OK, 0 rows affected (0.01 sec)
    	
       root@localhost [(none)] > show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.105
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 154
                   Relay_Log_File: localhost-relay-bin.000003
                    Relay_Log_Pos: 367
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 154
                  Relay_Log_Space: 791
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 233
                      Master_UUID: 3288e218-3ecc-11e7-8f0d-005056b2706f
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)

    ERROR: 
    No query specified
    	
    5. 让master 恢复读写
        mysql > SET @@global.read_only = OFF;

  • 从备份中恢复&搭建

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    1. 备份
       mysqldump xx 获取并且记录gtid_purged值
       --使用mysqldump导出数据库
       # mysqldump --all-databases --single-transaction --triggers --routines --events \
         --host=localhost --port=3306 --user=user --password=password >/tmp/alldb.sql        
       --导出的文件中已经包含了GTID_PURGED的信息
       # grep GTID_PURGED /tmp/alldb.sql   
       SET @@GLOBAL.GTID_PURGED='78336cdc-8cfb-11e6-ba9f-000c29328504:1-38';   
       or
       冷备份 --获取并且记录gtid_executed值，这个就相当于mysqldump中得到的gtid_purged
    2. 在新服务器上reset master,导入备份
       reset master; --清空gtid信息  
       导入备份； --如果是逻辑导入，请设置sql_log_bin=off
       set global gtid_purged=xx;
       
       mysql>SET @@GLOBAL.GTID_PURGED='78336cdc-8cfb-11e6-ba9f-000c29328504:1-38';  
    3. change master
    	mysql> change master to
               master_host=host,
               master_port=port,
               master_user=username,
               master_password=password,
               master_auto_position=1;
        mysql> start slave;
    	
       root@localhost [(none)] > CHANGE MASTER TO  MASTER_HOST='192.168.1.105',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl_123456',MASTER_AUTO_POSITION=1;
    	Query OK, 0 rows affected, 2 warnings (0.16 sec)
       root@localhost [(none)] > start slave;
    	Query OK, 0 rows affected (0.01 sec)
GTID 运维和错误处理
  • 错误场景: Errant transaction
    原因:
  1. 复制参数没有配置正确，当slave crash后，会出现重复键问题

  2. DBA操作不正确，不小心在slave上执行了事务
    传统模式下:

    1
    2
    3
    * skip transation; 
    mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    mysql> START SLAVE;

    GTID模式下:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    mysql> SET GTID_NEXT='b9b4712a-df64-11e3-b391-60672090eb04:7';   --设置需要跳过的gtid event
    mysql> BEGIN;COMMIT;
    mysql> SET GTID_NEXT='AUTOMATIC';
    mysql> START SLAVE;

    其中gtid_next就是跳过某个执行事务，设置gtid_next的方法一次只能跳过一个事务，要批量的跳过事务可以通过设置gtid_purged完成
    mysql> reset master;
    mysql> set global gtid_purged='b9b4712a-df64-11e3-b391-60672090eb04:1-7,b9b4712a-df64-11e3-b391-60672090eb05:6';
    mysql> show master status;

    此时从库的Executed_Gtid_Set已经包含了主库上'1-7'和'6'的事务，再开启复制会从后面的事务开始执行，就不会出错了。注意，使用gtid_next和gtid_purged修复复制错误的前提是，跳过那些事务后仍可以确保主备数据一致。如果做不到，就要考虑pt-table-sync或者拉备份的方式了

    无论是否启用GTID，使用pt-slave-restart工具:

    1
    2
    #忽略所有1062错误，并再次启动SLAVE进程
    pt-slave-resetart -S./mysql.sock —error-numbers=1062

    备注: 当发生inject empty transction后，有可能会丢失事务
    当slave上inject empty transction，说明有一个master的事务被忽略了（这里假设是 $uuid:100）
    事务丢失一：如果此时此刻master挂了，这个slave被选举为新master，那么其他的slave如果还没有执行到$uuid:100,就会丢失掉$uuid:100这个事务
    事务丢失二：如果从备份中重新搭建一个slave，需要重新执行之前的所有事务，而此时，master挂了， 又回到了事务丢失一的场景

    mysqldump时GTID参数
    [root@localhost ~]# /usr/local/whistle/mysql/bin/mysqldump –help|grep gtid-purged -A8
    –set-gtid-purged[=name] 

    Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible
values for this option are ON, OFF and AUTO. If ON is
used and GTIDs are not enabled on the server, an error is
generated. If OFF is used, this option does nothing. If
AUTO is used and GTIDs are enabled on the server, 'SET
@@GLOBAL.GTID_PURGED' is added to the output. If GTIDs
are disabled, AUTO does nothing. If no value is supplied
then the default (AUTO) value will be considered.

ref

MySQL5.7杀手级新特性：GTID原理与实战
MySQL基于GTID的复制实现详解
MySQL 5.7 Replication 相关新功能说明
5.6mysqldump gtid的一个小坑

您的鼓励是我写作最大的动力

俗话说，投资效率是最好的投资。 如果您感觉我的文章质量不错，读后收获很大，预计能为您提高 10% 的工作效率，不妨小额捐助我一下，让我有动力继续写出更多好文章。