MySQL 多源复制配置

多源复制可以解决多个主库实例同步到同一个从库实例的场景
以 MySQL8.0 为例，MySQL 5.7 中存在差异的部分后续补充说明

说明

准备三台实例，部署MySQL
- db1 db2 模拟两台单独的主库，db3模拟 db1/db2 的从库

hostname	IP	port
db1	172.17.191.106	6033
db2	172.17.191.108	6033
db3	172.17.191.107	6033

准备测试数据

db1

db1 创建测试库表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 建库
CREATE DATABASE `db1`;
-- 切换库
use `db1`;
-- 建标
CREATE TABLE test_tbl1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建存储过程

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


DELIMITER //

CREATE PROCEDURE generate_test_tbl1(IN num_records INT)
BEGIN
    DECLARE i INT DEFAULT 0;

    WHILE i < num_records DO
        INSERT INTO test_tbl1 (name, age)
        VALUES (CONCAT('User', i + 1), FLOOR(RAND() * 100));
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

执行存储过程，写入 2w 条数据

1

CALL generate_test_tbl1(20000);

检查

1
2
3


select count(*) from test_tbl1 \G
*************************** 1. row ***************************
count(*): 20000

db2

db2 创建测试库表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 建库
CREATE DATABASE `db2`;
-- 切换库
use `db2`;
-- 建标
CREATE TABLE test_tbl2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建存储过程

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


DELIMITER //

CREATE PROCEDURE generate_test_tbl2(IN num_records INT)
BEGIN
    DECLARE i INT DEFAULT 0;

    WHILE i < num_records DO
        INSERT INTO test_tbl2 (name, age)
        VALUES (CONCAT('User', i + 1), FLOOR(RAND() * 100));
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

执行存储过程，写入 2w 条数据

1

CALL generate_test_tbl2(20000);

检查

1
2
3


select count(*) from test_tbl2\G
*************************** 1. row ***************************
count(*): 20000

db3 同步数据

由于是多个主库实例同步至一个从库实例，仅第一个实例的数据可通过物理备份导入，其他数据库实例数据仅能通过逻辑备份倒入
由于当前数据并不多，通过逻辑备份演示，物理备份、恢复过程可参考其他文档

备份 db1 数据

备份数据

1

/data/mysql80/mysql8.0/bin/mysqldump  --routines --triggers --events -flush-logs  --single-transaction --set-gtid-purged=ON --default-character-set=utf8 --skip-add-drop-table  -u dump_user -p -h 127.0.0.1 -P6033 --compact -B db1 > db1-db1.sql

发送数据至 db3

1

scp db1-db1.sql  mydba@172.17.191.107:

备份 db2 数据

备份数据

1

/data/mysql80/mysql8.0/bin/mysqldump  --routines --triggers --events -flush-logs  --single-transaction --set-gtid-purged=ON --default-character-set=utf8 --skip-add-drop-table  -u dump_user -p -h 127.0.0.1 -P6033 --compact -B db2 > db2-db2.sql

发送数据至 db3

1

scp db2-db2.sql  mydba@172.17.191.107:

db3 恢复数据

db3 导入 db1、db2 数据

1
2
3
4


# 导入 db1 数据
/data/mysql80/mysql8.0/bin/mysql -uroot -p25241230 --socket=/data/mysql80/mysql_data/data/mysql_6033.sock < db1-db1.sql
# 导入 db2 数据
/data/mysql80/mysql8.0/bin/mysql -uroot -p25241230 --socket=/data/mysql80/mysql_data/data/mysql_6033.sock < db2-db2.sql

检查 GTID 状态，发现除自己的位置为 1 的 GTID 位置外，也增加了 db1/db2 同步的数据的 GTID 位置

1
2
3
4
5
6
7
8
9


mysql> show master status  \G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 492
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: b229a05e-7800-11ef-9b8f-00163e2c2c42:1-20013,
cc53e1e9-7800-11ef-88e6-00163e12db10:1-20005,
e5bddec7-7800-11ef-848f-00163e16dcc6:1

检查导入的库表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> select count(*) from db1.test_tbl1;
+----------+
| count(*) |
+----------+
|    20000 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from db2.test_tbl2;
+----------+
| count(*) |
+----------+
|    20000 |
+----------+
1 row in set (0.00 sec)

配置主从

db1、db2 写入新数据

db1、db2 再次调用存储过程，生成一部分数据，用于建立主从后测试从库是否同步数据

1
2
3
4
5


-- db1
mysql> CALL generate_test_tbl1(20000);
mysql> select count(*) from test_tbl1\G
*************************** 1. row ***************************
count(*): 40000

1
2
3
4
5


