docker搭建proxySql+MHA的读写分离与故障切换的数据库集群

参考文章：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

如此就会恢复原来的主从状态。