docker部署mysql主主备份 haproxy代理（swarm）

发布时间：2023年12月22日

docker部署mysql主主备份 haproxy代理（swarm）

1. 环境准备

主机	IP
node1	192.168.56.100
node2	192.168.56.101

互开防火墙
默认安装了docker docker-compose

firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.56.100" accept"
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.56.101" accept"
firewall-cmd --reload
firewall-cmd --list-all

在这里插入图片描述

1.1. 在3台主机上配置swarm

mkdir -p /home/liuhmpackage/liuhm-4.0.0-x16_64 && cd /home/liuhmpackage/liuhm-4.0.0-x16_64

在node1上执行：

docker swarm init --advertise-addr 192.168.56.100

返回类似以下内容：

docker swarm join --token SWMTKN-1-614xi9dvksycykobgifxb4pgopc1wwgczwqct5wqkq8zao6tmx-0ds4jj3ozclrr2wukcaoakxso 192.168.56.100:2377

在node2上执行上面的返回结果：

docker swarm join --token SWMTKN-1-2c2xopn2rld8oltcof24sue370681ijhbo3bwcqarjlhq9lkea-2g53o5qn2anre4j9puv4hecrn 192.168.0.101:2377

1.2. 创建swarm网络

在node1上执行以下命令：

docker network create -d overlay --subnet 10.0.1.0/24 --attachable liuhm-net

# 检查网络，如果存在表示创建成功
docker network ls | grep liuhm-net

1.3 创建文件

复制下面的sh命令即可获取第二步所需的安装文件

mkdir -p /home/liuhmpackage/liuhm-4.0.0-x16_64/haproxy
cd /home/liuhmpackage/liuhm-4.0.0-x16_64/haproxy

cat > haproxy.cfg << EOF
global
    maxconn     4000

defaults
        log     global
        log 127.0.0.1 local3
        mode    http
        option  tcplog
        option  dontlognull
        retries 10
        option redispatch
        maxconn         2000
        timeout connect         10s
        #timeout client          1m
        #timeout server          1m
        timeout http-keep-alive 10s
        timeout check           10s
######## 监控界面配置 #################	
listen  admin_stats
	#监控界面的访问的IP和端口
	bind  0.0.0.0:8888
	#访问协议
    mode        http
	#URI相对地址
    stats uri   /dbs
	#统计报告格式
    stats realm     Global\ statistics
	#登陆帐户信息
    stats auth  admin:admin
    # 隐藏HAProxy的版本号
    stats hide-version
    # 管理界面，如果认证成功了，可通过webui管理节点
    stats admin if TRUE
    # 统计页面自动刷新时间
    stats  refresh  30s
listen  mysql
        bind 0.0.0.0:3306
        mode tcp
        #负载均衡算法（轮询算法）
	    #轮询算法：roundrobin
	    #权重算法：static-rr
	    #最少连接算法：leastconn
	    #请求源IP算法：source 
        balance  roundrobin
        # 监控检查需要一个无密码的账号
        # mysql健康检查  haproxy为mysql登录用户名(需要在实体数据有这个账户,且无密码)
	    # option mysql-check  user haproxy 
        server s1 mysql-master1:3306 check weight 1 maxconn 2000 inter 5000 rise 2 fall 2
        server s2 mysql-master2:3306 check weight 1 maxconn 2000 inter 5000 rise 2 fall 2 backup

	    # 使用keepalive检测死链
        option tcpka
EOF

cat > dokcer-stark-haproxy.yml << EOF
version: '3'

networks:
  liuhm-net:
    external: true 

services:
  haproxy:
    image: haproxy:alpine
    hostname: haproxy
    volumes:
      - "${PWD}/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg"
      - "/etc/localtime:/etc/localtime:ro"
    restart: always
    privileged: true
    ports:
      - 8888:8888
      - 3306:3306
    networks:
      - liuhm-net
    deploy:
      mode: global
EOF

