Mariadb 10 GTID 复制 测试

同一机器上三个实例，3307为maser，新造两个mariadb实例，端口分别为3309 和3310,开始搭建复制关系，版本为mariadb 10.0.10

3307 导出： mysqldump --master-data=2
导入：
mysql -u -p --socket=/tmp/mysql3309.sock </apps/bak_3307.sql
mysql -u -p --socket=/tmp/mysql3310.sock </apps/bak_3307.sql

在做主从复制关系前，

dba@192.168.50.111(test_3309) : (none) 05:20:31>select @@global.gtid_current_pos;
@@global.gtid_current_pos
0-501113309-31329

1 row in set (0.02 sec)

dba@192.168.50.111(test_3309) : (none) 05:21:10>select @@global.gtid_current_pos;
@@global.gtid_current_pos
0-501113309-32311

1 row in set (0.00 sec)

dba@192.168.50.111(test_3309) : (none) 05:21:33>SELECT @@GLOBAL.gtid_slave_pos;
@@GLOBAL.gtid_slave_pos

1 row in set (0.00 sec)

可以看出@@global.gtid_current_pos ，由于数据不断写入，值是变的，而@@GLOBAL.gtid_slave_pos 由于复制关系还没有搭建，目前是空值.

mysqldump备份文件里面的change master备注：-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000052', MASTER_LOG_POS=394894368;

3309 端口采用原始的复制模式:

change master to master_host='192.168.50.111', MASTER_PORT=3307,master_user='', master_password='', master_log_file='mysql-bin.000052', master_log_pos=394894368;

start slave

dba@192.168.50.111(test_3309) : (none) 07:22:21>select @@global.gtid_current_pos;
@@global.gtid_current_pos
0-501113307-7227948

1 row in set (0.00 sec)

dba@192.168.50.111(test_3309) : (none) 07:22:48>SELECT @@GLOBAL.gtid_slave_pos;
@@GLOBAL.gtid_slave_pos
0-501113307-7227956

+-------------------------+ 可以看到 @@GLOBAL.gtid_slave_pos 已经有值了,不管是否采用传统或者gtid模式来复制.

3309 由传统模式改为 gtid模式复制：

STOP SLAVE;
CHANGE MASTER TO master_host="192.168.50.111", master_port=3307, master_user='', master_password='', master_use_gtid=current_pos;
START SLAVE;

show slave status G 增加内容:

Master_Server_Id: 501113307
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Current_Pos
Gtid_IO_Pos: 0-501113307-7227969

3310 端口，直接采用GTID模式的复制，方法如下：

在主库上（3307）执行 ： SELECT BINLOG_GTID_POS("mysql-bin.000052", 394894368);
BINLOG_GTID_POS("mysql-bin.000052", 394894368)
0-501113307-7227451	-- 得到GTID位置

从库上执行：
SET GLOBAL gtid_slave_pos = "0-501113307-7227451"; -- BINLOG_GTID_POS 函数得到的位置
CHANGE MASTER TO master_host="192.168.50.111", master_port=3307, master_user='', master_password='', master_use_gtid=slave_pos;
START SLAVE;

show slave status G
Master_Server_Id: 501113307
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-501113307-7228013

切换master实验，把3310端口的slave的master从 3307 端口，切换到3309端口

STOP SLAVE;
CHANGE MASTER TO master_host="192.168.50.111", master_port=3309
START SLAVE;

show slave status G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.50.111
Master_User: rep
Master_Port: 3309
Master_Server_Id: 501113309
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-501113307-7280903 -- 虽然切换到3309 ，gtid的server-id，还是3307的，501113307

再切回来，把对应的master指向3307端口

STOP SLAVE;
CHANGE MASTER TO master_host="192.168.50.111", master_port=3307
START SLAVE;

dba@192.168.50.111(test_3310) : (none) 03:29:42>show slave status G
1. row **

           Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.50.111
              Master_User: rep
              Master_Port: 3307
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000052       --变回3307 端口的了
      Read_Master_Log_Pos: 425706839
        Master_Server_Id:  501113307
           Master_SSL_Crl: 
       Master_SSL_Crlpath: 
               Using_Gtid: Slave_Pos
              Gtid_IO_Pos: 0-501113307-7281435     GTID的前两段，不变,也就是全局唯一
              

三个全局变量：

select @@global.gtid_slave_pos, @@global.gtid_binlog_pos,@@global.gtid_current_pos;