读书是在别人思想的帮助下建立自己的思@尼古拉。鲁巴金 (俄国作家)

MySQL经典复制错误1062 1032

MySQL 2018-05-22 浏览量: 625 字数统计: 771 最后更新: 2018-05-22 21:16

文章目录[显示]

因为 所有的 复制 都是基于 binlog 进行的 ,所以出问题的话,基本都需要解析binlog,看看是哪里出现的问题,看看为什么重放不了或者为什么慢了,然后在想想 复制的流程,比如说 SQL_thread怎么用的索引,然后分析

1062

主键冲突,所以把 从库的删掉 就可以继续了
重现
多实实例安装,主库3306,从库3307
主库:配置复制账号

"root@localhost:mysql3306.sock  [(none)]>grant replication slave on *.* to 'root'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.06 sec)

"root@localhost:mysql3306.sock  [(none)]>flush privileges;
Query OK, 0 rows affected (0.01 sec)

"root@localhost:mysql3306.sock  [(none)]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |      629 |              |                  | f3c4fb7c-43a8-11e8-996c-00163e08c051:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

从库

"root@localhost:mysql3307.sock  [(none)]>CHANGE MASTER TO 
MASTER_HOST='121.00.00.00',
MASTER_USER='root',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

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

主库

"root@localhost:mysql3306.sock  [(none)]>create database db1;
Query OK, 1 row affected (0.00 sec)

"root@localhost:mysql3306.sock  [db1]>create table t1(id int primary key,name varchar(10))
    -> ;
Query OK, 0 rows affected (0.09 sec)

"root@localhost:mysql3306.sock  [db1]>insert into t1 values(1,'java');
Query OK, 1 row affected (0.01 sec)

"root@localhost:mysql3306.sock  [db1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | java |
+----+------+
1 row in set (0.00 sec)

从库

"root@localhost:mysql3307.sock  [db1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | java |
+----+------+
1 row in set (0.00 sec)

"root@localhost:mysql3307.sock  [db1]>insert into t1 values(2,'python');
Query OK, 1 row affected (0.01 sec)

"root@localhost:mysql3307.sock  [db1]>select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | java   |
|  2 | python |
+----+--------+
2 rows in set (0.00 sec)

主库

"root@localhost:mysql3306.sock  [db1]>insert into t1 values(2,'pytho');
Query OK, 1 row affected (0.00 sec)

"root@localhost:mysql3306.sock  [db1]>select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | java  |
|  2 | pytho |
+----+-------+
2 rows in set (0.00 sec)

从库

"root@localhost:mysql3307.sock  [db1]>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 121.42.15.26
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1493
               Relay_Log_File: slave1-relay-bin.000004
                Relay_Log_Pos: 1446
        Relay_Master_Log_File: mysql-bin.000004
             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: Could not execute Write_rows event on table db1.t1; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000004, end_log_pos 1462

发现 io 还在写,但是 SQL 已经重放不过去了

这个时候可以备份,也可以暴力一些 直接把 冲突的数据删掉

"root@localhost:mysql3307.sock  [db1]>delete from t1 where id=2;
Query OK, 1 row affected (0.01 sec)

因为 只有 SQL_thread 挂了,所以启动一下 SQL_thread就 OK了

"root@localhost:mysql3307.sock  [db1]>start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

"root@localhost:mysql3307.sock  [db1]>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 121.42.15.26
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1493
               Relay_Log_File: slave1-relay-bin.000004
                Relay_Log_Pos: 1706
        Relay_Master_Log_File: mysql-bin.000004
             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: 1493
              Relay_Log_Space: 2001
              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: 0
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: 263306
                  Master_UUID: f3c4fb7c-43a8-11e8-996c-00163e08c051
             Master_Info_File: /data/mysql/mysql3307/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: f3c4fb7c-43a8-11e8-996c-00163e08c051:1-9
            Executed_Gtid_Set: bdd4d4e0-461e-11e8-95c3-00163e08c051:1-3,
f3c4fb7c-43a8-11e8-996c-00163e08c051:1-9
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

"root@localhost:mysql3307.sock  [db1]>select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | java  |
|  2 | pytho |
+----+-------+
2 rows in set (0.00 sec)

这种现象一般出现在:因为某些事务 主库挂了,然后 重启了 又拿他当 主库,可能会出现这种问题,如果是全部正常运行的话,基本不会出现这种情况,前提是从库read_only

error 1032

1032 错误分为两种:

  • 一个是 update 错误
  • 一种是 delete错误

先演示update

从库上删除一个

"root@localhost:mysql3307.sock  [db1]>select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | java  |
|  2 | pytho |
+----+-------+
2 rows in set (0.00 sec)

"root@localhost:mysql3307.sock  [db1]>delete from t1 where id=2;
Query OK, 1 row affected (0.00 sec)

