centos7使用docker-compose一键搭建mysql高可用主从集群

docker部署

环境准备

卸载旧版本

yum remove -y docker \docker-client \docker-client-latest \docker-common \docker-latest \docker-latest-logrotate \docker-logrotate \docker-selinux \docker-engine-selinux \docker-engine

安装依赖

yum install -y yum-utils \ device-mapper-persistent-data \ lvm2

下载阿里云仓库

wget http://mirrors.aliyun.com/repo/Centos-7.repowget  http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repoyum install -y epel-releasemv *.repo /etc/yum.repo.d/

安装docker

yum install -y docker-ce

开启docker

systemctl start docker.service 
systemctl enable docker.service 
systemctl status  docker.service 
docker version

安装docker-compose

linux安装docker和docker-compose_linux安装docker-compose_yuanzelin8的博客-CSDN博客

服务器IP：192.168.100.8

文件夹和其他文件如图：

编写docker-compose.yml文件

version: '3'
services:mysql-slave-lb:image: nginx:latestcontainer_name: mysql-slave-lbports:- 3307:3307volumes:- /home/docker/mariadb10.7/nginx/nginx.conf:/etc/nginx/nginx.confnetworks:- mysqldepends_on:- mysql-master- mysql-slave1- mysql-slave2- mysql-slave3mysql-master:image: mariadb:10.7.3container_name: mysql-masterenvironment:MYSQL_ROOT_PASSWORD: "123456"MASTER_SYNC_USER: "sync_admin" #设置脚本中定义的用于同步的账号MASTER_SYNC_PASSWORD: "123456" #设置脚本中定义的用于同步的密码ADMIN_USER: "root" #当前容器用于拥有创建账号功能的数据库账号ADMIN_PASSWORD: "123456"ALLOW_HOST: "10.10.%.%" #允许同步账号的host地址TZ: "Asia/Shanghai" #解决时区问题ports:- 3306:3306networks:mysql:ipv4_address: "10.10.10.10" #固定ip，因为从库在连接master的时候，需要设置hostvolumes:- /home/docker/mariadb10.7/master:/docker-entrypoint-initdb.d #挂载master脚本- /home/docker/mariadb10.7/master/data:/var/lib/mysqlcommand:-  "--server-id=1"-  "--character-set-server=utf8mb4"-  "--collation-server=utf8mb4_unicode_ci"-  "--log-bin=mysql-bin"-  "--sync_binlog=1"mysql-slave1:image: mariadb:10.7.3container_name: mysql-slave1environment:MYSQL_ROOT_PASSWORD: "123456"SLAVE_SYNC_USER: "sync_admin" #用于同步的账号，由master创建SLAVE_SYNC_PASSWORD: "123456"ADMIN_USER: "root"ADMIN_PASSWORD: "123456"MASTER_HOST: "10.10.10.10" #master地址，开启主从同步需要连接masterTZ: "Asia/Shanghai" #设置时区networks:mysql:ipv4_address: "10.10.10.20" #固定ipvolumes:- /home/docker/mariadb10.7/slave1:/docker-entrypoint-initdb.d #挂载slave脚本- /home/docker/mariadb10.7/slave1/data:/var/lib/mysqlcommand:-  "--server-id=2"-  "--character-set-server=utf8mb4"-  "--collation-server=utf8mb4_unicode_ci"mysql-slave2:image: mariadb:10.7.3container_name: mysql-slave2environment:MYSQL_ROOT_PASSWORD: "123456"SLAVE_SYNC_USER: "sync_admin"SLAVE_SYNC_PASSWORD: "123456"ADMIN_USER: "root"ADMIN_PASSWORD: "123456"MASTER_HOST: "10.10.10.10"TZ: "Asia/Shanghai"networks:mysql:ipv4_address: "10.10.10.30" #固定ipvolumes:- /home/docker/mariadb10.7/slave2:/docker-entrypoint-initdb.d #挂载slave脚本- /home/docker/mariadb10.7/slave2/data:/var/lib/mysqlcommand: #这里需要修改server-id，保证每个mysql容器的server-id都不一样-  "--server-id=3"-  "--character-set-server=utf8mb4"-  "--collation-server=utf8mb4_unicode_ci"mysql-slave3:image: mariadb:10.7.3container_name: mysql-slave3environment:MYSQL_ROOT_PASSWORD: "123456"SLAVE_SYNC_USER: "sync_admin" #用于同步的账号，由master创建SLAVE_SYNC_PASSWORD: "123456"ADMIN_USER: "root"ADMIN_PASSWORD: "123456"MASTER_HOST: "10.10.10.10" #master地址，开启主从同步需要连接masterTZ: "Asia/Shanghai" #设置时区networks:mysql:ipv4_address: "10.10.10.40" #固定ipvolumes:- /home/docker/mariadb10.7/slave3:/docker-entrypoint-initdb.d #挂载slave脚本- /home/docker/mariadb10.7/slave3/data:/var/lib/mysqlcommand:-  "--server-id=4"-  "--character-set-server=utf8mb4"-  "--collation-server=utf8mb4_unicode_ci"
networks:mysql:driver: bridgeipam:driver: defaultconfig:- subnet: "10.10.0.0/16"

