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

Reason:

In the test environment, after the master library executes the DDL statement to increase the length of a field, the slave library reports that it cannot modify the length of this field, and the length of this field is already between the original length and the target length.

environment

5.7.19 GTID Dual Master Replication

Solution steps:

1. Try to manually modify the field length, restore it to the unmodified length, and restart the slave process. Result: Failure, same error, error number 1677

2. Try to manually modify the field length, synchronize to the modified length, skip this transaction:

mysql>stop slave;mysql>set gtid_next="d7c35015-9dd1-11e7-b70d-005056aa19c3:51629"; ##Note that since dual master GTID replication is enabled,show master status and Executed_gtid_set will have two GTID values, one of which is its own GTID and the other is the master GTID. ##When setting, just write the GTID of the main library into "". The GTID itself does not need to be specified, but if skipped by using the set gtid_purged method, it is necessary to specify the mysql>begin;commit; ##of two GTIDs, and insert an empty transaction to update the GTID. mysql>set gtid_next='automatic';##"After this variable has been set to UUID:NUMBER, and a transaction has been committed or rolled back, an explicit SET GTID_NEXT statement must again be issued before any other statement. "mysql>start slave;

Reasonably speaking, this should be normal, but it still reports the same error, and it is also very troublesome to operate like this again and again.

3. There are two ways to skip batch copy errors:

Use slave-skip-errors=123,456,789, but this parameter is not a dynamic parameter. It needs to be written into the configuration file and restarted, and it is not convenient to observe.

Percona pt-slave-restart tool

pt-slave-restart is a tool in the percona-toolkit specifically designed to handle replication errors

Principle: Depending on settings, skip specified error transactions from the master library

1. GTID replication is supported, but multithreaded replication is not supported. The tool cannot tell which thread is copying the problem.

2. Practical parameters other than host designation:

--always : Never stop slave threads, not even manually

--ask-pass : replaces the-p command to prevent password entry from being viewed by developers behind the screen

--error-numbers: Specify which errors to skip, available, separate

--error-text: Match according to error message Skip

--log: output to file

--recurse: execute at the master, monitor the slave

--runtime: how long after the tool executes: default seconds, m=minute,h=hours,d=days

--slave-user --slave-password: account password of slave library, used when running from master

--skip-count: the number of errors skipped at one time, bold can be set larger, do not specify the default 1

--master-uuid : Specify error to skip parent or superior transaction when cascading replication

--until-master : Stop at the specified master_log_pos,file location,

Format: "file:pos"

--until-relay : Same as above, but stops at relay_log

Installation:

centos#yum install https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm centos#yum search percon-tool centos#yum -y install yum install percona-toolkit

View Help Files:

perldoc /usr/bin/pt-slave-restart

Operational tools:

pt-slave-restart --user=root --password=123456 --socket=/data/mysql/3304/tmp/mysql3304.sock --error-numbers=1677

The output is as follows:

Not checking slave because relay log file or position has not changed (file relay-bin.000002 pos 205804892)2017-09-30T12:55:03 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 205805634 1677 Not checking slave because relay log file or position has not changed (file relay-bin.000002 pos 205805634)Not checking slave because relay log file or position has not changed (file relay-bin.000002 pos 205805634)Not checking slave because relay log file or position has not changed (file relay-bin.000002 pos 205805634)2017-09-30T12:55:03 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 205806382 1677 2017-09-30T12:55:04 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 205807125 1677 2017-09-30T12:55:04 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 205807873 1677 Not checking slave because relay log file or position has not changed (file relay-bin.000002 pos 205807873)2017-09-30T12:55:20 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 205997621 1677 2017-09-30T12:55:21 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 205998353 1677 2017-09-30T12:55:22 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 205999085 1677 2017-09-30T12:55:23 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 205999823 1677 2017-09-30T12:55:24 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 206000565 1677 2017-09-30T12:55:24 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 206001307 1677 2017-09-30T12:55:24 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 206002049 16772017-09-30T12:55:24 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 206002783 1677 2017-09-30T12:55:24 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 206003517 1677 2017-09-30T12:55:24 S=/data/mysql/3304/tmp/mysql3304.sock,p=..., u=root relay-bin.000002 206004253 1677

View thread status from library:

************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.200 Master_User: bt_repl Master_Port: 3304 Connect_Retry: 60 Master_Log_File: 3304binlog.000002 Read_Master_Log_Pos: 665635124 Relay_Log_File: relay-bin.000006 Relay_Log_Pos: 2080902 Relay_Master_Log_File: 3304binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 665635124 Relay_Log_Space: 2081454 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Seconds_Behind_Master: 0Master_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: 3304 Master_UUID: d7c35015-9dd1-11e7-b70d-005056aa19c3 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 more updates Master_Retry_Count: 86400 Master_Bind: Retrieved_Gtid_Set: d7c35015-9dd1-11e7-b70d-005056aa19c3:1-430778 Executed_Gtid_Set: 886f86e9-9dd6-11e7-ba30-005056aa6236:1-2,d7c35015-9dd1-11e7-b70d-005056aa19c3:1-430778 Auto_Position: 1 1 row in set (0.07 sec)

There are no mistakes anymore. OK

attention:

gtid_next is a session-level parameter, while gtid_purged is a global-level parameter.

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.