MySQL多源复制实战案例

一.GTID环境准备

1.规划:(2主1从)

    10.0.0.108:
        主库,数据库为zabbix

    10.0.0.107:
        主库,数据库为hive

    10.0.0.106:
        从库

2.创建初始目录

pkill mysqld

rm -rf /oldboyedu/data

mkdir -pv /oldboyedu/{etc,data,logs,softwares}/{mysql57,mysql80,mysql56}/{3307,3308,3309}

install -o mysql -g mysql -d /oldboyedu/logs/mysql57/3307/logbin

3.主库修改配置文件

    10.0.0.108:
cat > /oldboyedu/etc/mysql57/3307/my.cnf <<EOF
[mysqld]
datadir=/oldboyedu/data/mysql57/3307/
basedir=/oldboyedu/softwares/mysql57/
server_id=108
port=3307
socket=/tmp/mysql3307.sock
log_bin=/oldboyedu/logs/mysql57/3307/logbin/oldboyedu_mysql_logbin
max_allowed_packet=64M
max_connections=1024
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=1
EOF

    10.0.0.107: 
cat > /oldboyedu/etc/mysql57/3307/my.cnf <<EOF
[mysqld]
datadir=/oldboyedu/data/mysql57/3307/
basedir=/oldboyedu/softwares/mysql57/
server_id=107
port=3307
socket=/tmp/mysql3307.sock
log_bin=/oldboyedu/logs/mysql57/3307/logbin/oldboyedu_mysql_logbin
max_allowed_packet=64M
max_connections=1024
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=1
EOF

4.从库修改配置文件

    10.0.0.106
cat > /oldboyedu/etc/mysql57/3307/my.cnf <<EOF
[mysqld]
datadir=/oldboyedu/data/mysql57/3307/
basedir=/oldboyedu/softwares/mysql57/
server_id=106
port=3307
socket=/tmp/mysql3307.sock
log_bin=/oldboyedu/logs/mysql57/3307/logbin/oldboyedu_mysql_logbin
max_allowed_packet=64M
max_connections=1024
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=1
master_info_repository=table
relay_log_info_repository=table
EOF

5.初始化数据库

    mysql 5.7:
        /oldboyedu/softwares/mysql57/bin/mysqld --initialize-insecure --user=mysql --basedir=/oldboyedu/softwares/mysql57 --datadir=/oldboyedu/data/mysql57/3307

        或者执行:
        /oldboyedu/softwares/mysql57/bin/mysqld --defaults-file=/oldboyedu/etc/mysql57/3307/my.cnf --initialize-insecure --user=mysql

    mysql 8.0:
        /oldboyedu/softwares/mysql80/bin/mysqld --initialize-insecure --user=mysql --basedir=/oldboyedu/softwares/mysql80 --datadir=/oldboyedu/data/mysql80/3308

6.启动数据库实例

        主库: 10.0.0.108
            /oldboyedu/softwares/mysql57/bin/mysqld_safe --defaults-file=/oldboyedu/etc/mysql57/3307/my.cnf &>/dev/null &

        主库: 10.0.0.107
            /oldboyedu/softwares/mysql57/bin/mysqld_safe --defaults-file=/oldboyedu/etc/mysql57/3307/my.cnf &>/dev/null &

        从库: 10.0.0.106
            /oldboyedu/softwares/mysql57/bin/mysqld_safe --defaults-file=/oldboyedu/etc/mysql57/3307/my.cnf &>/dev/null &

7.主库创建创建复制用户

        主库: 10.0.0.108
            SELECT @@sql_log_bin;
            SET sql_log_bin = 0;
            SELECT @@sql_log_bin;
            CREATE USER copy@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY 'oldboyedu';
            GRANT REPLICATION SLAVE ON *.* TO copy@'10.0.0.%' ;
            SET sql_log_bin = 1;
            SELECT @@sql_log_bin;
            SELECT user,host FROM mysql.user;
            SHOW GRANTS FOR 'copy'@'10.0.0.%';

        主库: 10.0.0.107
            SELECT @@sql_log_bin;
            SET sql_log_bin = 0;
            SELECT @@sql_log_bin;
            CREATE USER copy2021@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY 'oldboyedu';
            GRANT REPLICATION SLAVE ON *.* TO copy2021@'10.0.0.%' ;
            SET sql_log_bin = 1;
            SELECT @@sql_log_bin;
            SELECT user,host FROM mysql.user;
            SHOW GRANTS FOR 'copy2021'@'10.0.0.%';

二. 配置多源复制

1.从库配置10.0.0.107为主库并建立'db107'的CHANNEL

CHANGE MASTER TO MASTER_HOST='10.0.0.107',MASTER_USER='copy', MASTER_PASSWORD='oldboyedu',MASTER_PORT=3307, MASTER_AUTO_POSITION=1 FOR CHANNEL 'db107'; 

2.从库配置10.0.0.108为主库并建立'db108'的CHANNEL

CHANGE MASTER TO MASTER_HOST='10.0.0.108',MASTER_USER='copy2021', MASTER_PASSWORD='oldboyedu', MASTER_PORT=3307,MASTER_AUTO_POSITION=1 FOR CHANNEL 'db108'; 

3.从库查看相应的主库信息

SHOW SLAVE STATUS FOR CHANNEL 'db107'\G

SHOW SLAVE STATUS FOR CHANNEL 'db108'\G

三.多源复制监控

1.启动主从复制

    START SLAVE FOR CHANNEL  'db107';
    START SLAVE FOR CHANNEL  'db108'; 

2.从库查看相应的主库信息

    SHOW SLAVE STATUS FOR CHANNEL 'db107'\G
    SHOW SLAVE STATUS FOR CHANNEL 'db108'\G

​

3.查看表中的信息

SELECT * FROM  mysql.slave_master_info\G

select * from performance_schema.replication_connection_configuration\G
SELECT * FROM performance_schema.replication_connection_status WHERE CHANNEL_NAME='db107'\G
select * from performance_schema.replication_applier_status_by_worker;

四.多源复制配置过滤(对于下面的"[FOR CHANNEL channel]"只有MySQL 8.0支持哟~)

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db107.%') FOR CHANNEL "db107";
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db108.%') FOR CHANNEL "db108";

温馨提示:
    出现问题记得执行以下语句
        stop slave for CHANNEL  'db107';
        stop slave for CHANNEL  'db108';
        reset slave all;

五.可能会遇到的报错

1.To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.

报错原因:
    若要具有多个通道，存储库不能为文件类型；请检查存储库配置并将其转换为表

解决方案:
    master_info_repository =table
    relay_log_info_repository =table