MySQL主从复制重新初始化单表或者单库的方法

老苏畅谈运维

已于 2024-08-23 22:23:08 修改

阅读量187

点赞数 4

分类专栏： MySQL 故障诊断文章标签： mysql 数据库主从

于 2024-08-23 21:55:58 首次发布

文章来源于老苏畅谈运维，欢迎您转载，转载声明来源老苏畅谈运维

本文链接：https://blog.csdn.net/zhirongsu/article/details/141475477

版权

MySQL 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

故障诊断

3 篇文章 0 订阅

订阅专栏

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）
公众号：老苏畅谈运维
欢迎关注本人公众号，更多精彩与您分享。

背景说明

在很多情况下，由于一张表或几张表的数据不一致导致主从复制中断，或者在搭建从库时，发现漏掉了某些库或者表。如果数据量较大，重新搭建一个从库耗时会较长。那么有没有办法只针对有问题的表或者漏掉的库重新初始化，从而恢复主从同步呢？

当然是有方法的，针对表的恢复操作有以下几种可行的方式：

在主库锁定这张表做可传输表空间还原到从库
在主库锁定这张表导出数据导入到从库
使用备份恢复工具实现从库同步恢复

前面2种方法需要在导出数据过程中，需要进行锁表，考虑到对生产的影响尽可能减少，选择第三种方法来进行恢复。

环境说明

IP	角色	需初始的表
192.168.6.13	主库	sbtest库下的sbtest1、sbtest2
192.168.6.14	从库	sbtest库下的sbtest1、sbtest2

表的初始化恢复步骤

模拟从库出现表不一致

对主库进行加压测试数据

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.6.13 --mysql-port=3306 --mysql-user=root --mysql-password='root@MySQL888' --mysql-db=sbtest --db-driver=mysql \
--tables=10 --table-size=1000 --report-interval=10 --threads=4 --time=600 --db-ps-mode=disable --max-requests=0 --percentile=95  run

加压过程中，删除从库的表sbtest1和sbtest2

(root@localhost)[sbtest]20:00>show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest10         |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
| sbtest6          |
| sbtest7          |
| sbtest8          |
| sbtest9          |
+------------------+
10 rows in set (0.00 sec)

(root@localhost)[sbtest]20:01>drop table sbtest1;
Query OK, 0 rows affected (0.02 sec)

(root@localhost)[sbtest]20:01>drop table sbtest2;
Query OK, 0 rows affected (0.00 sec)

从库同步报错

--同步状态

(root@localhost)[sbtest]20:01>show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.6.13
                  Master_User: svr_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000038
          Read_Master_Log_Pos: 103341043
               Relay_Log_File: relay.000087
                Relay_Log_Pos: 8172423
        Relay_Master_Log_File: binlog.000038
             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: 1146
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction '86f14188-54fe-11ed-9f7a-0242c0a8060d:936591' at master log binlog.000038, end_log_pos 88970909. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

--日志显示找不到表sbtest.sbtest1
2024-08-23T20:01:31.543732+08:00 114 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 2 failed executing transaction '86f14188-54fe-11ed-9f7a-0242c0a8060d:936590' at master log binlog.000038, end_log_pos 88968604; Error executing row event: 'Table 'sbtest.sbtest1' doesn't exist', Error_code: MY-001146
2024-08-23T20:01:31.544719+08:00 115 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 3 failed executing transaction '86f14188-54fe-11ed-9f7a-0242c0a8060d:936591' at master log binlog.000038, end_log_pos 88970909; Error executing row event: 'Table 'sbtest.sbtest1' doesn't exist', Error_code: MY-001146
2024-08-23T20:01:31.545923+08:00 112 [Warning] [MY-010584] [Repl] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: MY-001756

从库停止复制进程

(root@localhost)[sbtest]20:03>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost)[sbtest]20:03>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.6.13
                  Master_User: svr_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000038
          Read_Master_Log_Pos: 198568806
               Relay_Log_File: relay.000087
                Relay_Log_Pos: 8172423
        Relay_Master_Log_File: binlog.000038
             Slave_IO_Running: No
            Slave_SQL_Running: No

主库单独备份有问题的表（一致性备份）

