MariaDB上传统复制升级为GTID复制

2018-02-27 10:56:28来源:oschina作者:mia0x75人点击

分享

在从库查看复制状态,注意复制延迟情况,没有延迟最好,有也没有关系。


SHOW SLAVE STATUS/G
- Relay_Master_Log_File: mysql-bin.000027
- Exec_Master_Log_Pos: 310094849
- Master_Log_File: mysql-bin.000027
- Read_Master_Log_Pos: 310094849

1. 在从库执行命令,停止当前复制:


STOP SLAVE;

2. 查看并记录中继日志执行的位置信息:


SHOW SLAVE STATUS/G
- Relay_Master_Log_File: mysql-bin.000027
- Exec_Master_Log_Pos: 310094849

3. 在主库查看从库执行位置对应的GTID位置


SELECT BINLOG_GTID_POS('mysql-bin.000027', 300050472);
+------------------------------------------------+
| BINLOG_GTID_POS('mysql-bin.000027', 300050472) |
+------------------------------------------------+
| 192-1681233-35567846|
+------------------------------------------------+

4. 回到从库设置GTID的开始位置,即第三步中从主库查询到的位置信息


SET GLOBAL gtid_slave_pos = '192-1681233-35567846';

5. 重新设置主库信息


CHANGE MASTER TO MASTER_HOST='192.168.1.233', # 主机
MASTER_PORT=3306, # 端口
MASTER_USER='replicator',# 用户
MASTER_PASSWORD='replpass',# 密码
MASTER_USE_GTID=slave_pos; # 位置

6. 启动复制


START SLAVE;

7. 查看复制状态


SHOW SLAVE STATUS/G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.233
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000027
Read_Master_Log_Pos: 310094849
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 10045104
Relay_Master_Log_File: mysql-bin.000027
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: 310094849
Relay_Log_Space: 10045407
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: 1681233
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 192-1681233-35580793
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
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

可以看到Using_Gtid有值,Gtid_IO_Pos也在不停变化。至此,传统复制已经更改成GTID复制。

微信扫一扫

第七城市微信公众平台