Shulou(Shulou.com)06/01 Report--

Editor to share with you this time is the MySQL5.6 GTID mode of synchronous replication errors can not be skipped how to do, the article is rich in content, interested friends can learn about it, I hope you can get something after reading this article.

Database version:

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.6.10-log |

+-+

1 row in set (0.02 sec)

Synchronous replication information:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.8.25

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 5036

Relay_Log_File: M2-relay-bin.000008

Relay_Log_Pos: 408

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1062

Last_Error: Could not execute Write_rows event on table test.t; Duplicate entry '12'

For key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007

End_log_pos 2267

Skip_Counter: 0

Exec_Master_Log_Pos: 2045

Relay_Log_Space: 3810

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

Last_SQL_Error: Could not execute Write_rows event on table test.t; Duplicate entry '12'

For key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007

End_log_pos 2267

Replicate_Ignore_Server_Ids:

Master_Server_Id: 25

Master_UUID: cf716fda-74e2-11e2-b7b7-000c290a6b8f

Master_Info_File: / usr/local/mysql/data2/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: 130313 07:24:43

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:141-151,

Executed_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-140

Auto_Position: 1

1 row in set (0.02 sec)

ERROR:

No query specified

Prompt primary key conflict, because it is a test machine, so I directly skip

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 GTID_MODE = ON.

Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

Tip: sql_slave_skip_counter syntax is not supported because it runs in GTID mode. If you want to skip it, you must set the transaction ID to null.

It seems that this is the only way to use it.

Mysql > show global variables like'% GTID%'

+-+

| | Variable_name | Value |

+-+

| | enforce_gtid_consistency | ON |

| | gtid_executed | cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-140 | |

| | gtid_mode | ON |

| | gtid_owned |

| | gtid_purged | cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-140 | |

+-+

5 rows in set (0.04 sec)

Mysql > set global gtid_executed=''

ERROR 1238 (HY000): Variable 'gtid_executed' is a read only variable

Mysql >

Mysql > set global gtid_purged=''

ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty.

Depressed, it is not enough to set it directly.

After checking the manual, you need to execute reset master.

Mysql > reset master

Query OK, 0 rows affected (0.16 sec)

Mysql > reset slave

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

Mysql > stop slave

Query OK, 0 rows affected (0.08 sec)

Mysql > reset slave

Query OK, 0 rows affected (0.16 sec)

The purpose of performing reset slave is to clear master.info and relay-log.info so that the master-slave replication can be re-change master to later.

Remember the gtid_purged point just now? just reset the next point.

Here are the steps:

Mysql > show global variables like'% GTID%'

+-+ +

| | Variable_name | Value |

+-+ +

| | enforce_gtid_consistency | ON |

| | gtid_executed |

| | gtid_mode | ON |

| | gtid_owned |

| | gtid_purged |

+-+ +

5 rows in set (0.06 sec)

Mysql > set global gtid_purged='cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-141'

Query OK, 0 rows affected (0.16 sec)

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.8.25',MASTER_USER='repl',MASTER_PASSWORD='repl'

, MASTER_AUTO_POSITION = 1

Query OK, 0 rows affected, 2 warnings (0.32 sec)

Mysql > start slave

Query OK, 0 rows affected (0.13 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.8.25

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 5036

Relay_Log_File: M2-relay-bin.000008

Relay_Log_Pos: 408

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1050

Last_Error: Error'Table 't0' already exists' on query.

Default database: 'test'. Query: 'create table t0 like t'

Skip_Counter: 0

Exec_Master_Log_Pos: 2298

Relay_Log_Space: 3557

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

Last_SQL_Error: Error'Table 't0' already exists' on query.

Default database: 'test'. Query: 'create table t0 like t'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 25

Master_UUID: cf716fda-74e2-11e2-b7b7-000c290a6b8f

Master_Info_File: / usr/local/mysql/data2/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: 130313 07:50:42

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:142-151,

Executed_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-141,

Auto_Position: 1

1 row in set (0.02 sec)

ERROR:

No query specified

# look, the error message here is different. According to this method, repeat until the synchronous replication is normal.

Mysql > stop slave

Query OK, 0 rows affected (0.07 sec)

Mysql > reset master

Query OK, 0 rows affected (0.17 sec)

Mysql > reset slave

Query OK, 0 rows affected (0.16 sec)

Mysql > set global gtid_purged='cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-151'

Query OK, 0 rows affected (0.13 sec)

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.8.25',MASTER_USER='repl',MASTER_PASSWORD='repl'

, MASTER_AUTO_POSITION = 1

Query OK, 0 rows affected, 2 warnings (0.33 sec)

Mysql > start slave

Query OK, 0 rows affected (0.11 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.8.25

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 5036

Relay_Log_File: M2-relay-bin.000008

Relay_Log_Pos: 408

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

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

Relay_Log_Space: 819

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

Master_UUID: cf716fda-74e2-11e2-b7b7-000c290a6b8f

Master_Info_File: / usr/local/mysql/data2/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:

Executed_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-151,

Auto_Position: 1

1 row in set (0.01 sec)

ERROR:

No query specified

After reading this article about how to skip the error report of synchronous replication in MySQL5.6 GTID mode, if you think the content of the article is good, you can share it with more people.

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.