當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql主从切换gtid不一致_GTID 复制、主从不一致跳过操作、快速切换master

發布時間：2025/3/20 数据库 20 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql主从切换gtid不一致_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=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.3?gitd方式skip

確認數據不同步原因后，進行skip操作

接受到的主庫事務號為13，已exec的事務號是11，故跳過12先

mysql>stop slave;