使用Docker自动化部署MySQL集群
本文主要是简化mysql主从搭建，同时给需要学习mysql主从的同学一个现成的环境。
此脚本主要是根据环境变量，创建出用于主从同步的用户。关于为什么要创建出master,slave脚本，是为了区分master和slave环境，因为只要把sql和sh文件放到master目录下面，master在第一次初始化的时候，会自动执行里面的文件内容
复制
mkdir -p init init/master init/slave
cat > init/master/master.sh <<EOF
#!/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 SELECT,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"
EOF1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.
slave主要是去master查询最新的pos位置和binlog文件名称，然后创建同步需要的相关信息，然后执行start slave;
复制
cat >init/slave/slave.sh<<EOF
#定义连接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:-%}"
#连接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}\`
#解析出logfile
LOG_FILE_NAME=\`echo \$RESULT | grep -v grep | awk {print \$1}\`
#解析出pos
LOG_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,get_master_public_key=1;"
#开启同步
START_SYNC_SQL="start slave;"
#查看同步状态
STATUS_SQL="show slave status\G;"
mysql -u"\$ADMIN_USER" -p"\$ADMIN_PASSWORD" -e "\$SYNC_SQL \$START_SYNC_SQL \$STATUS_SQL"
EOF1.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:
master:
image: mysql:8
container_name: mysql-master
ports:
- 3306:3306
restart: always
hostname: mysql-master
environment:
MYSQL_ROOT_PASSWORD: "123456"
MASTER_SYNC_USER: "sync"
MASTER_SYNC_PASSWORD: "123456"
ADMIN_USER: "root"
ADMIN_PASSWORD: "123456"
TZ: "Asia/Shanghai"
deploy:
resources:
limits:
memory: 512M
cpus: 50m
healthcheck:
test: ["CMD","mysqladmin","-uroot","-p$${MYSQL_ROOT_PASSWORD}","ping","-h","localhost"]
timeout: 2s
interval: 10s
retries: 5
start_period: 5s
logging:
options:
max-file: 1
max-size: 128k
command:
- "--server-id=1"
- "--character-set-server=utf8mb4"
- "--collation-server=utf8mb4_unicode_ci"
- "--log-bin=mysql-bin"
- "--sync_binlog=1"
- "--binlog-ignore-db=mysql"
- "--binlog-ignore-db=sys"
- "--binlog-ignore-db=performance_schema"
- "--binlog-ignore-db=information_schema"
- "--sql_mode=NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES"
volumes:
- ./init/master:/docker-entrypoint-initdb.d
- ./data/master:/var/lib/mysql
slave:
image: mysql:8
container_name: mysql-slave
ports:
- 3307:3306
restart: always
hostname: mysql-slave
environment:
MYSQL_ROOT_PASSWORD: "123456"
SLAVE_SYNC_USER: "sync"
SLAVE_SYNC_PASSWORD: "123456"
ADMIN_USER: "root"
ADMIN_PASSWORD: "123456"
MASTER_HOST: "mysql-master"
TZ: "Asia/Shanghai"
healthcheck:
test: ["CMD","mysqladmin","-uroot","-p$${MYSQL_ROOT_PASSWORD}","ping","-h","localhost"]
timeout: 2s
interval: 10s
retries: 5
start_period: 5s
deploy:
resources:
limits:
memory: 512M
cpus: 50m
logging:
options:
max-file: 1
max-size: 128k
depends_on:
master:
condition: service_healthy
command:
- "--server-id=2"
- "--character-set-server=utf8mb4"
- "--collation-server=utf8mb4_unicode_ci"
- "--sql_mode=NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES"
volumes:
- ./init/slave:/docker-entrypoint-initdb.d
- ./data/slave:/var/lib/mysql1.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.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.
启动脚本
复制
docker-compose up -d1.
后期维护过程中可能用到的脚本
复制
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO master_log_pos=0;
START SLAVE;
SHOW SLAVE STATUS;1.2.3.4.5.
写在后面，基于docker-compose搭建mysql主从已经结束，这里主要演示了如何一键搭建mysql主从，同时测试在各种环境下，还能保证主从同步。但是单主从同步肯定也无法满足企业的需求，有兴趣的同学可以研究一些主主双写同步，然后通过nginx搭建主备模式，达到高可用。
这里提供一个判断的方法，编写#boot.sh脚本文件，这时候需要重写dockerfile文件，使用myslq debian的镜像文件。并通过apt添加netcat工具。具体不再详述了，以下为判断脚本。
复制
#!/bin/bash
wait_for() {
echo Waiting for $1 to listen on $2...
sleep 1
while ! nc -vz $1 $2
do
echo waiting...;
sleep 1s;
done
}
if [ -e "/slave" ]; then
echo "slave..."
cp /slave.cnf /etc/mysql/conf.d/
else
echo "master..."
cp /master.cnf /etc/mysql/conf.d/
fi
echo "master config ... "
/master.sh
echo "complete. "
if [ -n "$MASTER_HOST" ]; then
echo "replicaof $MASTER_HOST"
wait_for $MASTER_HOST $MASTER_PORT
/slave.sh
echo "Slave config complete."
fi1.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.
THE END
