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

This article mainly introduces the ways of mysql GTID replication to skip replication errors, the article is very detailed, has a certain reference value, interested friends must read it!

1. The slave database executed the transaction, and the master database caused a conflict when executing the statement:

Solution 1: skip errors (a few misrecommendations)

(1) stop the slave process

(2) set the transaction number, which is obtained from Executed_Gtid_Set transaction number + 1, that is, skip the next transaction completed by the current execution, and set gtid_next in session, that is, skip the GTID.

(3) execute empty things

(4) restore automatic thing number

(5) start the slave process

Mysql > STOP SLAVE

Mysql > SET @ @ SESSION.GTID_NEXT= '8f9e146f-0a18-11e7-810aMur0050568833c8pur4'

Mysql > BEGIN; COMMIT

Mysql > SET SESSION GTID_NEXT = AUTOMATIC

Mysql > START SLAVE

Solution 2: reset the master method to skip the error (not recommended, because you need to perform reset master; if other nodes record the location information of the instance, you need to find the site when switching)

Mysql > STOP SLAVE

Mysql > RESET MASTER

Mysql > SET @ @ GLOBAL.GTID_PURGED = '8f9e146f-0a18-11e7-810a-0050568833c8:1-4'

Mysql > START SLAVE

The intention of the above commands is to ignore the GTID transaction 8f9e146f-0a18-11e7-810a-0050568833c8:1-4, and then skip the above error next time, starting with the GTID of 5.

Solution 3: use the pt-slave-restart tool to ignore specific errors (it can be used when there are many conflicts, it is recommended to keep relaylog to check transactions)

The role of the pt-slave-restart tool is to monitor for certain replication errors, then ignore them, and start the SLAVE process (Watch and restart MySQL replication after errors) again.

A probe into the principle of pt-slave-restart tool

1. Check version information and compatibility

Set autocommit=1

SHOW VARIABLES LIKE 'wait\ _ timeout'

SET SESSION wait_timeout=10000

SELECT @ @ SQL_MODE

SET @ @ SQL_QUOTE_SHOW_CREATE = 1 Universe 40101, @ @ SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/

SELECT @ @ SERVER_ID

SHOW SLAVE STATUS

SELECT CONCAT (@ @ hostname, @ @ port)

SHOW VARIABLES LIKE 'version%'

SHOW ENGINES

SHOW VARIABLES LIKE 'innodb_version'

SELECT @ @ GLOBAL.gtid_mode (whether to turn on GTID to determine how to skip transactions)

SELECT @ @ GLOBAL.slave_parallel_workers AS threads (parallel replication is not supported)

2. Check for synchronization errors

SHOW SLAVE STATUS

If you use-- error-numbers, check the Last_SQL_Errno: error number to determine whether there is a match; if the match continues, no action will be done if the match continues

If you use-- error-text, check Last_SQL_Error: error content to determine whether to match; if the match continues, no action will be taken if the match continues

3. Skip transactions

SET GTID_NEXT='026b75e5-d99f-11e7-a343-000c29dc5f5cpur2023'

Set autocommit=0

Commit

Set autocommit=1

SET GTID_NEXT='AUTOMATIC'

START SLAVE

4. Continue to judge the status, and if there are any errors, continue to take 2p3pm 4 steps.

SHOW SLAVE STATUS

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.137.101

Master_User: repluser

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 3435

Relay_Log_File: mysql-relay-bin.000016

Relay_Log_Pos: 1026

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Wild_Ignore_Table: passport.cooke,passport.user_token,sysbench_test.sbtest1,sysbench_test.sbtest_1

Last_Errno: 1062

Last_Error: Error 'Duplicate entry' 9' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into backup_test (id,backup_que) values (9 recordings 1111')

Skip_Counter: 0

Exec_Master_Log_Pos: 2856

Relay_Log_Space: 2362

Last_SQL_Errno: 1062

Last_SQL_Error: Error 'Duplicate entry' 9' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into backup_test (id,backup_que) values (9 recordings 1111')

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1013307

Master_UUID: 026b75e5-d99f-11e7-a343-000c29dc5f5c

Master_Info_File: mysql.slave_master_info

Last_SQL_Error_Timestamp: 180504 11:58:07

Retrieved_Gtid_Set: 026b75e5-d99f-11e7-a343-000c29dc5f5c:1004-2018

Executed_Gtid_Set: 026b75e5-d99f-11e7-a343-000c29dc5f5c:1-2016

5f5c73b5-2768-11e8-9e7e-000c298bd943:1-2997

68be50a9-3ba1-11e8-a25d-000c298bd943:1-4

Auto_Position: 1

[root@vm102 yum.repos.d] # pt-slave-restart-- user=mg-h227.0.0.1-P3307-p123qwe-- error-numbers=1062

2018-05-04T11:47:31 Prune 3307 mysql-relay-bin.000016 hype 127.0.0.1.

2018-05-04T11:47:31 Prune 3307 mysql-relay-bin.000016 hype 127.0.0.1.

[root@vm102 yum.repos.d] # pt-slave-restart-user=mg-h227.0.0.1-P3307-p123qwe-error-text= "backup_test"

2018-05-04T12:00:23 Prune 3307 mysql-relay-bin.000016 1026 1062

2018-05-04T12:00:24 Prune 3307 mysql-relay-bin.000016 1315 1062

Parameter explanation:

-- slave-password=s Sets the password to be used to connect to the slaves

-- slave-user=s Sets the user to be used to connect to the slaves

-- sleep=i Initial sleep seconds between checking the slave (default 1)

-- socket=s-S Socket file to use for connection=

-- password=s-p Password to use when connecting

Pt-slave-resetart-S./mysql.sock-error-numbers=1032

-- error-numbers=h Only restart this comma-separated list of errors

-- host=s-h Connect to host

-- user=s-u User for login if not current user

The above is all the contents of this article entitled "what are the ways to skip replication errors in mysql GTID replication?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.