-- db2
mysql> CALL generate_test_tbl2(20000);
mysql> select count(*) from test_tbl2 \G
*************************** 1. row ***************************
count(*): 40000

db1、db2 创建同步用户

db1、db2 创建主从同步用户
**注意！不要再db1/db2这些要同步数据的库下执行创建用户！！！**原因可看文末部分

1
2
3


-- db1 db2 均执行
create user 'sync_user'@'%' identified by 'slaveBkPass';
GRANT SELECT, RELOAD, PROCESS, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT,REPLICATION SLAVE, EVENT ON *.* TO 'sync_user'@'%' ;

建立同步关系

db3 建立与 db1/db2 的同步关系

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


-- 建立与 db1 的同步关系
change master to MASTER_HOST='172.17.191.106',
MASTER_PORT=6033,
MASTER_USER='sync_user', 
MASTER_PASSWORD='slaveBkPass', 
master_auto_position=1 , 
get_master_public_key=1 
for channel 'db1_172.17.191.106';

-- 建立与 db2 的同步关系
change master to MASTER_HOST='172.17.191.108',
MASTER_PORT=6033,
MASTER_USER='sync_user', 
MASTER_PASSWORD='slaveBkPass', 
master_auto_position=1 , 
get_master_public_key=1 
for channel 'db2_172.17.191.108';

配置同步过滤

db3利用复制过滤仅处理db1、db2数据，忽略其他表数据
- 针对主库来的数据，忽略 mysql 表
- 创建针对 db1 的过滤规则，假定从 db1 同步数据的 channel 为 db1_172.17.191.106，需要和后面建立同步是创建的 channel 一致
- 创建针对 db2 的过滤规则，假定从 db2 同步数据的 channel 为 db2_172.17.191.108，需要和后面建立同步是创建的 channel 一致

1
2
3
4
5
6


-- 针对所有主库忽略 mysql 表
CHANGE REPLICATION FILTER Replicate_Ignore_DB=(mysql);
-- 创建针对 db1 的过滤规则
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1) for channel 'db1_172.17.191.106';
-- 创建针对 db2 的过滤规则
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db2) for channel 'db2_172.17.191.108';

同步更改配置文件，否则重启 mysql 时会丢失配置

1
2
3
4


[mysqld]
replicate_ignore_db=mysql
replicate-do-db=db1_172.17.191.106:db1
replicate-do-db=db2_172.17.191.108:db2

开启主从同步

开启 channel db1_172.17.191.106

1

start slave for channel 'db1_172.17.191.106';

查看状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46


mysql> show slave status for channel 'db1_172.17.191.106' \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.17.191.106
                  Master_User: sync_user
                  Master_Port: 6033
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 16941513
               Relay_Log_File: relay-bin-db1_172@002e17@002e191@002e106.000002
                Relay_Log_Pos: 8469946
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: db1
          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: 16941513
              Relay_Log_Space: 8470181
             ...............
             Master_Server_Id: 1726912985
                  Master_UUID: b229a05e-7800-11ef-9b8f-00163e2c2c42
             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: b229a05e-7800-11ef-9b8f-00163e2c2c42:20014-40015
            Executed_Gtid_Set: b229a05e-7800-11ef-9b8f-00163e2c2c42:1-40015,
cc53e1e9-7800-11ef-88e6-00163e12db10:1-20005,
e5bddec7-7800-11ef-848f-00163e16dcc6:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: db1_172.17.191.106
             ...............

开启 channel db1_172.17.191.106

1

start slave for channel 'db2_172.17.191.108';

查看状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46


mysql> show slave status for channel 'db2_172.17.191.108' \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.17.191.108
                  Master_User: sync_user
                  Master_Port: 6033
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 16940043
               Relay_Log_File: relay-bin-db2_172@002e17@002e191@002e108.000002
                Relay_Log_Pos: 6101274
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: db2
          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: 14571372
              Relay_Log_Space: 8470180
             ...............
             Master_Server_Id: 1726913029
                  Master_UUID: cc53e1e9-7800-11ef-88e6-00163e12db10
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: waiting for handler commit
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: cc53e1e9-7800-11ef-88e6-00163e12db10:20006-40007
            Executed_Gtid_Set: b229a05e-7800-11ef-9b8f-00163e2c2c42:1-40015,
cc53e1e9-7800-11ef-88e6-00163e12db10:1-34420,
e5bddec7-7800-11ef-848f-00163e16dcc6:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: db2_172.17.191.108
             ...............

验证

查询 db1 db2 内数据可知已同步后续数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


mysql> select count(*) from db1.test_tbl1;
+----------+
| count(*) |
+----------+
|    40000 |
+----------+

mysql> select count(*) from db2.test_tbl2;
+----------+
| count(*) |
+----------+
|    40000 |
+----------+

