文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >MySQL复制错误1594

问MySQL复制错误1594
EN

Database Administration用户
提问于 2014-06-30 20:16:40
回答 3查看 10.1K关注 0票数 4

是否有一种方法可以重新启动复制而不遍历MySQL：：MySQL5.1参考手册：：16复制。

我在master上没有看到任何错误，我也不完全确定为什么slave会遇到以下问题：

代码语言：javascript
复制
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: Master_Host-FQDN.TLD
                  Master_User: Master_User-REPL
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000057
          Read_Master_Log_Pos: 247673142
               Relay_Log_File: mysql:PR_DB-relay-bin.000041
                Relay_Log_Pos: 5537565
        Relay_Master_Log_File: mysql-bin.000044
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: RDD1,RDD2,RDDX
          Replicate_Ignore_DB: information_schema,mysql
           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: 25338679
              Relay_Log_Space: 2190669763
              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.
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

--log-error=/var/log/mysqld.log (奴隶)：

代码语言：javascript
复制
140630  9:10:53 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 255, event_type: 2
140630  9:10:53 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
140630  9:10:53 [ERROR] Slave SQL: 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. Error_code: 1594
140630  9:10:53 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000044' position 25338679

我是否可以使用CHANGE MASTER TO (MySQL：：MySQL5.1参考手册：：16.1.1.10设置奴隶的主配置)并指向相应的值：

  • MASTER_LOG_FILE = Relay_Master_Log_File，
  • MASTER_LOG_POS Exec_Master_Log_Pos.
mysql
replication
mysql-5.1
EN

回答 3

Database Administration用户

回答已采纳

发布于 2014-06-30 21:06:12

你应该复制的最安全的位置是

  • 日志文件mysql-bin.000044
  • 日志位置: 25338679

它们表示在从服务器上执行的最新二进制日志事件。这就是你从那里获取复制的地方。运行CHANGE MASTER TO将删除中继日志，并从全新的中继日志开始。

票数 8
EN

Database Administration用户

发布于 2017-05-08 03:00:50

在我的例子中，我似乎遇到了同样的问题：

代码语言：javascript
复制
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.4.0.4
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: gtc-master-bin.000022
          Read_Master_Log_Pos: 163183335
               Relay_Log_File: gtc-relay-bin.000195
                Relay_Log_Pos: 37830249
        Relay_Master_Log_File: gtc-master-bin.000022
             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: 74792622
              Relay_Log_Space: 163184965
              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
                  Master_UUID: 793997ad-6398-11e6-a062-000d3aa1879f
             Master_Info_File: /var/lib/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: 170506 19:50:05
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

通过查看从从中获得的错误日志，我可以看到根本原因是磁盘空间不足的问题：

