Docker 容器 mysql 配置主从
1、前提条件[*]集群的条件下 服务器 172.16.11.195 13316:3306 服务器 172.16.11.196 13317:3306
[*]配置好主数据库和从数据
2、配置主从数据库
2.1使用portainer 来管理容器
创建数据库密码
https://img-blog.csdnimg.cn/direct/965893b1d4f34a01a61447d9c84ad6a6.png
新增配置文件
https://img-blog.csdnimg.cn/direct/6f83b9c70c3f4eba9fd734ba7d45783f.png
# mysql-master.cnf
server_id=110
log-bin=mysql-bin
relay_log=mysql-relay-bin
log_replica_updates=on
secure_file_priv=/var/lib/mysql # mysql-slave.cnf
server_id=111
log-bin=mysql-bin
slave-skip-errors=1032
read_only=1
relay_log=mysql-relay-bin
log_replica_updates=on
secure_file_priv=/var/lib/mysql https://img-blog.csdnimg.cn/direct/c70c221eb97c4072aa7ff7f482f5c3e1.png
# eip-mysql-cluster
version: '3.7'
services:
eip-mysql-master:
image: harbor.hkc.cn/jpaas/mysql:8
privileged: true
hostname: eip-mysql-master
container_name: eip_mysql_master
networks:
- golbal-service-jpaas-net
ports:
- 13316:3306
volumes:
- /etc/localtime:/etc/localtime
- mysql-master-data:/var/lib/mysql
configs:
- source: mysql-master.cnf
target: /etc/mysql/my.cnf
command:
--default-authentication-plugin=mysql_native_password
--max_connections=6000
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
--default-time-zone='+8:00'
--expire-logs-days=7
--sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
deploy:
replicas: 1
placement:
constraints:
restart_policy:
condition: on-failure
secrets:
- mysql-secrt
environment:
- MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql-secrt
eip-mysql-slave:
image: harbor.hkc.cn/jpaas/mysql:8
privileged: true
hostname: eip-mysql-slave
container_name: eip_mysql_slave
networks:
- golbal-service-jpaas-net
ports:
- 13317:3306
volumes:
- mysql-slave-data:/var/lib/mysql
- /etc/localtime:/etc/localtime
configs:
- source: mysql-slave.cnf
target: /etc/mysql/my.cnf
command:
--default-authentication-plugin=mysql_native_password
--max_connections=6000
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
--default-time-zone='+8:00'
--expire-logs-days=7
--sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
deploy:
replicas: 1
placement:
constraints:
restart_policy:
condition: on-failure
secrets:
- mysql-secrt
environment:
- MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql-secrt
secrets:
mysql-secrt:
external: true
configs:
mysql-master.cnf:
external: true
mysql-slave.cnf:
external: true
volumes:
mysql-master-data:
mysql-slave-data:
networks:
golbal-service-jpaas-net:
external: true
3、配置主从同步
3.1 配置主节点访问账号
进入容器里面的mysql
# 登录mysql
mysql -uroot -ptryyiuer https://img-blog.csdnimg.cn/direct/c8e6a18802c94753b5caaf59deeebff4.png
创建访问账户
## 创建从节点的访问账号
CREATE USER 'slave'@'%' IDENTIFIED BY 'slave';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
## 查看用户
SELECT User, Host FROM mysql.user;
## 查看用户权限
SHOW GRANTS FOR 'slave'@'%';
## 查看master状态
show master status; https://img-blog.csdnimg.cn/direct/4ab63a79ede14ad898c4e2f8c20511ac.png
https://img-blog.csdnimg.cn/direct/926b5e645e444274b7e917da91d98ec0.png
这里记住 File 和 Position 这里属性的值。
退出mysql 执行 exit即可;
退出容器执行 exit即可;
3.2 配置从节点同步
如上一样,进入到从节点容器的mysql中。执行以下指令
# MASTER_HOST是互通的IP地址,如果是容器管理,加入同一个网络,使用容器的服务名称
# 端口默认3306可以加上 Master_Port=3306可以改成自己mysql 配置的端口
CHANGE MASTER TO
MASTER_HOST='eip-mysql-master',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_LOG_FILE='ON.000004',
MASTER_LOG_POS=7639;
## 执行完成后,开启同步
start slave;
## 查看状态(不需要分号)
SHOW SLAVE STATUS\G 两个关键进程:下面两个参数都是Yes,则分析主从配置成功!
https://img-blog.csdnimg.cn/direct/f7bd06b47a984b299c742042705b030d.png
4、测试
5、大概会出现的题目
4.1、数据库布局同等
[*]配置之前,肯定要保证两个数据的布局一样
https://img-blog.csdnimg.cn/direct/e7b65ae69a6849baa0692b9b05f7e505.png
4.2、网络原因
[*]是否关闭防火墙。
# 查看防火墙状态
systemctl status firewalld.service
# 关闭防火墙
systemctl stop firewalld.service
# 开启防火墙
systemctl start firewalld.service
# 防火墙随系统开启启动
systemctl enable firewalld.service
# 执行开机禁用防火墙自启命令
systemctl disable firewalld.service
[*]ping ip 是否连通。
ping 172.16.11.195 4.3、实在不行就:重置从服务器(重新配置)
# 如果上述方法都无法解决问题,或者从服务器的复制状态变得混乱,你可能需要重置从服务器的复制状态:
STOP SLAVE;
RESET SLAVE ALL;
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。
页:
[1]