Mysql----多源复制搭建

一、环境准备

1、准备三台docker虚拟主机

主机名	ip
docker1	192.168.99.100
docker2	192.168.99.101
docker3	192.168.99.102

2、mysql配置文件准备

docker run -d --name mysql --rm -e MYSQL_ROOT_PASSWORD=123456 cuiyf/mysql:5.7
docker cp mysql:/etc/mysql .
mv mysql config ; mkdir mysql; mv config mysql
docker stop mysql

二、多源复制搭建

1、首先启动三个mysql实例

master1
docker run -d --name mysql-master1 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 \
-v /home/docker/mysql/config/:/etc/mysql \
-v /home/docker/mysql/data:/var/lib/mysql \
cuiyf/mysql:5.7

master2
docker run -d --name mysql-master2 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 \
-v /home/docker/mysql/config/:/etc/mysql \
-v /home/docker/mysql/data:/var/lib/mysql \
cuiyf/mysql:5.7

slave
docker run -d --name mysql-slave -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 \
-v /home/docker/mysql/config/:/etc/mysql \
-v /home/docker/mysql/data:/var/lib/mysql \
cuiyf/mysql:5.7

2、修改配置文件

进入到cd mysql/config/mysql.conf.d目录，配置mysql复制必须先开启binlog，增加binlog.cnf，每个实例都增加（server_id不能一样）

[mysqld]
log-bin=mysql-bin
binlog-format=ROW
server_id=1
expire_logs_days=7
max_binlog_size=100M

查看binlog是否开启，查看log_bin的值是否为ON

docker@docker1:~/mysql/config/mysql.conf.d$ docker exec -it mysql-slave bash
root@d2f274b3f758:/# mysql -uroot -p123456
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)

配置多源复制所必需配置文件

[mysqld]
# 必需
master_info_repository=TABLE
relay_log_info_repository=TABLE

# 可选
relay-log=/var/lib/mysql/relay-log
relay-log-index=/var/lib/mysql/relay-log-index
relay-log-info-file=/var/lib/mysql/relay-log.info

3、授权复制

首先两个主库都创建一个拥有复制权限的用户

docker@docker1:~/mysql/config/mysql.conf.d$ docker exec -it mysql-master1 bash
root@24383441e2d4:/# mysql -uroot -p123456

mysql> grant replication slave on *.* to 'repl'@'%' identified by '111111';
Query OK, 0 rows affected, 1 warning (0.00 sec)

记录master节点状态

# master1
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      437 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# master2
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      437 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

创建多源复制，登录从库操作，与主从复制类似，只不过多了一个FOR CHANNEL ’名称‘

首先创建master1

docker@docker3:~/mysql/config/mysql.conf.d$ docker exec -it mysql-slave bash
root@d2f274b3f758:/# mysql -uroot -p123456

mysql> CHANGE MASTER TO MASTER_HOST='192.168.99.100', MASTER_USER='repl',MASTER_PORT=3306,MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=437 FOR CHANNEL 'master1';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave for channel 'master1';
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status for channel 'master1'\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.99.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 437
               Relay_Log_File: relay-log-master1.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             ...
                             ...
                             ...
         Replicate_Rewrite_DB: 
                 Channel_Name: master2
           Master_TLS_Version: 
1 row in set (0.00 sec)

其次创建master1

mysql>  CHANGE MASTER TO MASTER_HOST='192.168.99.101', MASTER_USER='repl',MASTER_PORT=3306,MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=437 FOR CHANNEL 'master2';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave for channel 'master2';
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status for channel 'master2'\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.99.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 437
               Relay_Log_File: relay-log-master2.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             ...
                             ...
                             ...
         Replicate_Rewrite_DB: 
                 Channel_Name: master2
           Master_TLS_Version: 
1 row in set (0.00 sec)

当且仅当所有channel状态中Slave_IO_Running和Slave_SQL_Running均为Yes时，多源复制搭建完成。

4、验证是否成功

master1创建一个master1数据库

mysql> create database master1;

master2创建一个master2数据库

mysql> create database master2;

查看从库是否存在，存在即多源复制搭建成功

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master1            |
| master2            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+