补充：MySQL 5.7 配置多源复

说明

MySQL5.7 服务器 IP 不变，端口为 3306
MySQL5.7 配置和 MySQL8.0 类似，存在如下差异
- 主从同步用户，权限部分和 MySQL8.0 存在区别
- 与主库建立同关系部分存在差异，由于 MySQL5.7 不支持追加 GTID，仅能通过 binlog + position id 的方式建立主从
- 配置过滤时 MySQL5.7 不支持针对单个 channel 设置，过滤规则对所有 channel 生效

准备测试数据

准备测试数据部分与 mysql8.0 一致，命令及存储过程可复用，略。
检查

1
2
3
4


-- db1
mysql> select count(*) from db1.test_tbl1 \G
*************************** 1. row ***************************
count(*): 20000

1
2
3
4


-- db2
mysql> select count(*) from db2.test_tbl2 \G
*************************** 1. row ***************************
count(*): 20000

db3 同步数据

db1 导出数据
- –master-data=2：将备份时日志位置以注释方式写入备份文件中

1

/data/mysql57/mysql5.7/bin/mysqldump  --routines --triggers --events -flush-logs  --single-transaction --set-gtid-purged=OFF --default-character-set=utf8 --skip-add-drop-table  -u root -p  --socket=/data/mysql57/mysql_data/data/mysql_3306.sock --master-data=2 --compact -B db1 > db1-db1.sql

发送备份文件至 db3

1

scp db1-db1.sql  mydba@172.17.191.107:

db2 导出数据

1

/data/mysql57/mysql5.7/bin/mysqldump  --routines --triggers --events -flush-logs  --single-transaction --set-gtid-purged=OFF --default-character-set=utf8 --skip-add-drop-table  -u root -p  --socket=/data/mysql57/mysql_data/data/mysql_3306.sock --master-data=2 --compact -B db2 > db2-db2.sql

发送备份文件至 db3

1

scp db2-db2.sql  mydba@172.17.191.107:

db3 导入数据

1
2


/data/mysql57/mysql5.7/bin/mysql -uroot -p25241230 --socket=/data/mysql57/mysql_data/data/mysql_3306.sock < db1-db1.sql
/data/mysql57/mysql5.7/bin/mysql -uroot -p25241230 --socket=/data/mysql57/mysql_data/data/mysql_3306.sock < db2-db2.sql

建立同步关系

创建同步用户

db1/db2 创建同步用户，db1/db2 均执行

1

GRANT SELECT, RELOAD, PROCESS, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT,REPLICATION SLAVE, EVENT ON *.* TO 'sync_user'@'%' identified by 'slaveBkPass';

db1建立同步关系

查看 db1 备份文件 binlog 位置

1
2


$ cat db1-db1.sql |grep 'CHANGE MASTER'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=5630495;

db3 建立建立与 db1 的同步关系

1
2
3
4
5
6
7
8


-- 建立与 db1 的同步关系
change master to MASTER_HOST='172.17.191.106',
MASTER_PORT=3306,
MASTER_USER='sync_user', 
MASTER_PASSWORD='slaveBkPass', 
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=5630495 
for channel 'db1_172.17.191.106';

db2建立同步关系

查看 db2 备份文件 binlog 位置
- 此处 MASTER_LOG_FILE 和 MASTER_LOG_POS 与 db1 的一样是巧合，因为都是新库且都写入 2w 条数据

1
2


$ cat db2-db2.sql |grep 'CHANGE MASTER'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=5630495;

db3 建立建立与 db2 的同步关系

1
2
3
4
5
6
7
8


-- 建立与 db2 的同步关系
change master to MASTER_HOST='172.17.191.108',
MASTER_PORT=3306,
MASTER_USER='sync_user', 
MASTER_PASSWORD='slaveBkPass', 
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=5630495 
for channel 'db2_172.17.191.108';

配置过滤规则

MySQL 过滤规则不支持对 channel 配置，对所有 channel 生效。

1
2
3
4


-- 针对所有主库忽略 mysql 表
CHANGE REPLICATION FILTER Replicate_Ignore_DB=(mysql);
-- 创建过滤规则，仅处理 db1、db2 数据
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1,db2);

同步更改配置文件，否则重启 mysql 时会丢失配置

1
2
3
4


[mysqld]
replicate_ignore_db=mysql
replicate-do-db=db1
replicate-do-db=db2

开启主从同步

开启 channel db1_172.17.191.106

1

start slave for channel 'db1_172.17.191.106';

查看状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45


