欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql 主从复制如何跳过报错

程序员文章站 2022-03-11 22:38:08
一、传统binlog主从复制,跳过报错方法mysql> stop slave;mysql> set global sql_slave_skip_counter = 1;mysql>...

一、传统binlog主从复制,跳过报错方法

mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
mysql> show slave status \g

二、gtid主从复制,跳过报错方法

mysql> stop slave; #先关闭slave复制;
mysql> change master to ...省略... #配置主从复制;
mysql> show slave status\g #查看主从状态;

发现报错:

mysql> show slave status\g
*************************** 1. row ***************************
        slave_io_state: waiting for master to send event
         master_host: 172.19.195.212
         master_user: master-slave
         master_port: 3306
        connect_retry: 60
       master_log_file: mysql-bin.000021
     read_master_log_pos: 194
        relay_log_file: nginx-003-relay-bin.000048
        relay_log_pos: 454
    relay_master_log_file: mysql-bin.000016
       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: 1007
          last_error: error 'can't create database 'code'; database exists' on query. default database: 'code'. query: 'create database code'
         skip_counter: 0
     exec_master_log_pos: 8769118
       relay_log_space: 3500
       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: 1007
        last_sql_error: error 'can't create database 'code'; database exists' on query. default database: 'code'. query: 'create database code'
 replicate_ignore_server_ids: 
       master_server_id: 100
         master_uuid: fea89052-11ef-11eb-b241-00163e00a190
       master_info_file: /usr/local/mysql/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: 201022 09:31:29
        master_ssl_crl: 
      master_ssl_crlpath: 
      retrieved_gtid_set: fea89052-11ef-11eb-b241-00163e00a190:8-5617
      executed_gtid_set: a56c9b04-11f1-11eb-a855-00163e128853:1-11224,
fea89052-11ef-11eb-b241-00163e00a190:1-5614
        auto_position: 1
     replicate_rewrite_db: 
         channel_name: 
      master_tls_version: 
1 row in set (0.01 sec)

可以看到 slave_sql_running 为 no,表示运行取回的二进制日志出了问题;
在 last_error 中也可以看到大概的报错;(因为我之前的操作,大概可以判断出 是因为主库的二进制日志中有创建code库的sql,而从库上我已经创建了这个库,应该是产生了冲突;)

解决方法:

1、如果清楚自己之前的操作,可以将从库中产生冲突的库删除;
2、或者通过跳过gtid报错的事务的方法

--- 通过 last_sql_errno 报错编号查询具体的报错事务
mysql> select * from performance_schema.replication_applier_status_by_worker where last_error_number=1007\g
*************************** 1. row ***************************
     channel_name: 
      worker_id: 0
      thread_id: null
    service_state: off
last_seen_transaction: fea89052-11ef-11eb-b241-00163e00a190:5615
  last_error_number: 1007
  last_error_message: error 'can't create database 'code'; database exists' on query. default database: 'code'. query: 'create database code'
 last_error_timestamp: 2020-10-22 09:31:29
1 row in set (0.00 sec)

mysql> stop slave;
query ok, 0 rows affected (0.00 sec)

--- 跳过查找到报错的事务(last_seen_transaction 的值)
mysql> set @@session.gtid_next='fea89052-11ef-11eb-b241-00163e00a190:5615';
query ok, 0 rows affected (0.00 sec)

mysql> begin;
query ok, 0 rows affected (0.00 sec)

--- 提交一个空的事务,因为设置gtid_next后,gtid的生命周期开始了,必须通过显性的提交一个事务来结束;
mysql> commit;
query ok, 0 rows affected (0.00 sec)

--- 设置回自动模式;
mysql> set @@session.gtid_next=automatic;
query ok, 0 rows affected (0.00 sec)

mysql> start slave;
query ok, 0 rows affected (0.00 sec)

通过以上步骤,就跳过了这次的gtid报错的事务,如果 start slave 之后还是有报错,那么就按照此步骤继续跳过;

经验丰富的话,基本不用查询事务,通过 executed_gtid_set 就可以判断出报错的事务是 fea89052-11ef-11eb-b241-00163e00a190:5615 了;因为执行事务,到 fea89052-11ef-11eb-b241-00163e00a190:1-5614 的时候报错了,应该可以判断是 5615事务出现的错误;

以上就是mysql 主从复制如何跳过报错的详细内容,更多关于mysql 跳过报错的资料请关注其它相关文章!