GTID 复制、主从不一致跳过操作、快速切换master
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=1 binlog_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.000001 Position: 467 Binlog_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.52 Master_User: replrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: tqhtest-53-relay-bin.000001 Relay_Log_Pos: 4 Relay_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: 154 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: 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: 86400 Master_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:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) 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 |
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it |
主库uuid
从库uuid
2 skip
2.1 信息
从库信息， 由于无法顺利replication， exec状态落后于retiried状态
主库信息
2.2 传统
使用传统方式报错
|
mysql> set global sql_slave_skip_counter=1; |
2.3 gitd方式skip
确认数据不同步原因后，进行skip操作
接受到的主库事务号为13，已exec的事务号是11，故跳过12先
mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> set GTID_NEXT='b149ed20-eb22-11e8-9d63-005056bb87dc:12'; 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> set GTID_NEXT='AUTOMATIC'; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql>
检查slave status已经正常。
后续，确认原因，检查一致性。
2 利用GTID replication
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之前版本需要停服务
之后版本可以动态调整。
