乘风原创程序

    当前位置:首页>>数据库>>Mysql>>MySQL5.6 GTID模式下同步复制报错不能跳过怎么办
  • MySQL5.6 GTID模式下同步复制报错不能跳过怎么办
  • 2020/8/4 10:57:09
  • 数据库版本:

    mysql> select version();

    +------------+
    | version() |
    +------------+
    | 5.6.10-log |
    +------------+
    1 row in set (0.02 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: 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

    提示主键冲突,由于是测试机,于是我直接跳过,

    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

    提示:由于运行在GTID模式,所以不支持sql_slave_skip_counter语法,如果你想跳过,就必须把事务ID设置为空值。
    看来只能用这个方法了。

    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.
    郁闷,直接设置还不行。

    查看了手册,需要执行reset master才可以(注:在从上执行啊,千万别在主上)。

    MySQL5.6 GTID模式下同步复制报错不能跳过怎么办

    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)

    执行reset slave的目的是清空master.info和relay-log.info,以便后面重新change master to主从复制。
    还记得刚才的gtid_purged那个点吗,只需重新设置下一个点即可。

    下面是步骤:

    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
    ### 看,这里的报错信息已经不一样了,按照这种方法,重复执行,直到同步复制正常。

    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