1.部署
GTID全局事务标识 mysql 5.6加入
1.1准备
配置文件
## gtid repl config need
server_id=52
#开启gtid
gtid_mode=on#开启基于gtid复制
enforce_gtid_consistency=on
log-bin=mysql52-bin#slave端日志
log-slave-updates=1binlog_format=row#slave启动时不会自动开启复制
skip-slave-start=1
#choice
expire_logs_days =20
重启数据库
(5.7.6以上可以动态而不重启)
权限
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'replrepl'@'10.27.%' iDENTIFIED BY 'gtidpwd' ;
1.2启动复制
1.2.1新库所有binglog全方式
从库上
change master to master_host='10.27.247.52',master_port=3306,master_user='replrepl',master_password='gtidpwd',master_auto_position=1;
start slave
1.2.2老库binglog不齐全 方式
#TODO
获取GTID
从库设置跳过set @@global.gtid_purged='uuid:interval[-interval]'
主库状态检查
mysql>show master status\G*************************** 1. row ***************************File: mysql52-bin.000001Position:467Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: b149ed20-eb22-11e8-9d63-005056bb87dc:1
1 row in set (0.00 sec)
从库状态检查
mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State:
Master_Host:10.27.247.52Master_User: replrepl
Master_Port:3306Connect_Retry:60Master_Log_File:
Read_Master_Log_Pos:4Relay_Log_File: tqhtest-53-relay-bin.000001Relay_Log_Pos:4Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
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: 0
Relay_Log_Space:154Until_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: NULL
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: 0
Master_UUID:
Master_Info_File:/var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:86400Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 0189c282-eb22-11e8-ab4b-005056bbe192:1Auto_Position:1Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:1 row in set (0.00sec)
mysql> start slave;
与非GTID复制比较
主库多了Executed_Gtid_Set: b149ed20-eb22-11e8-9d63-005056bb87dc:1
基于gtid的replication
基本replication
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
...
Retrieved_Gtid_Set: b149ed20-eb22-11e8-9d63-005056bb87dc:1
Executed_Gtid_Set: 0189c282-eb22-11e8-ab4b-005056bbe192:1,
b149ed20-eb22-11e8-9d63-005056bb87dc:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
...
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
主库uuid
从库uuid
2 skip
2.1信息
从库信息，由于无法顺利replication，exec状态落后于retiried状态
主库信息
2.2 传统
使用传统方式报错
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>
2.3gitd方式skip
确认数据不同步原因后，进行skip操作
接受到的主库事务号为13，已exec的事务号是11，故跳过12先
mysql>stop slave;
Query OK, 0 rows affected (0.00sec)
mysql> set GTID_NEXT='b149ed20-eb22-11e8-9d63-005056bb87dc:12';
Query OK, 0 rows affected (0.00sec)
mysql>begin;commit;
Query OK, 0 rows affected (0.00sec)
Query OK, 0 rows affected (0.00sec)
mysql> set GTID_NEXT='AUTOMATIC';
Query OK, 0 rows affected (0.00sec)
mysql>start slave;
Query OK, 0 rows affected (0.00sec)
mysql>
检查slavestatus已经正常。
后续，确认原因，检查一致性。
2利用GTIDreplication
2.1快速切换master
stop slave
change master to master_host='10.27.247.52',master_port=3306,master_user='replrepl',master_password='gtidpwd',master_auto_position=1;
start slave
由于不需要指定logfilename与pos，可以快速切换，快速变更复制拓扑。基于此特性，可以做灵活的架构。
3 原始replication切换为基于gtid的replication
5.7.6之前版本需要停服务
之后版本可以动态调整。