"root@localhost:mysql3307.sock  [db1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | java |
+----+------+
1 row in set (0.00 sec)

主库上update

"root@localhost:mysql3306.sock  [db1]>select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | java  |
|  2 | pytho |
+----+-------+
2 rows in set (0.00 sec)

"root@localhost:mysql3306.sock  [db1]>update t1 set name='python' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

"root@localhost:mysql3306.sock  [db1]>select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | java   |
|  2 | python |
+----+--------+
2 rows in set (0.00 sec)

从库

"root@localhost:mysql3307.sock  [db1]>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 121.42.15.26
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1766
               Relay_Log_File: slave1-relay-bin.000004
                Relay_Log_Pos: 1706
        Relay_Master_Log_File: mysql-bin.000004
             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: 1032
                   Last_Error: Could not execute Update_rows event on table db1.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000004, end_log_pos 1735
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1493
              Relay_Log_Space: 2274
              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: 1032
               Last_SQL_Error: Could not execute Update_rows event on table db1.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000004, end_log_pos 1735 # 主库上的参照点,也就是在4 这个文件里面的 1735 这个位置 结束,也就是我们们需要找到开始的位置,也就是 Exec_Master_Log_Pos
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 263306
                  Master_UUID: f3c4fb7c-43a8-11e8-996c-00163e08c051
             Master_Info_File: /data/mysql/mysql3307/data/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: 180422 20:23:26
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: f3c4fb7c-43a8-11e8-996c-00163e08c051:1-10
            Executed_Gtid_Set: bdd4d4e0-461e-11e8-95c3-00163e08c051:1-4,
f3c4fb7c-43a8-11e8-996c-00163e08c051:1-9
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

我们知道 有一个 end_log_pos,和 mysql-bin.XXXXX,我们再去找 开始的位置,也就是 exec_master_log_pos
然后我们再去 主库找找 这个位置 看看发生了什么, 解析 主库上的 binlog

[root@slave1 ~]# mysqlbinlog --defaults-file=/data/mysql/mysql3306/my3306.cnf -v --base64-output=decode-rows /data/mysql/mysql3306/log/mysql-bin.000004

或者我们解析到一个文件里面

[root@slave1 ~]# mysqlbinlog --defaults-file=/data/mysql/mysql3306/my3306.cnf -v --base64-output=decode-rows /data/mysql/mysql3306/log/mysql-bin.000004 > 1.sql
[root@slave1 ~]# vi 1.sql

# 找到 1493 的位置,还能看到结束的位置 1735

COMMIT/*!*/;
# at 1493
#180422 19:47:32 server id 263306  end_log_pos 1493 CRC32 0xad1b75f3    Xid = 87
COMMIT/*!*/;
# at 1493
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'f3c4fb7c-43a8-11e8-996c-00163e08c051:10'/*!*/;
# at 1558
#180422 20:23:26 server id 263306  end_log_pos 1629 CRC32 0x12f5f50c    Query   thread_id=34    exec_time=0     error_code=0
SET TIMESTAMP=1524399806/*!*/;
BEGIN
/*!*/;
# at 1629
#180422 20:23:26 server id 263306  end_log_pos 1676 CRC32 0xc35eb6fa    Table_map: `db1`.`t1` mapped to number 115
# at 1676
#180422 20:23:26 server id 263306  end_log_pos 1735 CRC32 0x43da7dfb    Update_rows: table id 115 flags: STMT_END_F
### UPDATE `db1`.`t1`
### WHERE
###   @1=2
###   @2='pytho'
### SET
###   @1=2
###   @2='python'
# at 1735
#180422 20:23:26 server id 263306  end_log_pos 1766 CRC32 0x18230a00    Xid = 89
COMMIT/*!*/;
# at 1766
#180423 18:56:14 server id 263306  end_log_pos 1789 CRC32 0x07421762    Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

然后发现是一条一句的问题,然后去从库查看是不是没有这条语句,没有的话,insert一下,然后继续start slave SQL_thread

"root@localhost:mysql3307.sock  [db1]>insert into t1(id) values(2);
Query OK, 1 row affected (0.01 sec)

"root@localhost:mysql3307.sock  [db1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | java |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)


"root@localhost:mysql3307.sock  [db1]>start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

"root@localhost:mysql3307.sock  [db1]>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 121.42.15.26
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 194
               Relay_Log_File: slave1-relay-bin.000008
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000007
             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: 194
              Relay_Log_Space: 702
              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: 0
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: 263306
                  Master_UUID: f3c4fb7c-43a8-11e8-996c-00163e08c051
             Master_Info_File: /data/mysql/mysql3307/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: f3c4fb7c-43a8-11e8-996c-00163e08c051:1-10
            Executed_Gtid_Set: bdd4d4e0-461e-11e8-95c3-00163e08c051:1-5,
