Last_Errno: 1594 the master's binary log is corrupted Last_SQL_Errno: 1062

294人阅读 评论(0) 收藏 举报
分类:
mysql主从复制不同步异常

查看从库同步状态,发现Slave_SQL_Running: No状态异常,主备库已经不再同步,初步怀疑是断电倒是binlog的问题
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000030
          Read_Master_Log_Pos: 54007656
               Relay_Log_File: slave01-relay-bin.000068
                Relay_Log_Pos: 63524817
        Relay_Master_Log_File: log-bin.000029
       ..........................................
  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.
          

 查看下slave的error log 发现binlog最后截止在 'log-bin.000029' position 63524608

2017-10-13T05:44:56.382911Z 1 [Note] Slave I/O thread killed while reading event for channel ''
2017-10-13T05:44:56.382941Z 1 [Note] Slave I/O thread exiting for channel '', read up to log 'log-bin.000030', position 54007656
2017-10-13T05:44:58.675958Z 7 [Note] Slave I/O thread: Start semi-sync replication to master 'repl@192.168.1.3:3306' in log 'log-bin.000030' at position 54007656
2017-10-13T05:44:58.675993Z 7 [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-10-13T05:44:58.677146Z 7 [Note] Slave I/O thread for channel '': connected to master 'repl@192.168.1.3:3306',replication started in log 'log-bin.000030' at position 54007656
2017-10-13T05:44:58.687664Z 8 [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-10-13T05:44:58.688769Z 8 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'log-bin.000029' at position 63524608, relay log './slave01-relay-bin.000068' position: 63524817
2017-10-13T05:44:58.688858Z 8 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 65, event_type: 34
2017-10-13T05:44:58.688865Z 8 [ERROR] Error reading relay log event for channel '': slave SQL thread aborted because of I/O error
2017-10-13T05:44:58.688877Z 8 [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-10-13T05:44:58.688881Z 8 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". 
We stopped at log 'log-bin.000029' position 63524608

mysql>show binlog events in 'log-bin.000029' from 63524608 limit 3; 查看位于 pos 为 63524608 的一行记录下 endpos 63524608

于是采用在slave上重新定位master的binlog的方法以重新同步主备库 

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

mysql> change master to master_host='192.168.1.3',master_user='repl',master_password='ppay', master_log_file='log-bin.000029',master_log_pos=63524608;

Query OK, 0 rows affected, 2 warnings (0.16 sec)

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

查看同步状态

mysql> show  slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000030
          Read_Master_Log_Pos: 54416555
               Relay_Log_File: slave01-relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: log-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
            ...................................
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table log.logsver; Duplicate entry '874655' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log log-bin.000029, end_log_pos 63525006
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 63524608
              Relay_Log_Space: 54417775
            ....................................
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table log.logsver; Duplicate entry '874655' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log log-bin.000029, end_log_pos 63525006
  Replicate_Ignore_Server_Ids: 
            ....................................
ERROR: 
No query specified


重新启动slave之后,又出现了新的错误,常见的error 1062,主键重复

主键重复

在slave已经有874655该条记录,又在master上插入了同一条记录,所以报错。
Last_SQL_Error: Could not execute Write_rows event on table log.logsver; 
Duplicate entry '874655' for key 'PRIMARY', 
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; 
the event's master log log-bin.000029, end_log_pos 63525006

解决方法:

在slave上先看下log.logsver表结构,在主备库上查询重复的记录状态

mysql> desc log.logsver;

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(100)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)  | NO   |     | NULL    |                |
| level    | int(2)       | NO   |     | NULL    |                |
| msg      | varchar(255) | NO   |     | NULL    |                |
| intime   | datetime     | NO   |     | NULL    |                |
| codename | varchar(25)  | YES  |     | NULL    |                |
| codefunc | varchar(25)  | YES  |     | NULL    |                |
| codeline | varchar(10)  | YES  |     | NULL    |                |
| process  | varchar(10)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
停止slave
mysql>stop slave;

删除重复的主键
mysql> delete from logsver where id=874655;
Query OK, 1 row affected (0.07 sec)

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

重新开启salve
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000030
          Read_Master_Log_Pos: 54997622
               Relay_Log_File: slave01-relay-bin.000003
                Relay_Log_Pos: 43225
        Relay_Master_Log_File: log-bin.000030
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.................................
1 row in set (0.00 sec)


在master上和slave上分别确认,

mysql> select count(*) from  logsver where id=874655;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

mysql> select count(*) from  logsver where id=874655;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

至此,主备库重新同步正常

查看评论

Mysql 主从出现故障

今天由于 slave 机异常掉电导致主从同步出现异常在 slave mysql 上查看 slave 状态: show slave status\G发现 IO 线程正常,但是 SQL 线程 NO,错误...
  • xtjsxtj
  • xtjsxtj
  • 2016-08-17 18:32:14
  • 992

x86 TSC使用的那些坑

用x86 TSC寄存器的时间戳来计算程序的运行时间用于性能分析是常见的性能分析代码的植入技巧。但这里面到处都是坑。连Linux内核都未幸免,TSC的各种bug层出不穷。再加上各种虚拟化,热迁移导致TS...
  • yayong
  • yayong
  • 2016-02-06 09:25:24
  • 3832

Ext4

转载:https://ext4.wiki.kernel.org/index.php/Ext4_Disk_LayoutExt4 Disk LayoutThis document attempts to ...
  • u012218838
  • u012218838
  • 2018-02-08 14:34:19
  • 182

mysql slave 不能同步:Last_Errno: 1062

场景重现:       mysql 双主实现同步之后,同步机器IP设置为:192.168.101.118和192.168.101.119 在同步数据库中创建一个表  create table t...
  • lylyf99
  • lylyf99
  • 2013-05-28 14:10:23
  • 6538

mysql Last_SQL_Errno: 1032

今天遇到线上报错:相关信息如下 Slave_IO_Running: Yes             Slave_SQL_Running: No  Last_SQL_Errno: 1032 ...
  • lanjiangzhou
  • lanjiangzhou
  • 2016-05-28 17:04:31
  • 1345

ubuntu14.04编译android源码

Initializing a Build Environment IN THIS DOCUMENT Choosing a BranchSetting up a Linux ...
  • coloriy
  • coloriy
  • 2015-10-15 15:20:16
  • 562

mysql主从同步Slave_SQL_Running: No,Last_Errno: 1062的解决方法

在备节点上mysql> show slave status\G *************************** 1. row *************************** ...
  • libinbin_1014
  • libinbin_1014
  • 2016-03-17 14:11:15
  • 784

MySQL内核月报 2014.11

from: http://mysql.taobao.org/index.php/MySQL%E5%86%85%E6%A0%B8%E6%9C%88%E6%8A%A5_2014.11
  • longxibendi
  • longxibendi
  • 2014-11-18 22:34:12
  • 1611

同步故障Last_Errno:1061

1051处理完又出现好多1061的错误。 mysql>show slave status\G *************************** 1. row **********...
  • u010587433
  • u010587433
  • 2015-07-01 09:00:51
  • 798

brctl配置linux bridge及虚拟bridge实现

NAME        brctl - ethernet bridge administration SYNOPSIS        brctl [command] DESCRIPTION      ...
  • u011956172
  • u011956172
  • 2014-08-15 12:47:35
  • 14654
    个人资料
    持之以恒
    等级:
    访问量: 85万+
    积分: 9310
    排名: 2483
    简介
    此博客为笔者的DT工作记录,欢迎一起交流学习工作中的点滴,若博客文章观点存在原理的错误、纰漏,笔者非常欢迎各位不保留地指出
    最新评论