代码语言：javascript
复制
2017-05-06T19:02:52.678039Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 15056ms. The settings might not be optimal. (flushed=2 and evicted=0, during the time.)
2017-05-06T19:10:27.232249Z 2 [ERROR] Disk is full writing './gtc-relay-bin.000195' (Errcode: 15180656 - No space left on device). Waiting for someone to free space...
2017-05-06T19:10:27.232689Z 2 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
2017-05-06T19:10:27.362769Z 18007 [Note] Aborted connection 18007 to db: 'unconnected' user: 'backup' host: 'localhost' (Got an error reading communication packets)
2017-05-06T19:20:27.244649Z 2 [ERROR] Disk is full writing './gtc-relay-bin.000195' (Errcode: 15180656 - No space left on device). Waiting for someone to free space...
2017-05-06T19:20:27.264895Z 2 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
2017-05-06T19:30:27.417753Z 2 [ERROR] Disk is full writing './gtc-relay-bin.000195' (Errcode: 15180656 - No space left on device). Waiting for someone to free space...
2017-05-06T19:30:27.419691Z 2 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
2017-05-06T19:40:27.864058Z 2 [ERROR] Disk is full writing './gtc-relay-bin.000195' (Errcode: 15180656 - No space left on device). Waiting for someone to free space...
2017-05-06T19:40:27.864800Z 2 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
2017-05-06T19:49:40.947589Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-05-06T19:49:40.948741Z 0 [Warning] Changed limits: ta2017-05-06T19:49:49.121921Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-05-06T19:49:49.121958Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-05-06T19:49:49.122016Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-05-06T19:49:53.758747Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-05-06T19:49:53.759413Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-05-06T19:49:53.759422Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-05-06T19:49:53.775719Z 0 [Note] InnoDB: Waiting for purge to start
2017-05-06T19:49:53.828518Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6720ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-05-06T19:49:53.829030Z 0 [Note] InnoDB: 5.7.13 started; log sequence number 15543585458
2017-05-06T19:49:53.830064Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-05-06T19:49:53.830640Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-05-06T19:49:53.912376Z 0 [Note] Recovering after a crash using gtc-slave-bin
2017-05-06T19:49:53.912476Z 0 [Note] Starting crash recovery...
2017-05-06T19:49:53.912579Z 0 [Note] Crash recovery finished.
2017-05-06T19:49:55.163266Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170506 19:49:55
2017-05-06T19:49:58.665294Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-05-06T19:49:58.665321Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2017-05-06T19:49:58.665330Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2017-05-06T19:49:58.665368Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2017-05-06T19:50:05.480733Z 0 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 367, event_type: 2
2017-05-06T19:50:05.480765Z 0 [Warning] Error reading GTIDs from relaylog: -1
2017-05-06T19:50:05.620202Z 2 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-05-06T19:50:05.621524Z 1 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2017-05-06T19:50:05.621572Z 1 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'gtc-master-bin.000022' at position 74792622, relay log './gtc-relay-bin.000195' position: 37830249
2017-05-06T19:50:05.630830Z 2 [Note] Slave I/O thread for channel '': connected to master 'replicator@10.4.0.4:3306',replication started in log 'gtc-master-bin.000022' at position 74792800
2017-05-06T19:50:05.651022Z 1 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 367, event_type: 2
2017-05-06T19:50:05.651066Z 1 [ERROR] Error reading relay log event for channel '': slave SQL thread aborted because of I/O error
2017-05-06T19:50:05.651117Z 1 [ERROR] Slave SQL for channel '': 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. Error_code: 1594
2017-05-06T19:50:05.651138Z 1 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'gtc-master-bin.000022' position 74792622
2017-05-06T19:50:05.797632Z 0 [Note] Event Scheduler: Loaded 0 events
2017-05-06T19:50:05.797742Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.13-0ubuntu0.16.04.2-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2017-05-06T19:50:05.836873Z 4 [Note] Access denied for user 'root'@'localhost' (using password: NO)

一旦我解决了这个问题，恢复复制的方法是：

  1. 检查Relay_Master_Log_File和Exec_Master_Log_Pos值。就我而言，gtc-master-bin.000022和74792622。
  2. 使用上述值发出以下语句。

这条路：

代码语言：javascript
复制
mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.52 sec)
mysql> change master to master_log_file='gtc-master-bin.000022',master_log_pos=74792622;
Query OK, 0 rows affected (0.49 sec)
mysql> start slave;

然后它又开始正常工作了。

票数 2
EN

Database Administration用户

发布于 2018-01-28 18:45:06

我也犯了同样的错误，我这样做：

  1. 停止奴隶(停止奴隶;)
  2. 重置从(rest从;)
  3. 更改master_log_file和master_log_pos (将母版更改为MASTER_LOG_POS= _LOG_FILE=‘mysql-Iin.000006’，MASTER_LOG_POS= 11054817;) ( MASTER_LOG_FILE使用Relay_Master_Log_File，MASTER_LOG_POS使用Exec_Master_Log_Pos )
  4. 启动从(启动从;)

在执行此操作后，我看到:重复输入错误(1062错误)，对于此错误，我执行以下步骤：

  1. 打开my.cnf文件(/etc/my.cnf或/etc/mysql/my.cnf)
  2. 添加这一行:从-跳-错误= 1062
  3. 重新启动mysql服务器

和从服务器正常复制，没有任何错误。

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

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

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档