mkdir -p /home/liuhmpackage/liuhm-4.0.0-x16_64/mysql/mysql-master1/conf
cd /home/liuhmpackage/liuhm-4.0.0-x16_64/mysql/mysql-master1/conf
cat > my.cnf << EOF
!includedir /etc/mysql/conf.d/ 
!includedir /etc/mysql/mysql.conf.d/
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-time-zone=+08:00
max_connections=1000

# 开启binlog
log-bin=mysql-bin
log-bin-index=mysql-bin.index
# 服务器唯一id，默认值1
server-id=1
max_binlog_size=1G
max_binlog_cache_size=1G
# 设置日志格式，默认值ROW
binlog_format=mixed
expire_logs_days=7
# 开启慢查询
slow_query_log=1
long_query_time=2
slow_query_log_file=/data/mysql/slow.log
# 错误日志
log_error=/var/log/mysql/error.log
# 设置需要复制的数据库，默认复制全部数据库
#binlog-do-db=mcp_manager
# 设置不需要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema
EOF

cd /home/liuhmpackage/liuhm-4.0.0-x16_64/mysql/mysql-master1


cat > docker-compose.yml << EOF
version: '3'

networks:
  liuhm-net:
    external: true
services:
  mysql-master1:
    image: mysql:5.7.23
    hostname: mysql-master1
    container_name: mysql-master1
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=hancloud1234!
      - TZ=Asia/Shanghai
    volumes:
      - $PWD/slowSql:/data/mysql
      - $PWD/data:/var/lib/mysql
      - $PWD/logs:/var/log/mysql
      - $PWD/conf/my.cnf:/etc/mysql/my.cnf
    privileged: true
    networks:
      liuhm-net:
        ipv4_address: 10.0.1.50
    command: ['mysqld','--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci','--default-time-zone=+08:00']
    entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld"
EOF


cat > install.sh << EOF
#!/bin/sh

CURRENT_DIR=$(
   cd "$(dirname "$0")"
   pwd
)
nowDate=$(date "+%Y%m%d%H%M%S")
logFileName="install_"$nowDate.log
touch $CURRENT_DIR/${logFileName}
# 日志函数
log(){
	#echo "["$(date "+%Y-%m-%d %H:%M:%S.")$((`date +%N`/1000000))"] " $1 | tee -a $CURRENT_DIR/$logFileName
	
	echo "["$(date "+%Y-%m-%d %H:%M:%S")"]"$1 | tee -a $CURRENT_DIR/$logFileName
}

master1Ip="10.0.1.50"
master2Ip="10.0.1.51"

#///
log "【安装】开始安装"

log "【安装】开始安装 ${master1Ip}" 


checkMysql()
{

	status=true
	while($status)
	do 
		echo "Check MySQL status......"
		if docker exec -it $1 bash -c 'mysqladmin ping -h localhost -uroot -phancloud1234! '; then
			echo "Check that MySQL has been started!"
			status=false
		else
			echo "Waiting for MySQL to start..."
			sleep 5
		fi
	done
}

installMaster1(){
	# my.cnf 权限不能是777，会被忽略
	chmod 644 ./conf/my.cnf
	log "【启动程序】开始启动" 
	docker-compose up -d
	sleep 20
	checkMysql mysql-master1

	# 创建slave用户
	# 设置密码
	# 授予复制权限
	# 刷新权限
	log "【初始化用户】创建用户" 
	docker exec -it mysql-master1 bash -c "mysql -uroot -phancloud1234! -e \"CREATE USER 'slave'@'%';ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'hancloud@1234';GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';FLUSH PRIVILEGES;\"" 
	log "【初始化用户】创建slave用户成功"
	
	# mysql-bin.000003

	sleep 10
	
}


installMaster1
log "【主1结束】主1执行完1"
log "【主1结束】下面的语句在主2安装完成后执行"

fileName=$(docker exec -it mysql-master1 bash -c "mysql -uroot -phancloud1234! -e \"SHOW MASTER STATUS;\"" |grep "mysql-bin"|awk -F '|' '{print $2}' | awk '{gsub(/^\s+|\s+$/, "");print}') 
	# 939