一致性备份

mysqldump -uroot -proot@MySQL888 -h 192.168.6.13 --single-transaction --master-data=2 --flush-logs sbtest sbtest1 sbtest2 > t.sql

查看备份文件记录的pos、gtid信息

cat t.sql 

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '86f14188-54fe-11ed-9f7a-0242c0a8060d:1-994227';

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000039', MASTER_LOG_POS=196;

从库导入数据

(root@localhost)[sbtest]20:04>show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest10         |
| sbtest3          |
| sbtest4          |
| sbtest5          |
| sbtest6          |
| sbtest7          |
| sbtest8          |
| sbtest9          |
+------------------+
8 rows in set (0.00 sec)

(root@localhost)[sbtest]20:05>source t.sql
(root@localhost)[sbtest]20:05>show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest10         |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
| sbtest6          |
| sbtest7          |
| sbtest8          |
| sbtest9          |
+------------------+
10 rows in set (0.00 sec)

从库开启复制过滤

设置复制过滤，并指定UNTIL SQL_BEFORE_GTIDS

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=('sbtest.sbtest1','sbtest.sbtest2');

## UNTIL SQL_BEFORE_GTIDS指定的GTID值为备份文件中的GTID值+1
## 86f14188-54fe-11ed-9f7a-0242c0a8060d:994227 +1
## 注意指定结束点即可,无需指定开始点（不需要1-994228这种形式，只要994228）
start slave UNTIL SQL_BEFORE_GTIDS='86f14188-54fe-11ed-9f7a-0242c0a8060d:994228';

--start slave  SQL_THREAD  UNTIL   SQL_BEFORE_GTIDS='86f14188-54fe-11ed-9f7a-0242c0a8060d:994228'

或通过指定pos信息
start slave until MASTER_LOG_FILE='binlog.000039',MASTER_LOG_POS=196;

(root@localhost)[(none)]20:09>CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=('sbtest.sbtest1','sbtest.sbtest2');
Query OK, 0 rows affected (0.00 sec)

(root@localhost)[(none)]20:09>show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.6.13
                  Master_User: svr_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000038
          Read_Master_Log_Pos: 198568806
               Relay_Log_File: relay.000087
                Relay_Log_Pos: 8172423
        Relay_Master_Log_File: binlog.000038
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: sbtest.sbtest1,sbtest.sbtest2

(root@localhost)[(none)]20:09>start slave UNTIL SQL_BEFORE_GTIDS='86f14188-54fe-11ed-9f7a-0242c0a8060d:994228';
Query OK, 0 rows affected, 1 warning (0.01 sec)

(root@localhost)[(none)]20:10>show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.6.13
                  Master_User: svr_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000039
          Read_Master_Log_Pos: 122237428
               Relay_Log_File: relay.000090
                Relay_Log_Pos: 405
        Relay_Master_Log_File: binlog.000039
             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: sbtest.sbtest1,sbtest.sbtest2
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 196
              Relay_Log_Space: 122237920
              Until_Condition: SQL_BEFORE_GTIDS
               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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 330613
                  Master_UUID: 86f14188-54fe-11ed-9f7a-0242c0a8060d
             Master_Info_File: mysql.slave_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: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 86f14188-54fe-11ed-9f7a-0242c0a8060d:634558-1047246
            Executed_Gtid_Set: 86f14188-54fe-11ed-9f7a-0242c0a8060d:1-994227,
871fa289-54fe-11ed-9b0e-0242c0a8060e:1-20
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)


###可以看到同步这个gtid：86f14188-54fe-11ed-9f7a-0242c0a8060d:1-994227 就自动停止。

取消过滤,重新开启复制

待数据同步到GTID:994227点后会自动停止,重新配置过滤规则取消后复制恢复正常

stop slave;
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=();
start slave;

(root@localhost)[(none)]20:11>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost)[(none)]20:11>CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=();
Query OK, 0 rows affected (0.00 sec)

(root@localhost)[(none)]20:11>start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

