用Docker搭建MySQL主从多节点集群
MySQL 主从同步分为 5 个步骤：
master 节点将数据的更新记录写到 binary log 中。
slave 节点开启 IO 线程连接 master 节点，请求获取指定 binary log 文件的指定位置之后的日志。
master 节点的 binary log dump 线程将指定的 binary log 信息推送给 slave 节点。
slave 节点的 IO 线程接收到消息后，将日志内容写入 relay log 文件。
slave 节点的 SQL 线程检测到 relay log 新增了内容，马上解析 relay log 文件生成相应的 SQL 语句，并将这些 SQL 语句重放到数据库，保证主从数据一致性。
通过Dockerfile构建一主一从
创建目录结构
1
2
3
4
5
|
- docker-compose.yaml
- mysql/master/Dockerfil
- mysql/master/cluster.cnf
- mysql/slave/Dockerfil
- mysql/slave/cluster.cnf
|
docker-compose.yaml内容
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
version: "3"
services:
mysql-master:
build:
context: ./
dockerfile: mysql/master/Dockerfile
container_name: mysql-master
volumes:
- ./mysql/master/data:/var/lib/mysql
restart: always
ports:
- 3305:3306
links:
- mysql-slave
mysql-slave:
build:
context: ./
dockerfile: mysql/slave/Dockerfile
container_name: mysql-slave
volumes:
- ./mysql/slave/data:/var/lib/mysql
restart: always
ports:
- 3306:3306
|
master 节点的 cluster.cnf 配置
1
2
3
4
5
6
7
8
9
10
11
12
|
[mysqld]
server_id=100
binlog-ignore-db=mysql
log-bin=replicas-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
slave_skip_errors=1062
# MySQL 8.x，需要如下配置
default_authentication_plugin=mysql_native_password
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
|
master 节点的 Dockerfile 内容
1
2
3
|
FROM mysql:latest
ADD ./mysql/master/cluster.cnf /etc/mysql/conf.d/cluster.cnf
ENV MYSQL_ROOT_PASSWORD=password
|
slave 节点的 cluster.cnf 配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
[mysqld]
server_id=101
binlog-ignore-db=mysql
binlog_cache_size=1M
binlog_format=mixed
slave_skip_errors=1062
relay_log=replicas-mysql-relay-bin
log_slave_updates=1
read_only=1
# MySQL 8.x，需要如下配置
default_authentication_plugin=mysql_native_password
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
|
slave 节点的 Dockerfile 内容
1
2
3
|
FROM mysql:latest
ADD ./mysql/slave/cluster.cnf /etc/mysql/conf.d/cluster.cnf
ENV MYSQL_ROOT_PASSWORD=password
|
启动容器
1
2
|
> docker-compose up -d mysql-master mysql-slave
> docker ps
|
建立主从关系
连接主节点查看复制位点File,Postion
1
2
3
|
> docker exec -it mysql-master bash
> mysql -uroot -p
> show master status;
|
连接从节点设置复制节点和位点参数
1
2
3
4
5
6
7
8
9
10
11
|
> docker exec -it mysql-slave bash
> mysql -uroot -p
> reset master;
> CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='root',
MASTER_PASSWORD=<root password>,
MASTER_LOG_FILE=<master File>,
MASTER_LOG_POS=<master Position>;
> start slave;
> show slave status\G
|
连接主节点执行sql操作测试主从复制健康状态
1
2
3
|
> docker exec -it mysql-master bash
> mysql -uroot -p
> create database cluster_test;
|
1
2
3
|
> docker exec -it mysql-slave bash
> mysql -uroot -p
> show databases;
|
一主多从
docker-compose.yaml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
version: '3'
services:
mysql-master:
container_name: mysql-master
hostname: mysql-master
image: "mysql:5.7"
ports:
- "33060:3306"
networks: ## 引入外部预先定义的网段
srm-mysql:
ipv4_address: 173.200.6.110 #设置ip地址
volumes:
- ./mysql/master/etc:/etc/mysql
- ./mysql/master/var/lib:/var/lib/mysql
- ./mysql/master/my.cnf:/etc/my.cnf
- ./master/init_db/:/docker-entrypoint-initdb.d/
environment:
- TZ=Asia/Shanghai
- MYSQL_ROOT_PASSWORD=123456
command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
restart: always
mysql-slave-1:
container_name: mysql-slave-1
hostname: mysql-slave-1
image: "mysql:5.7"
ports:
- "33070:3306"
networks: ## 引入外部预先定义的网段
srm-mysql:
ipv4_address: 173.200.6.120 #设置ip地址
volumes:
- ./mysql/slave1/etc:/etc/mysql
- ./mysql/slave1/var/lib:/var/lib/mysql
- ./mysql/slave1/my.cnf:/etc/my.cnf
- ./slave-1/init_db/:/docker-entrypoint-initdb.d/
environment:
- TZ=Asia/Shanghai
- MYSQL_ROOT_PASSWORD=123456
command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
restart: always
mysql-slave-2:
container_name: mysql-slave-2
hostname: mysql-slave-2
image: "mysql:5.7"
ports:
- "33080:3306"
networks: ## 引入外部预先定义的网段
srm-mysql:
ipv4_address: 173.200.6.130 #设置ip地址
volumes:
- ./mysql/slave2/etc:/etc/mysql
- ./mysql/slave2/var/lib:/var/lib/mysql
- ./mysql/slave2/my.cnf:/etc/my.cnf
- ./slave-2/init_db/:/docker-entrypoint-initdb.d/
environment:
- TZ=Asia/Shanghai
- MYSQL_ROOT_PASSWORD=123456
command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
restart: always
# 设置网络模块
networks:
# 自定义网络
srm-mysql:
driver: bridge
ipam: #定义网段
config:
- subnet: "173.200.6.0/24"
|
主节点的my.cnf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[mysqld]
server_id = 1
# 开启GTID模式
gtid-mode = ON
enforce-gtid-consistency = 1
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db = mysql
# 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
log-bin = mysql-bin
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format = mixed
# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days = 7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors = 1062
# log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates = ON
|
从节点配置my.cnf，修改server_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[mysqld]
server_id = 2
gtid-mode = ON
enforce-gtid-consistency = 1
binlog-ignore-db = mysql
log-bin = mysql-slave1-bin
binlog_cache_size = 1M
binlog_format = mixed
expire_logs_days = 7
slave_skip_errors = 1062
relay_log = mysql-relay-bin
log_slave_updates = ON
read_only = 1
|
连接主节点创建复制账号
1
2
3
4
5
|
> docker exec -it mysql-master bash
> mysql -uroot -p
> create user 'repl'@'%' identified by 'repl';
> grant replication client,replication slave on *.* to 'repl'@'%';
|
连接从节点重置主
1
2
3
4
5
6
|
> docker exec -it mysql-slave-1 bash
> mysql -uroot -p
> reset master;
> CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
> start slave;
|
mha高可用
docker-compose.yaml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
version: "2"
services:
mha_share:
image: debian:jessie
volumes:
- "./employees_share/:/mha_share/"
mha_node:
build: ./mha_node
image: prontera/mha_node
read_only: true
mha_manager:
build: ./mha_manager
image: prontera/mha_manager
read_only: true
master:
image: prontera/mha_node
depends_on:
- mha_node
ports:
- "3406:3306"
volumes:
- "./employees_db/:/docker-entrypoint-initdb.d/:ro"
- "./employees_master/lib/:/var/lib/mysql/"
- "./employees_master/log/:/var/log/mysql/"
- "./employees_master/conf.d/:/etc/mysql/mysql.conf.d/"
volumes_from:
- mha_share
env_file:
- ./account.env
environment:
- MYSQL_USER=prontera
- MYSQL_PASSWORD=123123
slave_1:
image: prontera/mha_node
depends_on:
- master
ports:
- "3407:3306"
volumes:
- "./employees_slave_1/lib/:/var/lib/mysql/"
- "./employees_slave_1/log/:/var/log/mysql/"
- "./employees_slave_1/conf.d/:/etc/mysql/mysql.conf.d/"
volumes_from:
- mha_share
env_file:
- ./account.env
manager:
image: prontera/mha_manager
depends_on:
- mha_manager
- slave_1
volumes_from:
- mha_share
entrypoint:
tailf /dev/null
env_file:
- ./account.env
|
参考