文章转载来源:
一、背景说明
为了完成线上项目的高可用演练,单机数据库要改成高可用架构,公司没有工时给开发去修改架构,只能在应用侧无修改的条件下,修改数据库的架构。经过与项目经理沟通,他们想选择keepalived+双主的架构,但是到环境一看,数据库、应用全都是docker部署的,为了整齐划一,未来好维护,我选择了群都用docker部署。可是docker环境中keepalived的MySQL的主从切换脚本我没有解决,所以我弃用keepalived+双主的架构。改成了keepalived+router+innodb cluster,这样的好处是数据库的主从切换不用keepalived去控制,keepalived只负责router的探活功能。
二、部署步骤
1、docker-compose部署innodb cluster
第一台机器
创建对应目录
mkdir -p mysql3310/{conf,data,logs}
创建docker-compose.yml
vim docker-compose.yml
加入:
version: '3'
services:
mysql:
image: mysql:8.0.35
container_name: mysql01
restart: always
extra_hosts:
- "mysql01:10.8.91.10"
- "mysql02:10.8.91.11"
- "mysql03:10.8.91.12"
hostname: mysql01
env_file: .env
network_mode: host
volumes:
- /etc/localtime:/etc/localtime:ro
- ${DIR_MYSQL_DATA}:/var/lib/mysql
- /mysql3310/logs:/var/lib/logs
- ./conf/my.cnf:/etc/my.cnf
environment:
MYSQL_ROOT_PASSWORD: "${MYSQL_ROOT_PASSWORD}"
MYSQL_USER: apps #创建apps用户
MYSQL_PASSWORD: XXXXXX #设置apps用户的密码
TZ: Asia/Shanghai
command: --init_connect='SET NAMES utf8mb4' --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --innodb_flush_log_at_trx_commit=2
创建.env文件
vim .env
加入:
# Mysql
MYSQL_ROOT_PASSWORD=xxxxxxxxx
DIR_MYSQL_DATA=/mysql3310/data
创建my.cnf
vim ./conf/my.cnf
加入:
[mysqld]
server-id=103310
port=3310
mysqlx_port=33160
datadir=/var/lib/mysql
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
general_log=ON
general_log_file=/var/lib/logs/mysql.log
log-error=/var/lib/logs/mysql.err
pid-file=/var/lib/logs/mysql.pid
enforce_gtid_consistency=on
gtid_mode=on
binlog_transaction_dependency_tracking=WRITESET
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
第二、三台机器使用第一台机器的docker-compose.yml并进行如下修改:
container_name: mysql01 -->mysql02\mysql03
hostname: mysql01 -->mysql02\mysql03
server-id=103310 -->113310\server-id=123310
启动MySQL服务
docker-compose -f docker-compose.yml up -d
用mysqlshell组建集群
shell.connect('root@10.8.91.10:3310')
var sss=dba.createCluster('ssCluster')
sss.addInstance('root@10.8.91.11:3310')
sss.addInstance('root@10.8.91.12:3310')
组建集群前,在主机层面添加hosts:
cat >>/etc/hosts