MySQL GTID你知多少

MySQL在5.6的版本推出了GTID复制,相比传统的复制,GTID复制对于运维更加友好,这个事务是谁产⽣,产⽣多少事务,⾮常直接的标识出来,当然GTID也有限制,对于什么是GTID可以参考我之前的文章:MySQL 5.6 GTID Replication,那么今天主要是想和同学们分享一下关于从库show slave status中的Retrieved_Gtid_Set和Executed_Gtid_Set。

Retrieved_Gtid_Set:从库已经接收到主库的事务编号
Executed_Gtid_Set:已经执行的事务编号
 
那么下面截图中的这个如何解释?
 
 那么下面慢慢和大家道来,莫慌。首先看看master和slave的server-uuid
master:
[root@localhost][db1]> show variables like '%uuid%';       
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 2a09ee6e-645d-11e7-a96c-000c2953a1cb |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

[root@localhost][db1]> 

slave:

[root@localhost][(none)]> show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 8ce853fc-6f8a-11e7-8940-000c29e3f5ab |
+---------------+--------------------------------------+
1 row in set (0.01 sec)

[root@localhost][(none)]> 

其中主库的server-id是10,从库的server-id是20 

搭建好主从以后如果没有数据写入,那么show slave status是下面这样的:

Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 2a09ee6e-645d-11e7-a96c-000c2953a1cb
             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: 

如果在主库创建表,并且写入2条数据,那么是下面这样的:

[root@localhost][db1]> create table t2 ( id int);
Query OK, 0 rows affected (0.07 sec)

[root@localhost][db1]> insert into t2 select 1;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@localhost][db1]> insert into t2 select 2;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@localhost][db1]> 

从库:

Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 2a09ee6e-645d-11e7-a96c-000c2953a1cb
             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: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3
            Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3
                Auto_Position: 1

主库:

[root@localhost][db1]> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      912 |              |                  | 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

其中主库的Executed_Gtid_Set为2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3

可以看见Retrieved_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,也就是说主库产生了3个事务,从库接受到了来自主库的3个事务,并且都已经执行。
其中2a09ee6e-645d-11e7-a96c-000c2953a1cb是主库的server-uuid。那么我们可以解析从库的binlog再看看

# at 154
#170823  0:38:38 server id 10  end_log_pos 219 CRC32 0x6268641f         GTID    last_committed=0        sequence_number=1
SET @@SESSION.GTID_NEXT= '2a09ee6e-645d-11e7-a96c-000c2953a1cb:1'/*!*/;
# at 219
#170823  0:38:38 server id 10  end_log_pos 316 CRC32 0x6c837618         Query   thread_id=103   exec_time=0     error_code=0
use `db1`/*!*/;
SET TIMESTAMP=1503419918/*!*/;
SET @@session.pseudo_thread_id=103/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table t2 ( id int)
/*!*/;

可以看见server-id为10,gtid-next为2a09ee6e-645d-11e7-a96c-000c2953a1cb:1,执行了建表。剩下的2-3是执行的数据插入,我这里没写出来。

这也体现了文章开始提到的:这个事务是谁产⽣,产⽣多少事务,⾮常直接的标识出来

那么对于文章开头那个诡异的gtid是怎么出来的呢?先说说已经执行的事务:

Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1

这里的2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33肯定很好理解,就是已经执行主库的1-33的事务,那么8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1呢?这个其实也简单,有两种情况:

第一种情况:从库有数据写入( 从库插入数据 )

[root@localhost][db1]> insert into t2 select 1;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

show slave status

Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 2a09ee6e-645d-11e7-a96c-000c2953a1cb
             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: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3
            Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 

可以看见已经执行的事务有来自主库的2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,也有从库刚自己写入的数据:8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1。我们可以解析binlog看看。

mysqlbinlog -vv mysql-bin.000001 --include-gtids='8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1'
# at 896
#170823  0:59:19 server id 20  end_log_pos 961 CRC32 0x0492528a         GTID    last_committed=3        sequence_number=4
SET @@SESSION.GTID_NEXT= '8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1'/*!*/;
# at 961
#170823  0:59:19 server id 20  end_log_pos 1032 CRC32 0xbf545cca        Query   thread_id=25    exec_time=0     error_code=0
SET TIMESTAMP=1503421159/*!*/;
SET @@session.pseudo_thread_id=25/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 1032
#170823  0:59:19 server id 20  end_log_pos 1079 CRC32 0x2f2de3ec        Rows_query
# insert into t2 select 1
# at 1079
#170823  0:59:19 server id 20  end_log_pos 1123 CRC32 0x18fe1c5c        Table_map: `db1`.`t2` mapped to number 109
# at 1123
#170823  0:59:19 server id 20  end_log_pos 1163 CRC32 0x163a708e        Write_rows: table id 109 flags: STMT_END_F