mysql> show slave status for channel 'db1_172.17.191.106' \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.191.106
                  Master_User: sync_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 11260298
               Relay_Log_File: relay-bin-db1_172@002e17@002e191@002e106.000002
                Relay_Log_Pos: 1928647
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: db1,db2
          Replicate_Ignore_DB: mysql
           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: 7558822
              Relay_Log_Space: 5630355
             ...............
             Master_Server_Id: 1726979719
                  Master_UUID: 1fe6fc36-789c-11ef-bca5-00163e0ce5ac
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 1fe6fc36-789c-11ef-bca5-00163e0ce5ac:20007-40009
            Executed_Gtid_Set: 1fe6fc36-789c-11ef-bca5-00163e0ce5ac:20007-26872,
33f6a3f8-789c-11ef-bbee-00163e0a77d2:1-9
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: db1_172.17.191.106
           Master_TLS_Version: 

开启 channel db2_172.17.191.108

1

start slave for channel 'db2_172.17.191.108';

查看状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46


mysql> show slave status for channel 'db2_172.17.191.108' \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.191.108
                  Master_User: sync_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 5630864
               Relay_Log_File: relay-bin-db2_172@002e17@002e191@002e108.000002
                Relay_Log_Pos: 689
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: db1,db2
          Replicate_Ignore_DB: mysql
           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: 5630864
              Relay_Log_Space: 921
             ...............
             Master_Server_Id: 1726979736
                  Master_UUID: 2a7a73ef-789c-11ef-b7d4-00163e0e59d7
             Master_Info_File: mysql.slave_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: 2a7a73ef-789c-11ef-b7d4-00163e0e59d7:20007
            Executed_Gtid_Set: 1fe6fc36-789c-11ef-bca5-00163e0ce5ac:20007-40009,
2a7a73ef-789c-11ef-b7d4-00163e0e59d7:20007,
33f6a3f8-789c-11ef-bbee-00163e0a77d2:1-9
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: db2_172.17.191.108
           Master_TLS_Version: 

验证

db1 db2 调用存储过程，继续生成数据

1
2
3


-- db1
use db1;
CALL generate_test_tbl1(40000);

1
2
3


-- db2
use db2;
CALL generate_test_tbl2(20000);

db3 查询同步后的数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


mysql> select count(*) from db1.test_tbl1;
+----------+
| count(*) |
+----------+
|    60000 |
+----------+

mysql> select count(*) from db2.test_tbl2;
+----------+
| count(*) |
+----------+
|    40000 |
+----------+

已知存在的问题

在同步的 DB 内创建用户会导致主从异常

环境：8.0.37
报错信息

1

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'cc53e1e9-7800-11ef-88e6-00163e12db10:40006' at source log mysql-bin.000002, end_log_pos 16939732. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

查看主节点对应报错信息，是在 db2 内执行 create user 导致。
- 由于是在 db2 下执行的 CREATE USER，binlog 内 CREATE USER 前拼接了 use db2，导致从库过滤 binlog 出错，从库判断要执行这条 binlog；而 db1，db2 都创建了同名用户，导致第二个库同步过来的 CREATE USER 语句出错。

1
2


mysql> SHOW BINLOG EVENTS in 'mysql-bin.000002';
| mysql-bin.000002 | 16939491 | Query          | 1726913029 |    16939732 | use `db2`; CREATE USER 'sync_user'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$fs;PNs/%r~!ucabfCoSwEPcaw0EP7xVQlUs5xaQ8oNqV.mOQi0kL4t.' /* xid=120083 */                                                                                                                                                    |

MySQL5.7 GTID 模式导入备份时无法指定 GTID 位置

报错信息

1
2


mysql> SET @@GLOBAL.GTID_PURGED='1fe6fc36-789c-11ef-bca5-00163e0ce5ac:1-20006';
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

问题说明
- MySQL 8.0 重复执行 SET @@GLOBAL.GTID_PURGED 效果类似于追加
- 而 MySQL 5.7 上重复执行，仅能覆盖，因此多源复制场景下，无法追加多个主库的 GTID
- 因此只能通过老版本的 binlog file + position id 的模式建立主从同步关系

Mysql 多源复制

文章目录

MySQL 多源复制配置

说明

准备测试数据

db1

db2

db3 同步数据

备份 db1 数据

备份 db2 数据

db3 恢复数据

配置主从

db1、db2 写入新数据

db1、db2 创建同步用户

建立同步关系

配置同步过滤

开启主从同步

验证

补充：MySQL 5.7 配置多源复

说明

准备测试数据

db3 同步数据

建立同步关系

创建同步用户

db1建立同步关系

db2建立同步关系

配置过滤规则

开启主从同步

验证

已知存在的问题

在同步的 DB 内创建用户会导致主从异常

MySQL5.7 GTID 模式导入备份时无法指定 GTID 位置