Mysql学习之主从复制故障处理

最新推荐文章于 2023-11-23 16:44:46 发布

jzzw

最新推荐文章于 2023-11-23 16:44:46 发布

阅读量177

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/u014710633/article/details/98446699

版权

Mysql 专栏收录该内容

44 篇文章 2 订阅

订阅专栏

1.1062(主键冲突)

模拟故障：
从库先插入：

mysql> insert into test values(1,1,1);
Query OK, 1 row affected (0.14 sec)

主库再插入：

mysql> insert into test values(1,1,1);
Query OK, 1 row affected (0.11 sec)

从库show slaves status:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.169.10.241
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 2962
               Relay_Log_File: rhel6-relay-bin.000002
                Relay_Log_Pos: 2750
        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: 1062
                   Last_Error: Could not execute Write_rows event on table test.test; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 2931
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2697
              Relay_Log_Space: 3222
              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: Could not execute Write_rows event on table test.test; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 2931
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10241
                  Master_UUID: 5f95521a-b073-11e9-9591-000c296ee978
             Master_Info_File: /u01/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: 190729 03:20:20
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 5f95521a-b073-11e9-9591-000c296ee978:4-13
            Executed_Gtid_Set: 5f95521a-b073-11e9-9591-000c296ee978:1-12,
7842c967-0958-11e9-9f6c-000c296ee978:1-36,
d812b5b4-b13c-11e9-a0a8-000c296ee978:1,
eb495b42-b06a-11e9-af85-000c296ee978:1-3,
fad44c35-affe-11e9-b2fc-000c296ee978:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

解决思路：跳过该复制(后续的话可以把从库设置为只读)
方法一：

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

mysql> set gtid_next='5f95521a-b073-11e9-9591-000c296ee978:13';
Query OK, 0 rows affected (0.01 sec)

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

Query OK, 0 rows affected (0.14 sec)

mysql> set gtid_next='automatic';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.12 sec)

方法二：使用pt-slave-restart

[root@rhel6 ~]# pt-slave-restart -uroot -xxx --socket=/tmp/mysql3307.sock 
2019-07-29T04:19:29 S=/tmp/mysql3307.sock,p=...,u=root rhel6-relay-bin.000003         454 1062

2.1032(从库缺数据)
模拟故障：
从库先删除：

mysql> delete from test where id=41;
Query OK, 1 row affected (0.12 sec)

主库再更新：

mysql> update test set c=42 where id=41;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

从库show slaves status:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.169.10.241
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 3506
               Relay_Log_File: rhel6-relay-bin.000003
                Relay_Log_Pos: 719
        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 Update_rows event on table test.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000007, end_log_pos 3475
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3227
              Relay_Log_Space: 4066
              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 test.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000007, end_log_pos 3475
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10241
                  Master_UUID: 5f95521a-b073-11e9-9591-000c296ee978
             Master_Info_File: /u01/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: 190729 04:49:20
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 5f95521a-b073-11e9-9591-000c296ee978:4-15
            Executed_Gtid_Set: 5f95521a-b073-11e9-9591-000c296ee978:1-14,
7842c967-0958-11e9-9f6c-000c296ee978:1-36,
d812b5b4-b13c-11e9-a0a8-000c296ee978:1-3,
eb495b42-b06a-11e9-af85-000c296ee978:1-3,
fad44c35-affe-11e9-b2fc-000c296ee978:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

解决思路：通过解析sql找到缺少的数据，在从库上重新执行，然后跳过该复制

[root@rhel6 ~]# mysqlbinlog --socket=/tmp/mysql.sock  -v --base64-output=decode-rows /u01/mysql/log/mysql-bin.000007 |grep -A 15 3475 > /u01/binlog20190804.log
[root@rhel6 ~]#
[root@rhel6 ~]# cat /u01/binlog20190804.log 
#190729  4:49:20 server id 10241  end_log_pos 3475 CRC32 0xd5bcdc76 	Update_rows: table id 382 flags: STMT_END_F
### UPDATE `test`.`test`
### WHERE
###   @1=41
###   @2=41
###   @3=41
### SET
###   @1=41
###   @2=42
###   @3=41
# at 3475
#190729  4:49:20 server id 10241  end_log_pos 3506 CRC32 0xb46bd6e5 	Xid = 248
COMMIT/*!*/;
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*/;
[root@rhel6 ~]# mysql -uroot -pxxx --socket=/tmp/mysql3307.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> insert into test.test values(41,42,41);
Query OK, 1 row affected (0.18 sec)

mysql> exit
Bye
[root@rhel6 ~]# pt-slave-restart -uroot -pxxx --socket=/tmp/mysql3307.sock
2019-07-29T08:31:21 S=/tmp/mysql3307.sock,p=...,u=root rhel6-relay-bin.000003         719 1032