mysql错误忽略

mysql数据库-从库忽略1条错误

mysql控制台输入命令操作,跳过错误记录

stop slave;
set global sql_slave_skip_counter =1;
start slave;

mysql数据库-从库忽略所有错误

修改 my.cnf 配置文件

slave-skip-errors=all

重启mysql服务

service mysql restart

mysql设置read_only依赖执行更新问题

从库连接账号授权时不能指定有super或all权限

修改参数 super_read_only ，默认值为OFF，修改为ON，可以使super 用户read-only 【和mysql版本相关，低版本可能无效】

mysql主从数据库同步问题

问题：Relay log read failure: Could not parse relay log event entry

参考博客：主从数据库同步问题：Relay log read failure: Could not parse relay log event entry.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: XXX.XXX.XX.X
                  Master_User: root
                  Master_Port: 61011
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000280
          Read_Master_Log_Pos: 323835180
               Relay_Log_File: relay-log.000008
                Relay_Log_Pos: 286333492
         Relay_Master_Log_File: mysql-bin.000279
             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: 905542477
              Relay_Log_Space: 778371928
              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: 50

解决记录

通过命令show slave status\G;查看Relay_Master_Log_File和Exec_Master_Log_Pos参数，确定日志同步失败的位置，重新配置主从。

## 停止从库的同步
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

## 重置从库配置
mysql> reset slave all;
Query OK, 0 rows affected (0.07 sec)

## 变更从库配置
mysql> change master to master_host='XXX.XX.X.X',master_port=3306,master_user='root',master_password='XXXXXXX', master_log_file='mysql-bin.000279',master_log_pos=905542477;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log