mysql主从异常处理

测试环境快速解决方案（从库丢失部分数据）

• 查看主库binlog(file和positon)

mysql>show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000449 | 649206160 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 查看从库slave状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.16.1.170
                  Master_User: repl1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000449
          Read_Master_Log_Pos: 509513254
               Relay_Log_File: localhost-relay-bin.001450
                Relay_Log_Pos: 256253683
        Relay_Master_Log_File: mysql-bin.000446
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: aaa.tv_user_netoperator_group,aaa.tv_adaptation_rule,aaa.tv_auth_urla.tv_module_engine,aaa.tv_service_group_config,aaa.tv_subscriber_group,aaa.tv_subscriber_rule,aaa.tv_terminal_group,aaa.tv_terminal_module_config,aaa.tv_terminal_series,aaa.tv_rel_product_package,aantent,aaa.tv_rel_business_group_content,aaa.tv_rel_product_service,aaa.tv_rel_product_group,aaa.tv_,aaa.tv_product,aaa.tv_service,aaa.tv_product_group,aaa.tv_rel_product_package,aaa.tv_product_packa_filter
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: Query caused different errors on master and slave.     Error on mastnt' error code=1545 ; Error on slave: actual message='no error', error code=0. Default database: 'or`'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 494503001
              Relay_Log_Space: 3492493736
              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: 0
               Last_SQL_Error: Query caused different errors on master and slave.     Error on mastnt' error code=1545 ; Error on slave: actual message='no error', error code=0. Default database: 'or`'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 42b5adbc-e0e4-11e9-9d56-000c29d326cb
             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: 201016 09:40:51
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)


ERROR: 
No query specified

• 停止从库slave

mysql>stop slave;

• 修改binlog(file和positon)值和主库一致

mysql> change master to master_host='172.16.1.170',master_user='repl1',master_password='123456',master_log_file='mysql-bin.000449', master_log_pos=649206160;
Query OK, 0 rows affected, 2 warnings (0.31 sec)

• 启动从库slave，并查看状态

mysql>start slave;
mysql>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.16.1.170
                  Master_User: repl1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000449
          Read_Master_Log_Pos: 649206160
               Relay_Log_File: localhost-relay-bin.001450
                Relay_Log_Pos: 256253683
        Relay_Master_Log_File: mysql-bin.000446
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: aaa.tv_user_netoperator_group,aaa.tv_adaptation_rule,aaa.tv_auth_urla.tv_module_engine,aaa.tv_service_group_config,aaa.tv_subscriber_group,aaa.tv_subscriber_rule,aaa.tv_terminal_group,aaa.tv_terminal_module_config,aaa.tv_terminal_series,aaa.tv_rel_product_package,aantent,aaa.tv_rel_business_group_content,aaa.tv_rel_product_service,aaa.tv_rel_product_group,aaa.tv_,aaa.tv_product,aaa.tv_service,aaa.tv_product_group,aaa.tv_rel_product_package,aaa.tv_product_packa_filter
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: Query caused different errors on master and slave.     Error on mastnt' error code=1545 ; Error on slave: actual message='no error', error code=0. Default database: 'or`'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 494503001
              Relay_Log_Space: 3492493736
              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: 0
               Last_SQL_Error: Query caused different errors on master and slave.     Error on mastnt' error code=1545 ; Error on slave: actual message='no error', error code=0. Default database: 'or`'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 42b5adbc-e0e4-11e9-9d56-000c29d326cb
             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: 201016 09:40:51
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

• 同步已恢复

生产环境解决方案：

• 停止数据库写操作

mysql> set global read_only=1;    #1是只读，0是读写
mysql> show global variables like "%read_only%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | ON    |
| tx_read_only     | OFF   |
+------------------+-------+

• 主库执行

mysql>FLUSH TABLES WITH READ LOCK;

• 查看主库状态

mysql>show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000449 | 649206160 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 导出主库数据
  mysqldump -u root -ppassword --opt -R database > database20201016.sql

• 把主库导出的数据导入从库
  mysql -uroot -p database < database20201016.sql

• 主库执行
  mysql>UNLOCK TABLES;

• 从库重启修改binlog并重启slave

mysql>stop slave;
mysql>reset slave;
mysql>change master to master_host='主机IP',master_user='master',master_password='master',master_log_file='mysql-bin.000011',master_log_pos=19282684;
mysql>start slave;

• 主从同步恢复正常

拓展：

方法一：忽略错误后，继续同步

• 该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况：

mysql> stop slave;

-- 表示跳过一步错误，后面的数字可变
mysql> set global sql_slave_skip_counter =1;
mysql> start slave;
mysql> show slave status\G 
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

ok，现在主从同步状态正常了。。。

方法二：指定跳过错误代码，继续同步

• 主键冲突、表已存在等错误代码如1062,1032,1060等，可以在mysql主配置文件指定略过此类异常并继续下条sql同步，这样也可以避免很多主从同步的异常中断

[mysqld]

slave-skip-errors = 1062,1032,1060

重新启动mysql

service mysqld restart

之后再用mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes