您的位置: 首页  >  IT文章  >  mysql多源复制，多主一从复制 配置约束 【主库mysql8-1】操作及配置 【主库mysql8-2】配置及操作 【从库mysql8-3】配置及操作 验证数据同步 【主库mysql8-1】操作及配置 【主库mysql8-2】操作及配置 【从库mysql8-3】操作及配置

mysql多源复制，多主一从复制 配置约束 【主库mysql8-1】操作及配置 【主库mysql8-2】配置及操作 【从库mysql8-3】配置及操作 验证数据同步 【主库mysql8-1】操作及配置 【主库mysql8-2】操作及配置 【从库mysql8-3】操作及配置

分类: IT文章 • 2023-12-17 18:24:19

阿里云ECS，公网IP 121.40.110.2 

使用docker安装mysql8，创建三个容器：

实例A：A数据库 A表

实例B：B数据库 B表

实例C：C数据库C表

两种情形：

第一种

 A库和B库同步到实例C；

第二种

A库A表和B库B表同步到C库

一、使用docker创建mysql8容器

1、安装docker

yum install docker -y
systemctl start docker
systemctl enable docker

2、拉取mysql镜像

docker pull mysql
docker images

mysql多源复制，多主一从复制 配置约束 【主库mysql8-1】操作及配置 【主库mysql8-2】配置及操作 【从库mysql8-3】配置及操作 验证数据同步 【主库mysql8-1】操作及配置 【主库mysql8-2】操作及配置 【从库mysql8-3】操作及配置

3、启动三个mysql容器mysql8-1、mysql8-2、mysql8-3

docker run --name mysql8-1  -p 3310:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest
docker run --name mysql8-2  -p 3311:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest
docker run --name mysql8-3  -p 3312:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest

4、进入mysql8-1容器（先不要建自己的datingcenter等后面测试的库）

docker exec -it mysql8-1 /bin/bash

连接数据库，查看数据库版本

mysql多源复制，多主一从复制 配置约束 【主库mysql8-1】操作及配置 【主库mysql8-2】配置及操作 【从库mysql8-3】配置及操作 验证数据同步 【主库mysql8-1】操作及配置 【主库mysql8-2】操作及配置 【从库mysql8-3】操作及配置

5、设置数据库远程连接

设置远程登录数据库的root账户

mysql8和mysql5的版本不一样，具体操作

GRANT ALL ON *.* TO 'root'@'%';

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';

FLUSH PRIVILEGES;

6、创建用户slave用于从库同步复制，授予复制、同步访问

mysql8和mysql5的版本不一样，具体操作

mysql> use mysql;

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

mysql>ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';


mysql> GRANT replication slave ON *.* TO 'slave'@'%';

mysql>FLUSH PRIVILEGES;

其他两个容器同样操作

容器安装编辑器vim

 https://www.cnblogs.com/xiaoyou2018/p/10912117.html

二、配置数据库多源一从

和mysql一主一从复制相比，多源复制加入了一个叫做Channel的概念， 每一个Channel都是一个独立的Slave，都有一个IO_THREAD和SQL_THREAD。原理和普通复制一样。我们只需要对每一个Master执行Change Master 语句，只需要在每个语句最后使用For Channel来进行区分。多源复制和正常主从其他的配置都一样，基本上主库开下binlog、server-id不一样就可以了，只有下列额外限制：

  • master-info-repository必须为TABLE
  • relay-log-info-repository必须为TABLE
  • 以FOR CHANNEL 'CHANNEL_NAME'区分不同的master。

第一种情形：

主库同步到从库的情况如下配置：

  • 主库mysql8-1：IP=121.40.110.2; PORT=3310; server-id=1; database=datingcenter； table=aa、dd
  • 主库mysql8-2：IP=121.40.110.2; PORT=3311; server-id=2; database=usercenter ；table=cc、bb
  • 从库mysql8-3：IP=121.40.110.2; PORT=3312; server-id=3; database=datingcenter、usercenter、matchcenter ；table=aa、dd、cc、bb
  • 主从库必须保证网络畅通可访问
  • 主库必须开启binlog日志
  • 主从库的server-id必须不同

【主库mysql8-1】操作及配置

配置my.cnf（/etc/mysql/my.cnf）

添加如下的内容

client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data #数据存储目录

##########################
# log bin
##########################
server-id = 1            #必须唯一
log_bin = mysql-bin     #开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7        #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M
#要同步的数据库
binlog-do-db = datingcenter 
#不需要同步的数据库
binlog-ignore-db = mysql 
binlog_ignore_db = information_schema 
binlog_ignore_db = performance_schema 
binlog_ignore_db = sys 
########################## # character set ########################## 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci

log_bin是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set

查看master状态

mysql> show master status G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 155
     Binlog_Do_DB: datingcenter
 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

 记住file、position这两个值，后面需要用到

【主库mysql8-2】配置及操作

配置my.cnf

添加如下的内容

client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data #数据存储目录

##########################
# log bin
##########################
server-id = 2            #必须唯一
log_bin = mysql-bin     #开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7        #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M
#要同步的数据库
binlog-do-db = usercenter 
#不需要同步的数据库
binlog-ignore-db = mysql 
binlog_ignore_db = information_schema 
binlog_ignore_db = performance_schema 
binlog_ignore_db = sys 
########################## # character set ########################## 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci

log_bin是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set

查看master状态

mysql> show master status G;
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 155
     Binlog_Do_DB: usercenter
 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
 

【从库mysql8-3】配置及操作

配置my.cnf

[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data 	#数据存储目录

##########################
# log bin
##########################
server-id = 3
master_info_repository      = table
relay_log_info_repository   = table		

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

　　

设置【主库】信息

登录【从库mysql8-3】，进入mysql命令行。

mysql> stop slave;
Query OK, 0 rows affected

mysql> CHANGE MASTER TO 
MASTER_HOST='121.40.110.2',
MASTER_PORT=3310,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=155 
for channel '1';
Query OK, 0 rows affected

mysql> CHANGE MASTER TO 
MASTER_HOST='121.40.110.2',
MASTER_PORT=3311,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=155 
for channel '2';
Query OK, 0 rows affected

mysql> start slave;
Query OK, 0 rows affected

　stop slave;     //停止同步
start slave;     //开始同步
//必须和【主库】的信息匹配。
CHANGE MASTER TO
MASTER_HOST='121.40.110.2             //主库IP

MASTER_PORT=3310,                       //主库端口
MASTER_USER='slave',                     //访问主库且有同步复制权限的用户
MASTER_PASSWORD='123456',      //登录密码
//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
MASTER_LOG_FILE='mysql-bin.000001',
//【关键处】从文件中指定位置开始读取，主库show master status返回结果
MASTER_LOG_POS=155
for channel '1';            //定义通道名称　

查看同步状态

mysql> show slave status G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 47.111.132.49
                  Master_User: slave
                  Master_Port: 3310
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 169442
               Relay_Log_File: efe0882a2350-relay-bin-1.000017
                Relay_Log_Pos: 169656
        Relay_Master_Log_File: mysql-bin.000006
             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: 169442
              Relay_Log_Space: 170040
              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: 1
                  Master_UUID: 399257c7-a933-11e9-8d35-0242ac110002
             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: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (datingCenter_0,matchCenter_0),(userCenter_0,matchCenter_0)
                 Channel_Name: 1
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 47.111.132.49
                  Master_User: slave
                  Master_Port: 3311
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 155
               Relay_Log_File: efe0882a2350-relay-bin-2.000019
                Relay_Log_Pos: 369
        Relay_Master_Log_File: mysql-bin.000006
             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: 155
              Relay_Log_Space: 753
              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: 2
                  Master_UUID: 39a3fc71-a933-11e9-ac19-0242ac110003
             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: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (datingCenter_0,matchCenter_0),(userCenter_0,matchCenter_0)
                 Channel_Name: 2
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
2 rows in set (0.01 sec)

ERROR: 
No query specified

可以看见设置两个个的主从同步通道的所有状态信息。
只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes，同步才是正常的。
如果是No或者Connecting都不行，可查看mysql-error.log，

可以重启容器试试或者等待一会　　

若需要单独启动或停止某个同步通道，可使用如下命令：
start slave for channel '300';     //启动名称为300的同步通道
stop slave for channel '300';     //停止名称为300的同步通道

验证数据同步

新建库和表等，会发现同步到了mysql8-3上

mysql多源复制，多主一从复制
配置约束
【主库mysql8-1】操作及配置
【主库mysql8-2】配置及操作
【从库mysql8-3】配置及操作
验证数据同步
【主库mysql8-1】操作及配置
【主库mysql8-2】操作及配置
【从库mysql8-3】操作及配置
第二种情形：
多个主库上的表同步到从库的的一个库里面

  • 主库mysql8-1：IP=121.40.110.2; PORT=3310; server-id=1; database=datingcenter； table=aa、dd
  • 主库mysql8-2：IP=121.40.110.2; PORT=3311; server-id=2; database=usercenter ；table=cc、bb
  • 从库mysql8-3：IP=121.40.110.2; PORT=3312; server-id=3; database=matchcenter ；table=aa、dd、cc、bb

配置文件

【主库mysql8-1】操作及配置

配置my.cnf（/etc/mysql/my.cnf）

取消掉

binlog-do-db = datingcenter

【主库mysql8-2】操作及配置

配置my.cnf（/etc/mysql/my.cnf）

取消掉

binlog-do-db = usercenter

【从库mysql8-3】操作及配置

配置my.cnf（/etc/mysql/my.cnf）

[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data     #数据存储目录

##########################
# log bin
##########################
server-id = 3
master_info_repository      = table
relay_log_info_repository   = table    
replicate-rewrite-db=datingCenter->matchCenter_0
replicate-rewrite-db=userCenter->matchCenter_0    

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

设置【从库】信息

登录【从库mysql8-3】，进入mysql命令行。

mysql> stop slave;
Query OK, 0 rows affected

mysql> CHANGE MASTER TO 
MASTER_HOST='121.40.110.2',
MASTER_PORT=3310,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=155 
for channel '1';
Query OK, 0 rows affected

mysql> CHANGE MASTER TO 
MASTER_HOST='121.40.110.2',
MASTER_PORT=3311,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=155 
for channel '2';
Query OK, 0 rows affected

mysql> start slave;
Query OK, 0 rows affected

　stop slave;     //停止同步
start slave;     //开始同步
//必须和【主库】的信息匹配。
CHANGE MASTER TO
MASTER_HOST='121.40.110.2             //主库IP

MASTER_PORT=3310,                       //主库端口
MASTER_USER='slave',                     //访问主库且有同步复制权限的用户
MASTER_PASSWORD='123456',      //登录密码
//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
MASTER_LOG_FILE='mysql-bin.000001',
//【关键处】从文件中指定位置开始读取，主库show master status返回结果
MASTER_LOG_POS=155
for channel '1';            //定义通道名称　

查看同步状态

mysql> show slave status G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 47.111.132.49
                  Master_User: slave
                  Master_Port: 3310
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 169442
               Relay_Log_File: efe0882a2350-relay-bin-1.000017
                Relay_Log_Pos: 169656
        Relay_Master_Log_File: mysql-bin.000006
             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: 169442
              Relay_Log_Space: 170040
              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: 1
                  Master_UUID: 399257c7-a933-11e9-8d35-0242ac110002
             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: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (datingCenter_0,matchCenter_0),(userCenter_0,matchCenter_0)
                 Channel_Name: 1
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 47.111.132.49
                  Master_User: slave
                  Master_Port: 3311
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 155
               Relay_Log_File: efe0882a2350-relay-bin-2.000019
                Relay_Log_Pos: 369
        Relay_Master_Log_File: mysql-bin.000006
             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: 155
              Relay_Log_Space: 753
              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: 2
                  Master_UUID: 39a3fc71-a933-11e9-ac19-0242ac110003
             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: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (datingCenter_0,matchCenter_0),(userCenter_0,matchCenter_0)
                 Channel_Name: 2
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
2 rows in set (0.01 sec)

ERROR: 
No query specified
在datingcenter建立表aa dd    usercenter建立表 bb cc
查看同步结果
mysql多源复制，多主一从复制
配置约束
【主库mysql8-1】操作及配置
【主库mysql8-2】配置及操作
【从库mysql8-3】配置及操作
验证数据同步
【主库mysql8-1】操作及配置
【主库mysql8-2】操作及配置
【从库mysql8-3】操作及配置

参考：

https://juejin.im/entry/5bf7731351882518805ac985
https://www.cnblogs.com/zhjh256/p/9260554.html
https://blog.csdn.net/u014520745/article/details/76056170
https://blog.csdn.net/h996666/article/details/80921913
https://blog.csdn.net/sgrrmswtvt/article/details/82344183