Mysql - 多源复制

一、说明

我们使用mysql大多数都是一主一从或者是一主多从的架构，但在有些情况下我们希望能将多个主库同步到一个从库下面，本文将介绍多主一从的安装方法以及注意事项。
实验环境为：3台Centos7.6，mysql 8.0.28 community
192.168.56.91 主1
192.168.56.92 主2
192.168.56.93 从

二、搭建

1. 分别在3个节点上面安装mysql

这里需要注意的是所有节点的mysql的server-id不能重复。

2. 记录两个主库的position

我这里使用日志文件名称和偏移量来搭建，也可以使用gtjd，关于gtid搭建从库的方式可以参考官方文档。

([email protected])[(none)]> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      812 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

([email protected])[(none)]> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |     1044 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

3. 在两个主上分别创建同步用户

这里的同步用户建议用户名不要重复，具体原因后面再讲。

([email protected])[(none)]> create user 'repl'@'%' identified by 'repl';
([email protected])[(none)]> grant replication slave, replication client on *.* to 'repl'@'%';

([email protected])[(none)]> create user 'repl2'@'%' identified by 'repl';
([email protected])[(none)]> grant replication slave, replication client on *.* to 'repl2'@'%';

4. 同步主库

([email protected])[(none)]> change master to master_host='192.168.56.91',master_port=3306,master_user='repl',master_password='repl',master_log_file='binlog.000003',master_log_pos=812 for channel 'source_91';
([email protected])[(none)]> change master to master_host='192.168.56.92',master_port=3306,master_user='repl2',master_password='repl',master_log_file='binlog.000003',master_log_pos=1044 for channel 'source_92';

([email protected])[(none)]> start slave for channel 'source_91';
([email protected])[(none)]> start slave for channel 'source_92';
([email protected])[(none)]> show slave for channel 'source_91';
([email protected])[(none)]> show slave for channel 'source_92';

对于多个源，我们需要通过for channel 'xx'来指定源，不指定就是所有源，例如show slave就会将所有源的信息显示出来。

5. 主库创建数据库

([email protected])[(none)]> create database hello;  // 91 创建hello

([email protected])[(none)]> create database world;  // 92 创建world

([email protected])[(none)]> show databases;  // 93 hello和world都有了
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+

可以看到主库91和92上面分别创建的数据库在93上面都看见了，现在有个问题来了，假设92上面再创建一个数据库hello，会是什么情形？

([email protected])[(none)]> create database hello;

([email protected])[(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.56.91
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 1515
               Relay_Log_File: relaylog-source_91.000003
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.56.92
                  Master_User: repl2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 1937
               Relay_Log_File: relaylog-source_92.000002
                Relay_Log_Pos: 1028
        Relay_Master_Log_File: binlog.000003
             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: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log binlog.000003, end_log_pos 1937. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1749
              Relay_Log_Space: 1429
              Until_Condition: None

可以看到92的同步断开了，这是由于在从库中已经有了hello这个数据库，再增加一个hello数据库当然不行，因此报错。所以前面的同步用户也尽量不要重复，除非开始同步的点在创建用户之后。

三、适用场景

mysql的多源复制就是将所有源的binlog传递给备库执行，那么为了避免出现同步失败的情况，我们可以限定只对源库的某些schema进行同步。

([email protected])[(none)]> change replication filter replicate_wild_do_table = ('hello.%','test.%') for channel 'source_91';
([email protected])[(none)]> change replication filter replicate_wild_do_table = ('world.%') for channel 'source_92';

mysql的多源复制如果是同schema下的同名表，需要注意在同步的过程中，一些主键，唯一键和自增序列的相关问题。