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上
了解 工作生活心情记忆 的更多信息
Subscribe to get the latest posts sent to your email.