MySQL 多主一从配置

MySQL 的多主一从是指,多个master机,单个slave的模式,是在MySQL5.7以后支持的

主要的作用是可以同步多个主库上的数据到单个slave上,可以用于数据聚合到一台主机,更实用的是当是双机互为主备的情况下,可以只有一个slave配置同步两个master上的数据。

好了,因为本地之前是alisql(基于mysql5.6.32)不支持多主一从,所以,需要先建立几个5.7以上版本的mysql实例,我这里用 docker-compose 快速创建4个mysql8.0的实例:

version: '3.7'
services:
    mysql8_1:
        image: "mysql:8.0.19"
        container_name: mysql8_1
        ports:
            - "33080:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678
        
​
    mysql8_2:
        image: "mysql:8.0.19"
        container_name: mysql8_2
        ports:
            - "33081:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678
​
    mysql8_3:
        image: "mysql:8.0.19"
        container_name: mysql8_3
        ports:
            - "33082:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678
​
    mysql8_4:
        image: "mysql:8.0.19"
        container_name: mysql8_4
        ports:
            - "33083:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678
将上面的配置保存为 docker-compose-mysql8.xml 后,然后使用docker-compose 来启动,命令如下
docker-compose -f "/home/yangyan/Documents/mysql-cluster/docker-compose-mysql8.yml" up -d

输出

Pulling mysql8_1 (mysql:8.0.19)...
8.0.19: Pulling from library/mysql
c499e6d256d6: Pull complete
22c4cdf4ea75: Pull complete
6ff5091a5a30: Pull complete
2fd3d1af9403: Pull complete
0d9d26127d1d: Pull complete
54a67d4e7579: Pull complete
fe989230d866: Pull complete
3a808704d40c: Pull complete
826517d07519: Pull complete
69cd125db928: Pull complete
b5c43b8c2879: Pull complete
1811572b5ea5: Pull complete
Digest: sha256:b69d0b62d02ee1eba8c7aeb32eba1bb678b6cfa4ccfb211a5d7931c7755dc4a8
Status: Downloaded newer image for mysql:8.0.19
Creating mysql8_2 ... done
Creating mysql8_3 ... done
Creating mysql8_1 ... done
Creating mysql8_4 ... done

然后看下是不是都启动成功了

➜  mysql-cluster docker ps                                                                                 
CONTAINER ID        IMAGE                    COMMAND                  CREATED             STATUS              PORTS                                      NAMES
4cc40f14d3bb        mysql:8.0.19             "docker-entrypoint.s…"   10 seconds ago      Up 8 seconds        33060/tcp, 0.0.0.0:33083->3306/tcp         mysql8_4
244c6c930708        mysql:8.0.19             "docker-entrypoint.s…"   10 seconds ago      Up 8 seconds        33060/tcp, 0.0.0.0:33080->3306/tcp         mysql8_1
d2ba232da6ce        mysql:8.0.19             "docker-entrypoint.s…"   10 seconds ago      Up 7 seconds        33060/tcp, 0.0.0.0:33082->3306/tcp         mysql8_3
3dfd2fda114c        mysql:8.0.19             "docker-entrypoint.s…"   10 seconds ago      Up 8 seconds        33060/tcp, 0.0.0.0:33081->3306/tcp         mysql8_2
​

如果没有启动成功的话,看看是不是端口被占用了。这里提醒一下大家,使用docker-compose 停止服务时使用 stop ,不要使用down,down会直接连容器都删掉。

配置双主互备 mysql8_1 和 mysql8_3

这个版本的mysql配置文件 /etc/mysql/conf.d/docker.cnf 内容修改:

mysql8_1 操作:

➜  mysql-cluster docker exec -it mysql8_1 bash
root@244c6c930708:/# echo '
[mysqld]
skip-host-cache
skip-name-resolve
​
server-id=11
log-bin=mysql-bin
sync_binlog=1
binlog_checksum=none
binlog_format=mixed
​
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1' > /etc/mysql/conf.d/docker.cnf

配置互相同步:

change master to master_host='mysql8_3',master_port=3306,master_user='root',master_password='12345678',master_auto_position =1;
start slave ;
show slave status ;

mysql8_3 操作:

➜  mysql-cluster docker exec -it mysql8_3 bash
root@d2ba232da6ce:/# echo '
> [mysqld]
> skip-host-cache
> skip-name-resolve
> 
> server-id=13
> log-bin=mysql-bin
> sync_binlog=1
> binlog_checksum=none
> binlog_format=mixed
> 
> gtid-mode=on
> enforce-gtid-consistency=true
> log-slave-updates=1' > /etc/mysql/conf.d/docker.cnf

配置互相同步:

change master to master_host='mysql8_1',master_port=3306,master_user='root',master_password='12345678',master_auto_position =1;
start slave ;
show slave status ;

创建库和表,随便插入一条数据,验证互为主备成功后,继续下面的操作。

配置从库同步 mysql8_2 从两个主同步

我们需要达到的目的是,当其中一个master 挂掉的时候,从库仍然可以从另一个主库上同步成功

需要先开启 gtid,所以配置文件上需要设置开启 gtid-mode=on

➜  mysql-cluster docker exec -it mysql8_2 bash
root@3dfd2fda114c:/# echo '
> [mysqld]
> skip-host-cache
> skip-name-resolve
> 
> server-id=12
> log-bin=mysql-bin
> sync_binlog=1
> binlog_checksum=none
> binlog_format=mixed
> 
> gtid-mode=on
> enforce-gtid-consistency=true
> ' > /etc/mysql/conf.d/docker.cnf

然后在sql终端执行:

show slave status;
change master to master_host ='mysql8_1',master_port =3306,master_user ='root',master_password ='12345678',master_auto_position =1 for channel '1';
change master to master_host ='mysql8_3',master_port =3306,master_user ='root',master_password ='12345678',master_auto_position =1 for channel '3';
​
show slave status for channel '1';
show slave status for channel '3';
​
start slave;

如果一切顺利话,此slave上就已经有前面在master上创建的数据了,无论我们是在 1 还是 3 上对数据做出修改,都会成功的应用到此slave上

Leave a Comment

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据