(root@localhost)[(none)]20:12>show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.6.13
                  Master_User: svr_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000039
          Read_Master_Log_Pos: 122238144
               Relay_Log_File: relay.000091
                Relay_Log_Pos: 807
        Relay_Master_Log_File: binlog.000039
             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: 122238144
              Relay_Log_Space: 122238845
              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: 330613
                  Master_UUID: 86f14188-54fe-11ed-9f7a-0242c0a8060d
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 86f14188-54fe-11ed-9f7a-0242c0a8060d:634558-1047248
            Executed_Gtid_Set: 86f14188-54fe-11ed-9f7a-0242c0a8060d:1-1047248,
871fa289-54fe-11ed-9b0e-0242c0a8060e:1-20
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

库的初始化恢复步骤

针对某个库的初始化和表的初始化，步骤大体相似（不同地方就是在导出的时候针对库导出，后面用 CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(sbtest) 来进行过滤库）。

模拟从库出现库不一致

对主库进行加压测试数据

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.6.13 --mysql-port=3306 --mysql-user=root --mysql-password='root@MySQL888' --mysql-db=sbtest --db-driver=mysql \
--tables=10 --table-size=1000 --report-interval=10 --threads=4 --time=600 --db-ps-mode=disable --max-requests=0 --percentile=95  run

加压过程中，删除从库的sbtest库

(root@localhost)[(none)]21:15>show databases;
+-----------------------+
| Database              |
+-----------------------+
| 192_168_6_13_3306_aaa |
| aaa                   |
| inception             |
| information_schema    |
| mydb                  |
| mysql                 |
| ora_backup            |
| performance_schema    |
| sbtest                |
| sys                   |
+-----------------------+
10 rows in set (0.00 sec)

(root@localhost)[(none)]21:16>drop database sbtest;
Query OK, 10 rows affected (0.08 sec)

(root@localhost)[(none)]21:16>show databases;
+-----------------------+
| Database              |
+-----------------------+
| 192_168_6_13_3306_aaa |
| aaa                   |
| inception             |
| information_schema    |
| mydb                  |
| mysql                 |
| ora_backup            |
| performance_schema    |
| sys                   |
+-----------------------+
9 rows in set (0.01 sec)

从库同步报错

(root@localhost)[(none)]21:17>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.6.13
                  Master_User: svr_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000040
          Read_Master_Log_Pos: 67009395
               Relay_Log_File: relay.000091
                Relay_Log_Pos: 11750613
        Relay_Master_Log_File: binlog.000039
             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: 1049
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '86f14188-54fe-11ed-9f7a-0242c0a8060d:1052495' at master log binlog.000039, end_log_pos 134212310. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

2024-08-23T21:16:11.664410+08:00 129 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 3 failed executing transaction '86f14188-54fe-11ed-9f7a-0242c0a8060d:1052497' at master log binlog.000039, end_log_pos 134216920; Error executing row event: 'Unknown database 'sbtest'', Error_code: MY-001049
2024-08-23T21:16:11.664722+08:00 128 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 2 failed executing transaction '86f14188-54fe-11ed-9f7a-0242c0a8060d:1052496' at master log binlog.000039, end_log_pos 134214615; Error executing row event: 'Unknown database 'sbtest'', Error_code: MY-001049
2024-08-23T21:16:11.672727+08:00 127 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction '86f14188-54fe-11ed-9f7a-0242c0a8060d:1052495' at master log binlog.000039, end_log_pos 134212310; Error executing row event: 'Unknown database 'sbtest'', Error_code: MY-001049
2024-08-23T21:16:11.675160+08:00 126 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000039' position 133987950

从库停止复制进程

(root@localhost)[(none)]21:19>stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

主库备份有问题的库（一致性备份）

mysqldump -uroot -proot@MySQL888 -h 192.168.6.13 --single-transaction --master-data=2 --flush-logs sbtest  > db.sql

查看备份文件记录的pos、gtid信息

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '86f14188-54fe-11ed-9f7a-0242c0a8060d:1-1064457';

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000040', MASTER_LOG_POS=196;

从库导入数据

(root@localhost)[(none)]21:22>create database sbtest;
Query OK, 1 row affected (0.02 sec)

