mysql replication 之 1594错误

最新推荐文章于 2024-07-17 13:54:10 发布

weixin_34378767

最新推荐文章于 2024-07-17 13:54:10 发布

阅读量94

点赞数

文章标签：开发工具数据库

原文链接：https://my.oschina.net/AnnaWu/blog/3006378

版权

为什么80%的码农都做不了架构师？>>>

Step 1:本周末因服务商初始化了mysql 从库服务器，导致mysql主从不同

说明：主从不同步信息如下


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 104.237.146.129
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master.000066
          Read_Master_Log_Pos: 924477125
               Relay_Log_File: mysqld-relay-bin.000183
                Relay_Log_Pos: 805630111
        Relay_Master_Log_File: mysql-master.000066
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 805629945
              Relay_Log_Space: 924482592
              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: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 129
                  Master_UUID: 47451c14-87e6-11e8-93b3-f23c91cd2190
             Master_Info_File: /data/mysql/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: 190126 13:57:17
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

Step 2.通过上面的信息，我们可以知道

1.Master_Log_File: mysql-master.000066 当前的slave已经读取了master的binlog文件为：mysql-master.000066
          
2.Read_Master_Log_Pos: 924477125   当前的slave读取的master binlog---mysql-master.000066的位置是924477125

3.Slave_SQL_Running: No    当前slave的sql线程停止，表示，master的变化，可以读取到slave，但是slave不能应用这些变换

4.Last_Errno: 1594    当前slave发生了1594错误

5.Last_Error  1594错误的具体信息在laster_error中显示

6.Relay_Master_Log_File: mysql-master.000066    当前slave应用的最新的master的binlog是：mysql-master.000066，这就意味着，如果我们想重新启用slave，同步的起点应该是从mysql-master.000066这个文件开始

7.Exec_Master_Log_Pos: 236752486    当前slave应用的最新的binlog的位置是：805629945，这就意味着，同步的起点文件的位置是：805629945

Step 3.解决办法

1.重新创建slave。
使用mysqldump命令，重新备份数据库。然后在slave中导入，然后使用change master to命令重建slave
注：在备份时，加入参数--single-transaction --master-data=2，减少对线上业务的影响，把当前备份的binglog的信息记录到备份中。

2.直接使用change master to 命令，根据Relay_Master_Log_File、Exec_Master_Log_Pos的值，重建slave.

#先stop slave
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

#重建slave
mysql> change master to master_log_file='mysql-master.000066',master_log_pos=805629945;
Query OK, 0 rows affected (0.05 sec)

#开启slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)



#查看同步情况，不能同步
show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 104.237.146.129
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master.000066
          Read_Master_Log_Pos: 929646723
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 286
        Relay_Master_Log_File: mysql-master.000066
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '25-112-MediaShakers-US-East-2019-01-26 12:23:46-086-6a-051ef24c0' for key 'PRIMARY'' on query. Default database: 'gadmobe_dw'. Query: 'INSERT INTO open_rtb_win(exchange_id, dsp_id, endpoint_id, request_id, advertiser_spend, gadmobe_cost, bid_price, bid_floor,advertiser_price,campaign_id,response_id,creative_id,country) VALUES
        (
          25,112,'MediaShakers-US-East','086-6a-051ef24c017f4866',0.46988000554858445,0.26188,0.2680000055485845,0.1,0.47600001109716894,'1167_21','Wi89d4Iu7-w_0','1167_29','USA'
        )'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 805629945
              Relay_Log_Space: 124017238
              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: Error 'Duplicate entry '25-112-MediaShakers-US-East-2019-01-26 12:23:46-086-6a-051ef24c0' for key 'PRIMARY'' on query. Default database: 'gadmobe_dw'. Query: 'INSERT INTO open_rtb_win(exchange_id, dsp_id, endpoint_id, request_id, advertiser_spend, gadmobe_cost, bid_price, bid_floor,advertiser_price,campaign_id,response_id,creative_id,country) VALUES
        (
          25,112,'MediaShakers-US-East','086-6a-051ef24c017f4866',0.46988000554858445,0.26188,0.2680000055485845,0.1,0.47600001109716894,'1167_21','Wi89d4Iu7-w_0','1167_29','USA'
        )'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 129
                  Master_UUID: 47451c14-87e6-11e8-93b3-f23c91cd2190
             Master_Info_File: /data/mysql/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: 190127 11:02:31
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)


#尝试跳过这个错误

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#仍然不能同步
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 104.237.146.129
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master.000066
          Read_Master_Log_Pos: 932450072
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 901
        Relay_Master_Log_File: mysql-master.000066
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '19-112-MediaShakers-US-East-2019-01-26 12:23:48-246-57-1962c2525' for key 'PRIMARY'' on query. Default database: 'gadmobe_dw'. Query: 'INSERT INTO open_rtb_win(exchange_id, dsp_id, endpoint_id, request_id, advertiser_spend, gadmobe_cost, bid_price, bid_floor,advertiser_price,campaign_id,response_id,creative_id,country) VALUES
        (
          19,112,'MediaShakers-US-East','246-57-1962c2525479a11c',0.465220031573686,0.26922,0.280000031573686,0.14,0.47600006314737203,'1167_21','pfV2DBMCttI_0','1167_29','USA'
        )'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 805630560
              Relay_Log_Space: 126820927
              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: Error 'Duplicate entry '19-112-MediaShakers-US-East-2019-01-26 12:23:48-246-57-1962c2525' for key 'PRIMARY'' on query. Default database: 'gadmobe_dw'. Query: 'INSERT INTO open_rtb_win(exchange_id, dsp_id, endpoint_id, request_id, advertiser_spend, gadmobe_cost, bid_price, bid_floor,advertiser_price,campaign_id,response_id,creative_id,country) VALUES
        (
          19,112,'MediaShakers-US-East','246-57-1962c2525479a11c',0.465220031573686,0.26922,0.280000031573686,0.14,0.47600006314737203,'1167_21','pfV2DBMCttI_0','1167_29','USA'
        )'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 129
                  Master_UUID: 47451c14-87e6-11e8-93b3-f23c91cd2190
             Master_Info_File: /data/mysql/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: 190127 11:31:29
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)


#在配置文件里配置跳过错误
注意：如果不重要的数据可跳过. 

$ vim /etc/my.cnf

加入：
slave_skip_errors = 1062

#重启mysql
$ /etc/init.d/mysqld restart


#查看同步情况
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 104.237.146.129
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master.000066
          Read_Master_Log_Pos: 933161094
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 28953833
        Relay_Master_Log_File: mysql-master.000066
             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: 834583492
              Relay_Log_Space: 127533112
              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: 60002
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: 129
                  Master_UUID: 47451c14-87e6-11e8-93b3-f23c91cd2190
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: update
           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: 
                Auto_Position: 0
1 row in set (0.00 sec)

转载于:https://my.oschina.net/AnnaWu/blog/3006378