当前位置:   article > 正文

mysql 一主多从_MySQL 多主一从配置

多主一从 启动单个slave

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上

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/木道寻08/article/detail/1008861
推荐阅读
相关标签
  

闽ICP备14008679号