编写master用于创建同步账号的脚本create_sync_user.sh

#!/bin/bash
#定义用于同步的用户名
MASTER_SYNC_USER=${MASTER_SYNC_USER:-sync_admin}
#定义用于同步的用户密码
MASTER_SYNC_PASSWORD=${MASTER_SYNC_PASSWORD:-123456}
#定义用于登录mysql的用户名
ADMIN_USER=${ADMIN_USER:-root}
#定义用于登录mysql的用户密码
ADMIN_PASSWORD=${ADMIN_PASSWORD:-123456}
#定义运行登录的host地址
ALLOW_HOST=${ALLOW_HOST:-%}
#定义创建账号的sql语句
CREATE_USER_SQL="CREATE USER '$MASTER_SYNC_USER'@'$ALLOW_HOST' IDENTIFIED BY '$MASTER_SYNC_PASSWORD';"
#定义赋予同步账号权限的sql,这里设置两个权限，REPLICATION SLAVE，属于从节点副本的权限，REPLICATION CLIENT是副本客户端的权限，可以执行show master status语句
GRANT_PRIVILEGES_SQL="GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '$MASTER_SYNC_USER'@'$ALLOW_HOST';"
#定义刷新权限的sql
FLUSH_PRIVILEGES_SQL="FLUSH PRIVILEGES;"
#执行sql
mysql -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$CREATE_USER_SQL $GRANT_PRIVILEGES_SQL $FLUSH_PRIVILEGES_SQL"

编写slave脚本slave.sh

#定义连接master进行同步的账号SLAVE_SYNC_USER="${SLAVE_SYNC_USER:-sync_admin}"#定义连接master进行同步的账号密码SLAVE_SYNC_PASSWORD="${SLAVE_SYNC_PASSWORD:-123456}"#定义slave数据库账号ADMIN_USER="${ADMIN_USER:-root}"#定义slave数据库密码ADMIN_PASSWORD="${ADMIN_PASSWORD:-123456}"#定义连接master数据库host地址MASTER_HOST="${MASTER_HOST:-%}"#等待10s，保证master数据库启动成功，不然会连接失败sleep 10#连接master数据库，查询二进制数据，并解析出logfile和pos，这里同步用户要开启 REPLICATION CLIENT权限，才能使用SHOW MASTER STATUS;RESULT=`mysql -u"$SLAVE_SYNC_USER" -h$MASTER_HOST -p"$SLAVE_SYNC_PASSWORD" -e "SHOW MASTER STATUS;" | grep -v grep |tail -n +2| awk '{print $1,$2}'`#解析出logfileLOG_FILE_NAME=`echo $RESULT | grep -v grep | awk '{print $1}'`#解析出posLOG_FILE_POS=`echo $RESULT | grep -v grep | awk '{print $2}'`#设置连接master的同步相关信息SYNC_SQL="change master to master_host='$MASTER_HOST',master_user='$SLAVE_SYNC_USER',master_password='$SLAVE_SYNC_PASSWORD',master_log_file='$LOG_FILE_NAME',master_log_pos=$LOG_FILE_POS;"#开启同步START_SYNC_SQL="start slave;"#关闭同步STOP_SYNC_SQL="stop slave;"#查看同步状态STATUS_SQL="show slave status\G;"mysql -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$STOP_SYNC_SQL $SYNC_SQL $START_SYNC_SQL $STATUS_SQL"

加入nginx，实现slave的负载均衡

nginx下创建nginx.conf


user  nginx;
worker_processes  auto;error_log  /var/log/nginx/error.log warn;
pid        /var/run/nginx.pid;events {worker_connections  1024;
}# 添加stream模块，实现tcp反向代理
stream {proxy_timeout 30m;upstream mysql-slave-cluster{#docker-compose.yml里面会配置固定mysql-slave的ip地址，这里就填写固定的ip地址server 10.10.10.20:3306 weight=1;server 10.10.10.30:3306 weight=1;  server 10.10.10.40:3306 weight=1 backup; #备用数据库，当上面的数据库挂掉之后，才会使用此数据库，也就是如果上面的数据库没有挂，则所有的流量都很转发到上面的主库}server {listen  0.0.0.0:3307;proxy_pass mysql-slave-cluster;}
}

启动容器docker-compose up -d