測試環境：MYSQL 5.7.22
1.在主庫與從庫上各自執行enforce_gtid_consistency=warn( 檢查 )
1.在主庫與從庫上各自執行enforce_gtid_consistency=warn( 檢查 )
mysql> set global enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)
enforce_gtid_consistency：此參數是保證GTID安全的參數
注意：執行完這條語句後，如果出現GTID不兼容的語句用法，在錯誤日誌會記錄相關信息，那麼需要調整應該程序避免不兼容的寫法，直到完全沒有產生不兼容的語句，可以通過應該程序去排查所有的sql，也可以設置後觀察錯誤日誌一段時間，這一步非常重要。
接著查看error.log有無警告信息
主庫:
2019-04-10T15:01:57.927932+08:00 29883 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN
從庫:
2019-04-10T07:02:03.502454Z 15 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN.
※確保在error log中不會出現警告信息。如果有，需要先修復，才能往後繼續執行。
2.在主庫與從庫上各自執行enforce_gtid_consistency=on(
開啟
)
mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)
3.在主庫與從庫上各自執行先OFF再ON
(
先
) global gtid_mode=off_permissive 和
( 後)
global gtid_mode=on_permissive
※錯誤步驟-如果先執行on_permissive會報錯
mysql> set global gtid_mode=on_permissive;
ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.
※正確步驟
mysql> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.01 sec)
mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.00 sec)
參數說明:
OFF：不產生GTID,Slave只接收不帶GTID的事務
OFF_PERMISSIVE：不產生GTID,Slave接收不帶GTID的事務也接收帶GTID的事務
ON_PERMISSIVE ：產生GTID,Slave接收不帶GTID的事務也接收帶GTID的事務
ON ：產生GTID,Slave只接收帶GTID的事務
4.確認從庫ongoing_anonymous_transaction_count參數是否為0
此參數表示還有沒有等待的事務未執行,如果為0表示沒有等待
mysql> show global status like 'Ongoing_anonymous_%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0 |
+-------------------------------------+-------+
1 row in set (0.02 sec)
5.在主庫從庫上各自執行開啟gtid功能
mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.00 sec)
6.檢查GTID的設置狀態是否都開啟
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.00 sec)
※主從的my.cnf需填加此參數，避免往後有重啟狀況時，參數無效
#GTID
gtid_mode = on
gtid_mode = on
enforce_gtid_consistency = on
7.把原有的slave停止，然後再執行change master to master_auto_position=1
原有的slave狀態需先停止
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000022
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000011
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: 154
Relay_Log_Space: 621
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: 1
Master_UUID: a57afe45-51e5-11e9-8c72-000c292c01ba
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
停止原本slave後，再執行change master to master_auto_position
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_auto_position=1;
Query OK, 0 rows affected (0.00 sec)
設定好後再START SLAVE，接著show slave satatus \G
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
※show slave status \G 我自己測試不是馬上就YES.YES
大約執行三次才從Connecting.Yes變YES.YES
而且Executed_Gtid_Set:是空值(之後插入數據就出現了)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000011
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: 154
Relay_Log_Space: 574
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: 1
Master_UUID: a57afe45-51e5-11e9-8c72-000c292c01ba
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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: （連線成功但是空值？？？）
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
8.繼續測試在主庫DB:ttt創立TABLE:bbb，看從庫是否有複制無誤
主庫：
mysql> create table bbb(id int,name varchar(10));
Query OK, 0 rows affected (0.04 sec)
從庫：
mysql>show tables;
+---------------+
| Tables_in_ttt |
+---------------+
| bbb |
+---------------+
1 row in set (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 332
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 545
Relay_Master_Log_File: mysql-bin.000011
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: 332
Relay_Log_Space: 752
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: 1
Master_UUID: a57afe45-51e5-11e9-8c72-000c292c01ba
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: a57afe45-51e5-11e9-8c72-000c292c01ba:1
Executed_Gtid_Set: a57afe45-51e5-11e9-8c72-000c292c01ba:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Retrieved_Gtid_Set: a57afe45-51e5-11e9-8c72-000c292c01ba:1
Executed_Gtid_Set: a57afe45-51e5-11e9-8c72-000c292c01ba:1
原本沒數值現在有出現數值了，且主庫更新數據:後數值會一直增加
測試結果成功!
記錄一次錯誤記錄
狀況：MASTER當機故障重新啟動！
再SLAVE 上執行show slave statu \G 查看狀態
原來是忘記在MY.CNF加上該參數，因為原本是不關機狀態下建立主從，所以重開機變量消失，所以在主庫從庫上的MY.CNF加入兩個參數
接著重啟MASTER，然後再次查看SLAVE狀態就連線正常了
記錄一次錯誤記錄
狀況：MASTER當機故障重新啟動！
再SLAVE 上執行show slave statu \G 查看狀態
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 180266467
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 180266680
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: No
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: 180266467
Relay_Log_Space: 180266974
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: 1593
Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a57afe45-51e5-11e9-8c72-000c292c01ba
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190412 15:58:21
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: a57afe45-51e5-11e9-8c72-000c292c01ba:1-60
Executed_Gtid_Set: a57afe45-51e5-11e9-8c72-000c292c01ba:1-60
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 180266467
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 180266680
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: No
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: 180266467
Relay_Log_Space: 180266974
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: 1593
Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a57afe45-51e5-11e9-8c72-000c292c01ba
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190412 15:58:21
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: a57afe45-51e5-11e9-8c72-000c292c01ba:1-60
Executed_Gtid_Set: a57afe45-51e5-11e9-8c72-000c292c01ba:1-60
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
#在配置文件中添加：
gtid_mode = on
enforce_gtid_consistency = 1
enforce_gtid_consistency = 1
接著重啟MASTER，然後再次查看SLAVE狀態就連線正常了
沒有留言:
張貼留言