f3c4fb7c-43a8-11e8-996c-00163e08c051:1-10
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

"root@localhost:mysql3307.sock  [db1]>select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | java   |
|  2 | python |
+----+--------+
2 rows in set (0.00 sec)

第一个方法:我们 可以 添加一行记录,只要写主键就行和其他非空列

mysql还支持两个参数

[root@slave1 ~]# mysqlbinlog --defaults-file=/data/mysql/mysql3306/my3306.cnf --help| grep position
  -j, --start-position=#
                      Start reading the binlog at position N. Applies to the
  --stop-position=#   Stop reading the binlog at position N. Applies to the
start-position                    4
stop-position                     18446744073709551615

一个是 --start-position=1493 或者是 --end-position=1735

[root@slave1 ~]# mysqlbinlog --defaults-file=/data/mysql/mysql3306/my3306.cnf -v --base64-output=decode-rows --start-position=1493 /data/mysql/mysql3306/log/mysql-bin.000004 | more

注意:
start-position 是字节,mysqlbinlog 在哪个字节开始,mysqlbinlog 命令可以把前面的 全部跳过,直接 跳到 当前的 start-position 的位置

1032 delete

操作就比较简单了,可以直接跳过

模拟一下
从库

"root@localhost:mysql3307.sock  [db1]>delete from
    -> t1 where id=2;
Query OK, 1 row affected (0.00 sec)

"root@localhost:mysql3307.sock  [db1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | java |
+----+------+
1 row in set (0.00 sec)

主库

"root@localhost:mysql3306.sock  [db1]>select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | java   |
|  2 | python |
+----+--------+
2 rows in set (0.01 sec)

"root@localhost:mysql3306.sock  [db1]>delete from t1 where id=2;
Query OK, 1 row affected (0.01 sec)

"root@localhost:mysql3306.sock  [db1]>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | java |
+----+------+
1 row in set (0.00 sec)

从库

"root@localhost:mysql3307.sock  [db1]>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 121.42.15.26
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 455
               Relay_Log_File: slave1-relay-bin.000008
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000007
             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: 1032
                   Last_Error: Could not execute Delete_rows event on table db1.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000007, end_log_pos 424
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 194
              Relay_Log_Space: 963
              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: 1032
               Last_SQL_Error: Could not execute Delete_rows event on table db1.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000007, end_log_pos 424
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 263306
                  Master_UUID: f3c4fb7c-43a8-11e8-996c-00163e08c051
             Master_Info_File: /data/mysql/mysql3307/data/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: 180423 19:50:58
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: f3c4fb7c-43a8-11e8-996c-00163e08c051:1-11
            Executed_Gtid_Set: bdd4d4e0-461e-11e8-95c3-00163e08c051:1-6,
f3c4fb7c-43a8-11e8-996c-00163e08c051:1-10
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

此时我们并不知道 删除的哪一行,所以也不知道哪个位置出错了,当然是GTID的话 也可以判断 gtid 的位置 Retrieved_Gtid_Set: f3c4fb7c-43a8-11e8-996c-00163e08c051:1-11 出错
也可以按照上面 update的 解决方式来解决 :查看 相应的 binlog file 和相应的 binlog position 位点,看是哪一行delete 出的问题,再去从库看是不是没有这条记录

"root@localhost:mysql3307.sock  [db1]>select * from t1 where id = 2;
Empty set (0.00 sec)

确实没有,
这个时候我们有两种办法,第一种是 insert一个语句 ,然后在 start slave

另外一种是 跳过
我们看到,GTID 10 已经执行完了,下面该执行 GTID 11 了,
于是我们可以先停掉复制

stop slave;
set gtid_next='f3c4fb7c-43a8-11e8-996c-00163e08c051:11';
          注意不是 f3c4fb7c-43a8-11e8-996c-00163e08c051:1-11

# 以前的版本 直接 start slave 也跑过去了,但是后期 官方修复了这个问题,而且 直接 start的话 当前的 session会断掉,需重新连进来才能正常操作, 而且如果后面接一个DDL语句的话 也会报错,所以还是建议按照官方的来操作
注入一个空事务,
begin;commit;
set gtid_next='automatic';
start slave;
show slave status\G
小蜗牛 说:
Freedom is the source from which all meaning and all values spring .


文章版权归 原文作者所有丨本站默认采用CC-BY-NC-SA 4.0协议进行授权| 转载必须包含本声明,并以超链接形式注明原文作者和本文原始地址: https://www.tougetu.com/2018/05/mysql-replicatio-error-1.html

还不快抢沙发

添加新评论

代码 Pastebin Gist 加粗 删除线 斜体 链接 签到