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;

阅读 5.2k

阿拉丁
Mysql DBA 及DBA 运维自动化方面的技术分享

开源技术管理,Mysql,redis,mongodb,DBA自动化平台。。

48 声望
16 粉丝
0 条评论
你知道吗?

开源技术管理,Mysql,redis,mongodb,DBA自动化平台。。

48 声望
16 粉丝
宣传栏