pos=$(docker exec -it mysql-master1 bash -c "mysql -uroot -phancloud1234! -e \"SHOW MASTER STATUS;\"" |grep "mysql-bin"|awk -F '|' '{print $3}' | awk '{gsub(/^\s+|\s+$/, "");print}') 

echo "docker exec -it mysql-master2 bash -c \"mysql -uroot -phancloud1234! \""
echo "CHANGE MASTER TO MASTER_HOST='${master1Ip}',MASTER_USER='slave',MASTER_PASSWORD='hancloud@1234', MASTER_PORT=3306,MASTER_LOG_FILE='${fileName}',MASTER_LOG_POS=${pos};"

echo "START SLAVE;"
echo "SHOW SLAVE STATUS\G"
echo "exit"

EOF





mkdir -p /home/liuhmpackage/liuhm-4.0.0-x16_64/mysql/mysql-master2/conf
cd /home/liuhmpackage/liuhm-4.0.0-x16_64/mysql/mysql-master2/conf
cat > my.cnf << EOF
!includedir /etc/mysql/conf.d/ 
!includedir /etc/mysql/mysql.conf.d/
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-time-zone=+08:00
max_connections=1000

# 开启binlog
log-bin=mysql-bin
log-bin-index=mysql-bin.index
# 服务器唯一id，默认值1
server-id=2
max_binlog_size=1G
max_binlog_cache_size=1G
# 设置日志格式，默认值ROW
binlog_format=mixed
expire_logs_days=7
# 开启慢查询
slow_query_log=1
long_query_time=2
slow_query_log_file=/data/mysql/slow.log
# 错误日志
log_error=/var/log/mysql/error.log
# 设置需要复制的数据库，默认复制全部数据库
#binlog-do-db=mcp_manager
# 设置不需要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema
EOF

cd /home/liuhmpackage/liuhm-4.0.0-x16_64/mysql/mysql-master2


cat > docker-compose.yml << EOF
version: '3'
networks:
  liuhm-net:
    external: true
    
services:
  mysql-master2:
    image: mysql:5.7.23
    hostname: mysql-master2
    container_name: mysql-master2
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=hancloud1234!
      - TZ=Asia/Shanghai
    volumes:
      - $PWD/slowSql:/data/mysql
      - $PWD/data:/var/lib/mysql
      - $PWD/logs:/var/log/mysql
      - $PWD/conf/my.cnf:/etc/mysql/my.cnf
    privileged: true
    networks:
      liuhm-net:
        ipv4_address: 10.0.1.51
    command: ['mysqld','--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci','--default-time-zone=+08:00']
    entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld"
EOF


cat > install.sh << EOF
#!/bin/sh

CURRENT_DIR=$(
   cd "$(dirname "$0")"
   pwd
)
nowDate=$(date "+%Y%m%d%H%M%S")
logFileName="install_"$nowDate.log
touch $CURRENT_DIR/${logFileName}
# 日志函数
log(){
	#echo "["$(date "+%Y-%m-%d %H:%M:%S.")$((`date +%N`/1000000))"] " $1 | tee -a $CURRENT_DIR/$logFileName
	
	echo "["$(date "+%Y-%m-%d %H:%M:%S")"]"$1 | tee -a $CURRENT_DIR/$logFileName
}

master1Ip="10.0.1.50"
master2Ip="10.0.1.51"

#///
log "【安装】开始安装"

log "【安装】开始安装 ${master2Ip}" 


checkMysql()
{

	status=true
	while($status)
	do 
		echo "Check MySQL status......"
		if docker exec -it $1 bash -c 'mysqladmin ping -h localhost -uroot -phancloud1234! '; then
			echo "Check that MySQL has been started!"
			status=false
		else
			echo "Waiting for MySQL to start..."
			sleep 5
		fi
	done
}

