参考文章:https://blog.breezelin.cn/practice-mysql-mha-docker-compose.html
参考文章的git地址:https://github.com/breeze2/mysql-mha-docker/tree/2dcd29996a9ecade0eeda96434180c998368bdfa
我的部署文件:待补充
要搭建一主一备的mysql集群。
proxysql负责读写分离,MHA负责故障切换,如果不担心脑裂的风险,故障切换用keepalived更简单。
一、MHA的搭建
mha包含有mha_manager与mha_node,node部署在每个mysql的机器上。manager管理这些node,并在故障发生时,进行主从的切换。
1.镜像准备
可以自己制作镜像,也可以使用网上已有的镜像,我这里使用的是这两个:
docker pull breeze2/mha4mysql-node:0.57
docker pull breeze2/mha4mysql-manager:0.57
2.docker-compose基础目录
准备一个docker-compose的基础目录,以后执行的命令均默认在此目录下
mkdir /home/docker/proxysql-MHA
再创建一个mha子目录,存放mha相关的配置文件与映射目录,
再创建一个scripts目录,存放mha启动相关的便捷脚本
mkdir /home/docker/proxysql-MHA/mha
mkdir /home/docker/proxysql-MHA/scripts
3.编排docker-compose.yml文件
在目录/home/docker/proxysql-MHA下,创建文件docker-compose.yml,包含3个容器,一个主库、一个备库,以及一个管理节点。
version: "3"
services:
mha_master:
image: breeze2/mha4mysql-node:0.57
container_name: mha_master
restart: always
networks:
mhanet:
ipv4_address: 10.5.0.10
ports:
- "3307:3306"
volumes:
- "/etc/localtime:/etc/localtime:ro"
- "./mha/mha_share/:/root/mha_share/"
- "./mha/mha_master/data/:/var/lib/mysql/"
- "./mha/mha_master/conf/:/etc/mysql/conf.d/"
env_file:
- ./parameters.env
environment:
- CONTAINER_NAME=mha_master
mha_slave:
image: breeze2/mha4mysql-node:0.57
container_name: mha_slave
restart: always
depends_on:
- mha_master
networks:
mhanet:
ipv4_address: 10.5.0.11
ports:
- "3308:3306"
volumes:
- "/etc/localtime:/etc/localtime:ro"
- "./mha/mha_share/:/root/mha_share/"
- "./mha/mha_slave/data/:/var/lib/mysql/"
- "./mha/mha_slave/conf/:/etc/mysql/conf.d/"
env_file:
- ./parameters.env
environment:
- CONTAINER_NAME=mha_slave
mha_manager:
image: breeze2/mha4mysql-manager:0.57
container_name: mha_manager
depends_on:
- mha_master
- mha_slave
restart: always
networks:
mhanet:
ipv4_address: 10.5.0.9
volumes:
- "/etc/localtime:/etc/localtime:ro"
- "./mha/mha_share/:/root/mha_share/"
- "./mha/mha_manager/conf:/etc/mha"
- "./mha/mha_manager/work:/usr/local/mha"
entrypoint: "tailf /dev/null"
env_file:
- ./parameters.env
environment:
- CONTAINER_NAME=mha_manager
networks:
mhanet:
driver: bridge
ipam:
config:
- subnet: 10.5.0.0/16
同目录下的环境变量文件,parameters.env:
ROOT_PASSWORD=123456
MYSQL_ROOT_PASSWORD=root
MHA_SHARE_SCRIPTS_PATH=/root/mha_share/scripts
MHA_SHARE_SSHKEYS_PATH=/root/mha_share/sshkeys
4.准备mysql的配置文件
创建目录:
#创建master节点的目录
mkdir ./mha/mha_master
mkdir ./mha/mha_master/conf
mkdir ./mha/mha_master/data
chmod 777 ./mha/mha_master/data
#创建slave节点的目录
mkdir ./mha/mha_slave
mkdir ./mha/mha_slave/conf
mkdir ./mha/mha_slave/data
chmod 777 ./mha/mha_slave/data
mysql主库的配置文件,执行 vim mha/mha_master/conf/my.cnf
就是常见的主从同步的配置,注意server-id不能重复以及自增id的起点不能重复
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
replicate-ignore-db=mysql
auto_increment_increment=2
auto_increment_offset=1
expire_logs_days=7
mysql从库的配置文件,执行 vim mha/mha_slave/conf/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
binlog-ignore-db=mysql
replicate-ignore-db=mysql
auto_increment_increment=2
auto_increment_offset=2
expire_logs_days=7
#本库只读。主库挂了以后,mha会改变只读标记
read_only=ON
relay_log_purge=0
5.准备mha的配置文件以及ssh脚本和主从配置脚本
创建目录:
#创建MHA的管理节点的目录
mkdir mha/mha_manager
mkdir mha/mha_manager/conf
#创建ssh的脚本及存放sshkey的目录
mkdir mha/mha_share
mkdir mha/mha_share/scripts
mkdir mha/mha_share/sshkeys
manager的配置文件,执行 vim mha/mha_manager/conf/app1.conf
user和password是数据库的账户密码,repl_user为后面主从同步创建的用户【写在脚本里了】
那俩目录是docker里的目录,数据库的地址则是上面docker-compose.yml中配置的内联网络,candidate_master的意思是该库可以是备选主库
[server default]
user=root
password=root
ssh_user=root
manager_workdir=/usr/local/mha
remote_workdir=/usr/local/mha
repl_user=myslave
repl_password=myslave
[server0]
hostname=10.5.0.10
candidate_master=1
[server1]
hostname=10.5.0.11
candidate_master=1
接下来准备脚本了,脚本很简单,基本都能看懂。
sshKey生成脚本 vim mha/mha_share/scripts/ssh_generate_key.sh
ssh-keygen -t rsa -P "" -f /root/.ssh/id_rsa
if [ ! -d "$MHA_SHARE_SSHKEYS_PATH" ];then
mkdir "$MHA_SHARE_SSHKEYS_PATH"
fi
cp /root/.ssh/id_rsa.pub "$MHA_SHARE_SSHKEYS_PATH/id_rsa_$CONTAINER_NAME.pub"
sshKey共享写入脚本 vim mha/mha_share/scripts/ssh_auth_keys.sh
cat $MHA_SHARE_SSHKEYS_PATH/*.pub > /root/.ssh/authorized_keys
mysql的主库主从同步脚本 vim mha/mha_share/scripts/mysql_grant_slave.sh
mysql -u root -p$MYSQL_ROOT_PASSWORD <<EOSQL
GRANT ALL ON *.* TO myuser@'%' IDENTIFIED BY 'mypass';
GRANT ALL privileges ON *.* TO monitor@"%" IDENTIFIED BY "monitor";
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'%' IDENTIFIED BY 'myslave';
reset master;
EOSQL
mysql的从库主从同步脚本 vim mha/mha_share/scripts/mysql_start_slave1.sh
mysql -u root -p$MYSQL_ROOT_PASSWORD <<EOSQL
GRANT ALL ON *.* TO myuser@'%' IDENTIFIED BY 'mypass';
GRANT ALL privileges ON *.* TO monitor@"%" IDENTIFIED BY "monitor";
CHANGE MASTER TO MASTER_HOST='10.5.0.10', MASTER_USER='myslave', MASTER_PASSWORD='myslave', MASTER_CONNECT_RETRY=60;
start slave;
EOSQL
mysql的从库主从同步脚本 vim mha/mha_share/scripts/mysql_start_slave2.sh
mysql -u root -p$MYSQL_ROOT_PASSWORD <<EOSQL
GRANT ALL ON *.* TO myuser@'%' IDENTIFIED BY 'mypass';
GRANT ALL privileges ON *.* TO monitor@"%" IDENTIFIED BY "monitor";
CHANGE MASTER TO MASTER_HOST='10.5.0.11', MASTER_USER='myslave', MASTER_PASSWORD='myslave', MASTER_CONNECT_RETRY=60;
start slave;
EOSQL
6.便捷脚本准备
在基础目录下的scripts目录下,准备一些便捷脚本
ssh启动脚本, vim scripts/ssh_start.sh ,如下
docker exec -it mha_master /bin/bash service ssh start
docker exec -it mha_slave /bin/bash service ssh start
docker exec -it mha_manager /bin/bash service ssh start
sshKey共享脚本, vim scripts/ssh_share.sh
docker exec -it mha_master /bin/bash /root/mha_share/scripts/ssh_generate_key.sh
docker exec -it mha_slave /bin/bash /root/mha_share/scripts/ssh_generate_key.sh
docker exec -it mha_manager /bin/bash /root/mha_share/scripts/ssh_generate_key.sh
docker exec -it mha_master /bin/bash /root/mha_share/scripts/ssh_auth_keys.sh
docker exec -it mha_slave /bin/bash /root/mha_share/scripts/ssh_auth_keys.sh
docker exec -it mha_manager /bin/bash /root/mha_share/scripts/ssh_auth_keys.sh
ssh通讯检查脚本,vim scripts/mha_check_ssh.sh
docker exec -it mha_manager masterha_check_ssh --conf=/etc/mha/app1.conf
数据库主从同步脚本,vim scripts/mysql_set_mbs.sh
docker exec -it mha_master /bin/bash /root/mha_share/scripts/mysql_grant_slave.sh
docker exec -it mha_slave /bin/bash /root/mha_share/scripts/mysql_grant_slave.sh
docker exec -it mha_slave /bin/bash /root/mha_share/scripts/mysql_start_slave1.sh
docker exec -it mha_master /bin/bash /root/mha_share/scripts/mysql_start_slave2.sh
主从同步关系检查脚本, vim scripts/mha_check_repl.sh
docker exec -it mha_manager masterha_check_repl --conf=/etc/mha/app1.conf
mha监控启动脚本, vim scripts/mha_start_manager.sh
docker exec -it mha_manager masterha_manager --conf=/etc/mha/app1.conf
7.启动MHA集群
#创建并启动docker容器
docker-compose up -d
#启动各个容器的ssh服务,如果重启了容器,只需执行此步骤即可
sh ./scripts/ssh_start.sh
#将各个容器的sshKey共享给其它容器
sh ./scripts/ssh_share.sh
#检查各个容器间,ssh互通情况
sh ./scripts/mha_check_ssh.sh
#配置数据库的主从关系
sh ./scripts/mysql_set_mbs.sh
#检查数据库的主从关系
sh ./scripts/mha_check_repl.sh
还有最后一步,启动mha的监控,这是一个监控的脚本,所以最好放在后台运行,我这里使用了screen程序,不知道的可以百度。
#创建mha监控的会话
screen -S mha_manager
#默认进入了会话,执行监控脚本
sh ./scripts/mha_start_manager.sh
#退出会话,按键盘上的Ctrl+A+D
#查看已有会话
screen -ls
#恢复会话
screen -r mha_manager
#关闭会话,在会话内执行
exit
如果有容器重启了,只需执行启动ssh的脚本即可,如果mha的监控脚本停下来了,也要再执行。
二、proxysql部署
1.下载镜像
proxysql有官方的镜像,下载官方镜像最新版即可
docker pull proxysql/proxysql
2.编排docker-compose.yml文件
还是同一个docker-compose.yml文件,再加一个容器即可
proxysql:
image: proxysql/proxysql
restart: always
networks:
mhanet:
ipv4_address: 10.5.0.2
ports:
- "6032:6032"
- "6033:6033"
environment:
MONITOR_CONFIG_CHANGE: "true"
volumes:
- "/etc/localtime:/etc/localtime:ro"
- "./proxysql/proxysql.cnf:/etc/proxysql.cnf:ro"
3.准备proxysql的配置文件
创建目录
#创建proxysql的目录
mkdir /home/docker/proxysql-MHA/proxysql
创建配置文件,vim proxusql/proxysql.cnf, 内容如下
#file proxysql.cfg
datadir="/var/lib/proxysql"
admin_variables=
{
#登录proxysql的管理库时使用的账号,admin只能在本地使用,必须再配一个
admin_credentials="admin:admin;root:root"
mysql_ifaces="0.0.0.0:6032"
}
#proxysql的地址,在建立proxysql集群时,有用
proxysql_servers =
(
{
hostname="10.5.0.2"
port=6032
comment="proxysql_01"
}
)
#一些变量,monitor_username这个要在数据库中建立相应账户
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
# 数据库的地址,hostgroup时数据库的组别,
mysql_servers =
(
{ address="10.5.0.10" , port=3306 , hostgroup=1 },
{ address="10.5.0.11" , port=3306 , hostgroup=2 }
)
# proxysql最终代理库的账号密码
mysql_users:
(
{
username = "myuser"
password = "mypass"
default_hostgroup = 1
active = 1
}
)
#mysql的规则,读写分离规则,读走组别2,写走组别1
mysql_query_rules:
(
{
rule_id=100
active=1
schemaname="test"
username="myuser"
match_digest="(?i)^SELECT.*$"
destination_hostgroup=2
apply=1
},
{
rule_id=200
active=1
schemaname="test"
username="myuser"
match_digest="(?i)^(?!SELECT).*$"
destination_hostgroup=1
apply=1
}
)
scheduler=
(
# {
# id=1
# active=0
# interval_ms=10000
# filename="/var/lib/proxysql/proxysql_galera_checker.sh"
# arg1="0"
# arg2="0"
# arg3="0"
# arg4="1"
# arg5="/var/lib/proxysql/proxysql_galera_checker.log"
# }
)
#readongly为0的库认为是写库,readonly为1的库认为是读库,搭配mha切换主从使用
mysql_replication_hostgroups=
(
{
writer_hostgroup=1
reader_hostgroup=2
comment="test repl 1"
}
)
4.启动proxysql
docker-compose up -d proxysql
登入proxysql的管理数据库(最好用命令行模式),宿主机IP+PORT,账户密码root/root
mysql -h 192.168.3.237 -P 6032 -u root -proot
检查当前活跃的数据库,以及分组状况
select * from runtime_mysql_servers;
结果:能看到 两个数据库一个是组1(写),一个是组2(读),写都走10.5.0.10,读都走10.5.0.11
这时我们把主库关掉,然后再看下情况。
回到服务器的/home/docker/proxysql-MHA目录,执行关闭master的数据库的命令
#先把容器自动重启关闭
docker update --restart=no mha_master
#关闭mha_master 的数据库
docker exec -it mha_master mysqladmin shutdown -uroot -proot
使用命令screen -r mha_manager,回到mha监控的会话,可以看到mha进行了主从切换
再回到mha的管理库,查看表runtime_mysql_servers,可以看到10.5.0.11这个库即在写组,也在读组,完成了故障转移。
若想恢复,先启动主库,再启动ssh,(等数据从从库同步到主库完成后),再重配主从同步,把从库readonly设为1,删除上次failover的标记文件,然后再次启动manager的监控进程
从库只读设置
set GLOBAL read_only=1
删除上次failover的标记文件的命令
docker exec -it mha_manager rm /usr/local/mha/app1.failover.complete
如此就会恢复原来的主从状态。