(root@localhost)[(none)]21:22>use sbtest;
Database changed
(root@localhost)[sbtest]21:22>source db.sql;
(root@localhost)[sbtest]21:23>show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest10         |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
| sbtest6          |
| sbtest7          |
| sbtest8          |
| sbtest9          |
+------------------+
10 rows in set (0.00 sec)

从库开启复制过滤

设置复制过滤，并指定UNTIL SQL_BEFORE_GTIDS

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(sbtest);

## UNTIL SQL_BEFORE_GTIDS指定的GTID值为备份文件中的GTID值+1
## 86f14188-54fe-11ed-9f7a-0242c0a8060d:1-1064457 +1
## 注意指定结束点即可,无需指定开始点（不需要1-1064457这种形式，只要1064458）
start slave UNTIL SQL_BEFORE_GTIDS='86f14188-54fe-11ed-9f7a-0242c0a8060d:1064458';

--start slave  SQL_THREAD  UNTIL   SQL_BEFORE_GTIDS='86f14188-54fe-11ed-9f7a-0242c0a8060d:1064458'

或通过指定pos信息
start slave until MASTER_LOG_FILE='binlog.000040',MASTER_LOG_POS=196;

(root@localhost)[sbtest]21:23>CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(sbtest);
Query OK, 0 rows affected (0.00 sec)

(root@localhost)[(none)]21:42>start slave UNTIL SQL_BEFORE_GTIDS='86f14188-54fe-11ed-9f7a-0242c0a8060d:1064458';
Query OK, 0 rows affected, 1 warning (0.01 sec)

(root@localhost)[(none)]21:43>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Queueing source event to the relay log
                  Master_Host: 192.168.6.13
                  Master_User: svr_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000040
          Read_Master_Log_Pos: 514040758
               Relay_Log_File: relay.000097
                Relay_Log_Pos: 405
        Relay_Master_Log_File: binlog.000040
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: sbtest
           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: 196
              Relay_Log_Space: 514041475
              Until_Condition: SQL_BEFORE_GTIDS
               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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 330613
                  Master_UUID: 86f14188-54fe-11ed-9f7a-0242c0a8060d
             Master_Info_File: mysql.slave_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: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 86f14188-54fe-11ed-9f7a-0242c0a8060d:1052495-1287353
            Executed_Gtid_Set: 86f14188-54fe-11ed-9f7a-0242c0a8060d:1-1064457,
871fa289-54fe-11ed-9b0e-0242c0a8060e:1-22
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)
###可以看到同步这个gtid：86f14188-54fe-11ed-9f7a-0242c0a8060d:1-1064457 就自动停止。

取消过滤，重新开启复制

待数据同步到GTID:1064457点后会自动停止,重新配置过滤规则取消后复制恢复正常

stop slave;
CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=();
start slave;

查看同步情况

(root@localhost)[(none)]21:50>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.6.13
                  Master_User: svr_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000040
          Read_Master_Log_Pos: 569068103
               Relay_Log_File: relay.000098
                Relay_Log_Pos: 1881
        Relay_Master_Log_File: binlog.000040
             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: 569068103
              Relay_Log_Space: 569068804
              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: 330613
                  Master_UUID: 86f14188-54fe-11ed-9f7a-0242c0a8060d
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 86f14188-54fe-11ed-9f7a-0242c0a8060d:1052495-1311285
            Executed_Gtid_Set: 86f14188-54fe-11ed-9f7a-0242c0a8060d:1-1311285,
871fa289-54fe-11ed-9b0e-0242c0a8060e:1-22
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

在这里插入图片描述

关注我，学习更多的数据库知识！
请添加图片描述

老苏畅谈运维

关注

4
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL主从复制重新初始化单表或者单库的方法

在很多情况下，由于一张表或几张表的数据不一致导致主从复制中断，或者在搭建从库时，发现漏掉了某些库或者表。那么有没有办法只针对有问题的表或者漏掉的库重新初始化，从而恢复主从同步呢？针对某个库的初始化和表的初始化，步骤大体相似（不同地方就是在导出的时候针对库导出，后面用 CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(sbtest) 来进行过滤库）。前面2种方法需要在导出数据过程中，需要进行锁表，考虑到对生产的影响尽可能减少，选择第三种方法来进行恢复。
复制链接

扫一扫