installMaster1(){
	# my.cnf 权限不能是777，会被忽略
	chmod 644 ./conf/my.cnf
	log "【启动程序】开始启动" 
	docker-compose up -d
	sleep 20
	checkMysql mysql-master2

	# 创建slave用户
	# 设置密码
	# 授予复制权限
	# 刷新权限
	log "【初始化用户】创建用户" 
	docker exec -it mysql-master2 bash -c "mysql -uroot -phancloud1234! -e \"CREATE USER 'slave'@'%';ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'hancloud@1234';GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';FLUSH PRIVILEGES;\"" 
	log "【初始化用户】创建slave用户成功"
	
	# mysql-bin.000003

	sleep 10
	
}


installMaster1
log "【主2结束】主2执行完2"
log "【主2结束】下面的语句在主2安装完成后执行"

fileName=$(docker exec -it mysql-master2 bash -c "mysql -uroot -phancloud1234! -e \"SHOW MASTER STATUS;\"" |grep "mysql-bin"|awk -F '|' '{print $2}' | awk '{gsub(/^\s+|\s+$/, "");print}') 
	# 939
pos=$(docker exec -it mysql-master2 bash -c "mysql -uroot -phancloud1234! -e \"SHOW MASTER STATUS;\"" |grep "mysql-bin"|awk -F '|' '{print $3}' | awk '{gsub(/^\s+|\s+$/, "");print}') 


echo "docker exec -it mysql-master1 bash -c \"mysql -uroot -phancloud1234! \""
echo "CHANGE MASTER TO MASTER_HOST='${master2Ip}',MASTER_USER='slave',MASTER_PASSWORD='hancloud@1234', MASTER_PORT=3306,MASTER_LOG_FILE='${fileName}',MASTER_LOG_POS=${pos};"

echo "START SLAVE;"
echo "SHOW SLAVE STATUS\G"
echo "exit"


EOF

1.4 拷贝文件

scp -r /home/liuhmpackage root@192.168.56.101:/home

2. mysql 集群安装

2.1. 启动 mysql-master1

进入 liuhm1服务器

mkdir -p /home/liuhmmysql/mysql-master1 && cd /home/liuhmmysql/mysql-master1

\cp -r /home/liuhmpackage/liuhm-4.0.0-x16_64/haproxy /home/liuhmmysql

\cp -r /home/liuhmpackage/liuhm-4.0.0-x16_64/mysql/mysql-master1/* ./

bash install.sh

出现下面的日志代表启动成功
在这里插入图片描述

2.2. 启动 mysql-master2

进入 liuhm2服务器

mkdir -p /home/liuhmmysql/mysql-master2 && cd /home/liuhmmysql/mysql-master2

\cp -r /home/liuhmpackage/liuhm-4.0.0-x16_64/haproxy /home/liuhmmysql

\cp -r /home/liuhmpackage/liuhm-4.0.0-x16_64/mysql/mysql-master2/* ./

bash install.sh

出现下面的日志代表启动成功
在这里插入图片描述

2.3. mysql-master1执行mysql-master2 完成后的语句

进入 liuhm1服务器

docker exec -it mysql-master1 bash -c "mysql -uroot -phancloud1234! "

CHANGE MASTER TO MASTER_HOST='10.0.1.51',MASTER_USER='slave',MASTER_PASSWORD='hancloud@1234', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=939;

START SLAVE;

SHOW SLAVE STATUS\G

exit

在这里插入图片描述

2.4. mysql-master2执行mysql-master1 完成后的语句

进入 liuhm2服务器

docker exec -it mysql-master2 bash -c "mysql -uroot -phancloud1234! "

CHANGE MASTER TO MASTER_HOST='10.0.1.50',MASTER_USER='slave',MASTER_PASSWORD='hancloud@1234', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=939;

START SLAVE;

SHOW SLAVE STATUS\G

exit

在这里插入图片描述

2.5. 启动代理

进入 liuhm1服务器

cd /home/liuhmmysql/haproxy
docker stack deploy -c dokcer-stark-haproxy.yml haproxy

mysql 连接方式 haproxy:3306 admin/hancloud1234!

文章来源:https://blog.csdn.net/qq_36120342/article/details/135147000
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！