文章/答案/技术大牛

发布

社区首页 >问答首页 >MySQL复制在错误1594后重复密钥错误

问MySQL复制在错误1594后重复密钥错误
EN

Database Administration用户

提问于 2014-10-07 08:27:23

回答 1查看 764关注 0票数 1

所以我最近遇到了这样的事情：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 198.101.212.103
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000103
          Read_Master_Log_Pos: 30051137
               Relay_Log_File: mysqld-relay-bin.000095
                Relay_Log_Pos: 23511387
        Relay_Master_Log_File: mysql-bin.000095
             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: 23511241
              Relay_Log_Space: 477287485
              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: 1

于是我看着师父：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000103 | 30007591 | xxx_l       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

一切都很好。所以我跑了：

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

mysql> reset slave;
Query OK, 0 rows affected (0.09 sec)

mysql> change master to master_log_file='mysql-bin.000095', master_log_pos=23511241;
Query OK, 0 rows affected (0.02 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: 198.101.212.103
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000103
          Read_Master_Log_Pos: 30763976
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 58516
        Relay_Master_Log_File: mysql-bin.000091
             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 '128776' for key 'PRIMARY'' on query. Default database: 'xxx_l'. Query: 'INSERT INTO images ...'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 58370
              Relay_Log_Space: 684452249
              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 '128776' for key 'PRIMARY'' on query. Default database: 'xxx_l'. Query: 'INSERT INTO images ...'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

显然，我的复制现在已经无法修复了。考虑到每一个答案都说，从上一个主位置复制是安全的，为什么这不起作用呢？

顺便提一句:如果我不需要改头换面，请告诉我。

mysql

replication

回答 1

Database Administration用户

发布于 2014-10-07 08:52:27

我将如何做到这一点，而不必重新加载所有数据：

把奴隶从生产中带走--它已经漂移并且有错误的数据。
跳过复制错误，直到从跳过错误再次运行复制(但请记住，使用错误的数据)。不总是可能的。
使用pt表-校验和识别主从差异。
使用特定表上的pt表同步 (S)修复错误。这两种工具在使用过滤时都很难使用。
现在您有了一个一致的复制(在一段时间内-您应该确定导致它的根本问题)。

考虑到每一个答案都说，从上一个主位置复制是安全的，为什么这不起作用呢？

，这不是一个理智的MySQL建议。句号：

我不能肯定地说，但如果有过滤，我的经验表明，这是复制问题的90%的根本原因。有许多原因不使用主端过滤而有相同的结果(复制中断，难以同步，不可能促进从主，等等)。其他选项包括中继日志的丢失或损坏。

票数 1

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/78545

复制

相似问题

问MySQL复制在错误1594后重复密钥错误
EN

回答 1

Database Administration用户

，这不是一个理智的MySQL建议。句号：

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL复制在错误1594后重复密钥错误EN

回答 1

Database Administration用户

，这不是一个理智的MySQL建议。句号：

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL复制在错误1594后重复密钥错误
EN