Shulou(Shulou.com)05/31 Report--

How to fix MySQL master-slave GTID replication, I believe many inexperienced people are helpless about this, this article summarizes the causes and solutions of the problem, through this article I hope you can solve this problem.

GTID is a new feature in 5.6 that reduces the workload of DBA operations. In the previous one master two slave architecture, when the master library M1 fails, we need to select a slave library S1 as the new master library, but S2 needs to change master to the new master library. In this case, how do we get master_log_file and master_log_pos? The MHA architecture solves this problem for us without GTID. With GTID, we just need to set MASTER_AUTO_POSITION = 1 under change master on S2. This article describes how to resolve errors encountered under GTID replication.

scene description

When we use GTID replication, we cannot skip transactions like traditional replication, we can only register an empty transaction to fool MySQL. The specific operation steps are as follows:

View GTID execution for current backup

Retrieved_Gtid_Set: dd2a02a3-f0be-11e5-af62-0050563a97cc:70261-71462

Executed_Gtid_Set: dd2a02a3-f0be-11e5-af62-0050563a97cc:1-71459,

e28420fb-f0be-11e5-af62-0050562edd64:1-81209

Auto_Position: 0

Retrieved_Gtid_Set represents the set of GTIDs that have been received

Executed_Gtid_Set The set of GTIDs that the code has executed

For the above GTID implementation we can see:

Retrieved_Gtid_Set: dd2a02a3-f0be-11e5-af62-0050563a97cc:70261-71462

Executed_Gtid_Set: dd2a02a3-f0be-11e5-af62-0050563a97cc:1-71459

Received 71462 executed to 71459 on the error report.

manual registration transaction

[root@shadow1:/root 5.6.28-log_Instance1 root@localhost:test 12:59:41]>stop slave;

Query OK, 0 rows affected (0.00 sec)

[root@shadow1:/root 5.6.28-log_Instance1 root@localhost:test 13:04:57]>set gtid_next='dd2a02a3-f0be-11e5-af62-0050563a97cc:71460';

Query OK, 0 rows affected (0.00 sec)

[root@shadow1:/root 5.6.28-log_Instance1 root@localhost:test 13:05:23]>begin;

Query OK, 0 rows affected (0.00 sec)

[root@shadow1:/root 5.6.28-log_Instance1 root@localhost:test 13:05:29]>commit;

Query OK, 0 rows affected (0.02 sec)

[root@shadow1:/root 5.6.28-log_Instance1 root@localhost:test 13:05:31]>SET GTID_NEXT='AUTOMATIC';

Query OK, 0 rows affected (0.00 sec)

[root@shadow1:/root 5.6.28-log_Instance1 root@localhost:test 13:05:36]>start slave;

Query OK, 0 rows affected (0.00 sec)

it should be noted here

set gtid_next='dd2a02a3-f0be-11e5-af62-0050563a97cc:71460'; this value is

Executed_Gtid_Set: dd2a02a3-f0be-11e5-af62-0050563a97cc:1-71459 71459 this GTID+1

And uuid must be Retrieved_Gtid_Set: dd2a 02a3-f0be-11e5-af62 -0050563a97cc:70261-71462 Received uuid displayed here

Check again show slave status\G has returned to normal

[root@shadow1:/root 5.6.28-log_Instance1 root@localhost:test 13:05:39]>show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.30.101

Master_User: repl

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 28275288

Relay_Log_File: mysql-relay-bin.000012

Relay_Log_Pos: 21794

Relay_Master_Log_File: mysql-bin.000003

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: 28275288

Relay_Log_Space: 302335

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: 101

Master_UUID: dd2a02a3-f0be-11e5-af62-0050563a97cc

Master_Info_File: mysql.slave_master_info

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

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: dd2a02a3-f0be-11e5-af62-0050563a97cc:70261-72005

Executed_Gtid_Set: dd2a02a3-f0be-11e5-af62-0050563a97cc:1-72005,

e28420fb-f0be-11e5-af62-0050562edd64:1-81209

Auto_Position: 0

After reading the above, do you know how to repair MySQL master-slave GTID copy? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.