BINLOG '
52KcWR0UAAAALwAAADcEAACAABdpbnNlcnQgaW50byB0MiBzZWxlY3QgMezjLS8=
52KcWRMUAAAALAAAAGMEAAAAAG0AAAAAAAEAA2RiMQACdDIAAQMAAVwc/hg=
52KcWR4UAAAAKAAAAIsEAAAAAG0AAAAAAAEAAgAB//4BAAAAjnA6Fg==
'/*!*/;
### INSERT INTO `db1`.`t2`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 1163
#170823  0:59:19 server id 20  end_log_pos 1194 CRC32 0xe3347ac1        Xid = 68
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

从binlog中可以清楚的看到是从库进行了写入。下面说第二组情况
第二种情况:主从切换(我这里使用MHA切换主从)

             Master_Server_Id: 20
                  Master_UUID: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab
             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: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1
            Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1
                Auto_Position: 1

可以看到在切换以后主库的server-id是20。这里的意思是接收到主库8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1,并且已经执行这个事务,那么这个事务其实就是之前在从库写入的那条数据。对于2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3这个是之前作为主库执行。如果此时在主库再插入1条数据,那么又会变化如下:

  Retrieved_Gtid_Set: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-2
            Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-2

下面说说文章开头提到的gtid不连续的问题,类似2a09ee6e-645d-11e7-a96c-000c2953a1cb:37-45,这个是由于binlog被清理以后导致的,我们可以测试一下。然后查看gtid_purged变量。
binlog不可能永远驻留在服务上,需要定期进行清理(通过expire_logs_days可以控制定期清理间隔),否则迟早它会把磁盘用尽。gtid_purged用于记录已经被清除了的binlog事务集合,它是gtid_executed的子集。只有gtid_executed为空时才能手动设置该变量,此时会同时更新gtid_executed为和gtid_purged相同的值。gtid_executed为空意味着要么之前没有启动过基于GTID的复制,要么执行过RESET MASTER。执行RESET MASTER时同样也会把gtid_purged置空,即始终保持gtid_purged是gtid_executed的子集。

从库:

[root@localhost][db1]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3530 |
+------------------+-----------+
1 row in set (0.00 sec)

[root@localhost][db1]> flush logs;
Query OK, 0 rows affected (0.05 sec)

[root@localhost][db1]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3577 |
| mysql-bin.000002 |       234 |
+------------------+-----------+
2 rows in set (0.00 sec)

[root@localhost][db1]> PURGE BINARY LOGS TO 'mysql-bin.000002';
Query OK, 0 rows affected (0.01 sec)

[root@localhost][db1]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       234 |
+------------------+-----------+
1 row in set (0.00 sec)

然后只要从库有重新启动,才会读取。MySQL服务器启动时,通过读binlog文件,初始化gtid_executed和gtid_purged,使它们的值能和上次MySQL运行时一致。

gtid_executed被设置为最新的binlog文件中Previous_gtids_log_event和所有Gtid_log_event的并集。
gtid_purged为最老的binlog文件中Previous_gtids_log_event。

没启动前:

 Retrieved_Gtid_Set: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-9
            Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-9

重启以后并且插入数据:

            Master_Server_Id: 20
                  Master_UUID: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab
             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: 8ce853fc-6f8a-11e7-8940-000c29e3f5ab:10
            Executed_Gtid_Set: 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-10
                Auto_Position: 1
[root@localhost][(none)]> show variables like 'gtid_purged'; 
+---------------+------------------------------------------------------------------------------------+
| Variable_name | Value                                                                              |
+---------------+------------------------------------------------------------------------------------+
| gtid_purged   | 2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,
8ce853fc-6f8a-11e7-8940-000c29e3f5ab:1-9 |
+---------------+------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

到这里相信聪明的你一定看懂了。最后顺道说说gtid跳过复制错误的方法,对于跳过一个错误,找到无法执行事务的编号,比如是2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-10,那么操作如下:

stop slave;
set gtid_next='2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-10';
begin;
commit;
set gtid_next='AUTOMATIC';
start slave;

上面方法只能跳过一个事务,那么对于一批如何跳过?在主库执行show master status,看主库执行到了哪里,比如:2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33,那么操作如下:

stop slave;
reset master;
set global gtid_purged='2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-33';
start slave;

 

 

 

posted @ 2017-08-23 16:05 yayun 阅读(...) 评论(...) 编辑 收藏