mysql gtid测试

首先测试一下gtid模式与pos复制模式是否能共存，搭建一主两从的模式，在其中一个从库配置成gtid的模式后，另外的一个从库启动后报错：

        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1593
                Last_IO_Error: The slave IO thread stops because the master has @@GLOBAL.GTID_MODE ON and this server has @@GLOBAL.GTID_MODE OFF

提示这个从库上没有设置成gtid模式，配置后在启动,此时是正常的

           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: 1
                  Master_UUID: 00021690-1111-1111-1111-111111111111
             Master_Info_File: /home/bjbaixiaoyu/sandboxes/rsandbox_mysql-5_6_25/node2/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 00021690-1111-1111-1111-111111111111:1-8
            Executed_Gtid_Set: 00021690-1111-1111-1111-111111111111:1-8
                Auto_Position: 0
1 row in set (0.00 sec)

如果在这种情况下基于pos的从库出现了复制的问题，比如主键冲突的问题，那么就不能用之前的skip跳跃了，会出现下面的提示

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> set global sql_slave_skip_counter=1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

需要指定空事务跳过，如下：

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set gtid_next='00021690-1111-1111-1111-111111111111:6';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '00021690-1111-1111-1111-111111111111:6'.
mysql> stop slave;
ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '00021690-1111-1111-1111-111111111111:6'.
mysql> set gtid_next='automatic';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

下面在测试下基于gtid的复制从库，如果出现了master 二进制日志被purge的情况的处理:
slave 停止复制，master上的操作

mysql> select * from baixyu;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | b    |
|  3 | dd   |
|  4 | ee   |
|  5 | ff   |
+----+------+
5 rows in set (0.00 sec)

mysql> insert into baixyu values(6,'qq');
Query OK, 1 row affected (0.01 sec)

mysql> insert into baixyu values(7,'aa');
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3056 |
| mysql-bin.000002 |      2605 |
| mysql-bin.000003 |       191 |
+------------------+-----------+
3 rows in set (0.00 sec)

mysql> purge binary logs to 'mysql-bin.000003';
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |       191 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> show global variables like 'GTID_EXEC%';
+---------------+-------------------------------------------+
| Variable_name | Value                                     |
+---------------+-------------------------------------------+
| gtid_executed | 00021690-1111-1111-1111-111111111111:1-10 |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)

slave 开启同步

        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

下面的处理方式就是会丢失数据的

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> set global gtid_executed='00021690-1111-1111-1111-111111111111:1-10';
ERROR 1238 (HY000): Variable 'gtid_executed' is a read only variable
mysql> show variables like 'GTID_EX%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global gtid_purged='00021690-1111-1111-1111-111111111111:1-10';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

直接把丢失的事务跳过，在同步，这样数据就有问题了，不丢数据的处理方式就是在master上备份在导入从库，正常开启同步。