黑龙江省农业网站建设情况,网站建设费与网络维护费区别,网站建设公司 专题制作,网站seo谷歌InnoDB Cluster 集群 Mysql-Router 代理层
前言
Mysql是现今最常用的关系型数据库之一#xff0c;高可用一直是我们对软件服务的要求。常见的Mysql高可用是主从配置#xff0c;在主节点挂掉后需要依赖监控脚本进行主从切换将从节点升级#xff0c;后台服务代码层面也…InnoDB Cluster 集群 Mysql-Router 代理层
前言
Mysql是现今最常用的关系型数据库之一高可用一直是我们对软件服务的要求。常见的Mysql高可用是主从配置在主节点挂掉后需要依赖监控脚本进行主从切换将从节点升级后台服务代码层面也需要进行相关配置。那有没有更简约的办法做到后台代码零侵入呢答案是有的本文就采用 Mysql 官方的集群模式加官方的 Router 代理层实现 Mysql 对后台服务的隐藏后台服务只需要像连接普通 Mysql 服务一样连接到 Router 即可。
这种方案优势非常明显
MySQL Router 是官方出品是轻量级代理程序后台应用不可见。Router 可自己实现读写分离。数据库服务器故障业务可以正常运行。由MySQL Router来进行自动下线不可用服务器和替换主节点。 现在直接把全套最佳实践正文发布如下Docker版本三节点。
1、InnoDB Cluster集群
1.1、Mysql8.0 standalone(建议至少3个节点才能保证高可用)
使用Mysql8.0镜像启动3个节点一主两从除server_id和report_host外其他配置均一致。
# docker-compose
services:mysql-server:# container_name: mysql-serverimage: mysql/mysql-server:8.0restart: always# volumes:# - /data/mysql-server:/var/lib/mysqlnetwork_mode: hostenvironment:MYSQL_ROOT_PASSWORD: rootMYSQL_ROOT_HOST: %command: [mysqld,--server_id1,--report_host$HOSTNAME, # 通信的ip地址--report_port3306, # 通信的端口--binlog_checksumNONE,--gtid_modeON,--enforce_gtid_consistencyON,--log_bin,--log_slave_updatesON,--master_info_repositoryTABLE,--relay_log_info_repositoryTABLE,--transaction_write_set_extractionXXHASH64,--usermysql,--skip-host-cache,--skip-name-resolve,--default_authentication_pluginmysql_native_password,--binlog_transaction_dependency_trackingWRITESET]1.2、使用mysql-shell建立集群
# 手动方式
mysqlsh --uri rootmysql-server-1// init.js
var password root
var clusterName mysqlClustertry {print(Setting up InnoDB cluster...\n);shell.connect(root127.0.0.1:3306, password)var cluster dba.createCluster(clusterName);print(Adding instances to the cluster.);cluster.addInstance({user: root, host: 127.0.0.1, port: 3307, password: password}, {recoveryMethod:clone})print(.);cluster.addInstance({user: root, host: 127.0.0.1, port: 3308, password: password}, {recoveryMethod:clone})print(.\nInstances successfully added to the cluster.);print(\nInnoDB cluster deployed successfully.\n);
} catch(e) {print(\nThe InnoDB cluster could not be created.\n\nError: e.message \n);
}1.3、使用docker自动建立集群
1.3.1、docker-entrypoint.sh
#!/bin/bash
set -eif [ -n $1 ]; thenexec $
fiif [ -z $MYSQL_HOST ]; thenecho -e MYSQL_HOST is required.exit 1
fi
if [ -z $MYSQL_PORT ]; thenecho -e MYSQL_PORT is required.exit 1
fi
if [ -z $MYSQL_USER ]; thenecho -e MYSQL_USER is required.exit 1
fi
if [ -z $MYSQL_PASSWORD ]; thenecho -e MYSQL_PASSWORD is required.exit 1
fimax_tries10
attempt_num0
until (echo /dev/tcp/$MYSQL_HOST/$MYSQL_PORT) /dev/null 21; doecho Waiting for mysql server $MYSQL_HOST ($attempt_num/$max_tries)sleep $(( attempt_num ))if [ attempt_num -eq max_tries ]; thenexit 1fi
doneif [ -n $MYSQLSH_SCRIPT ]; thenmysqlsh $MYSQL_USER$MYSQL_HOST:$MYSQL_PORT --password$MYSQL_PASSWORD -f $MYSQLSH_SCRIPT || true
fi
if [ -n $MYSQL_SCRIPT ]; thenmysqlsh $MYSQL_USER$MYSQL_HOST:$MYSQL_PORT --password$MYSQL_PASSWORD --sql -f $MYSQL_SCRIPT || true
fi1.3.2、Dockerfile
FROM alpine:3.18 as downloadARG pkgmysql-shell-8.0.33-linux-glibc2.12-x86-64bit
RUN wget https://dev.mysql.com/get/Downloads/MySQL-Shell/$pkg.tar.gz###
FROM debian:bullseye-slimARG pkgmysql-shell-8.0.33-linux-glibc2.12-x86-64bit
COPY --fromdownload /$pkg.tar.gz /opt
COPY docker-entrypoint.sh /bin/
RUN cd /opt \tar -xzf $pkg.tar.gz \ln -s /opt/$pkg/bin/mysqlsh /bin/ \rm -f /$pkg.tar.gz \chmod 755 /bin/docker-entrypoint.sh
ENTRYPOINT [docker-entrypoint.sh]
CMD []1.3.3、docker-compose
services:mysql-shell:container_name: mysql-shellimage: mysql-shell:8.0build: ./mysql-shell-builderrestart: on-failurevolumes:- ./scripts/:/scripts/environment:- MYSQL_HOSTmysql-server-1- MYSQL_PORT3306- MYSQL_USERroot- MYSQL_PASSWORDroot- MYSQLSH_SCRIPT/scripts/init.js- MYSQL_SCRIPT/scripts/init.sqldepends_on:- mysql-server-1- mysql-server-2- mysql-server-32、Mysql Router代理层
# docker-compose
services:mysql-router:container_name: mysql-routerimage: mysql/mysql-router:8.0restart: alwaysports:- 3306:6446environment:- MYSQL_HOSTmysql-server-1- MYSQL_PORT3306- MYSQL_USERroot- MYSQL_PASSWORDroot- MYSQL_INNODB_NUM_MEMBERS3 #Wait for this number of cluster instances to be online.- MYSQL_CREATE_ROUTER_USER0depends_on:- mysql-server-1- mysql-server-2- mysql-server-3- mysql-shell3、整合
3.1、目录结构
- PROJECT_DIRECTORY- mysql-shell-builder* docker-entrypoint.sh * Dockerfile- scripts* init.js* init.sql [optional]* docker-compose.yaml3.2、docker-compose.yaml
version: 3services:mysql-server-1:container_name: mysql-server-1image: mysql/mysql-server:8.0restart: alwaysvolumes:- /data/mysql-server-1:/var/lib/mysql# ports:# - 3301:3306environment:MYSQL_ROOT_PASSWORD: rootMYSQL_ROOT_HOST: %command: [mysqld,--server_id1,--binlog_checksumNONE,--gtid_modeON,--enforce_gtid_consistencyON,--log_bin,--log_slave_updatesON,--master_info_repositoryTABLE,--relay_log_info_repositoryTABLE,--transaction_write_set_extractionXXHASH64,--usermysql,--skip-host-cache,--skip-name-resolve,--default_authentication_pluginmysql_native_password,--binlog_transaction_dependency_trackingWRITESET]mysql-server-2:container_name: mysql-server-2image: mysql/mysql-server:8.0restart: alwaysvolumes:- /data/mysql-server-2:/var/lib/mysql# ports:# - 3302:3306environment:MYSQL_ROOT_PASSWORD: rootMYSQL_ROOT_HOST: %command: [mysqld,--server_id2,--binlog_checksumNONE,--gtid_modeON,--enforce_gtid_consistencyON,--log_bin,--log_slave_updatesON,--master_info_repositoryTABLE,--relay_log_info_repositoryTABLE,--transaction_write_set_extractionXXHASH64,--usermysql,--skip-host-cache,--skip-name-resolve,--default_authentication_pluginmysql_native_password,--binlog_transaction_dependency_trackingWRITESET]mysql-server-3:container_name: mysql-server-3image: mysql/mysql-server:8.0restart: alwaysvolumes:- /data/mysql-server-3:/var/lib/mysql# ports:# - 3303:3306environment:MYSQL_ROOT_PASSWORD: rootMYSQL_ROOT_HOST: %command: [mysqld,--server_id3,--binlog_checksumNONE,--gtid_modeON,--enforce_gtid_consistencyON,--log_bin,--log_slave_updatesON,--master_info_repositoryTABLE,--relay_log_info_repositoryTABLE,--transaction_write_set_extractionXXHASH64,--usermysql,--skip-host-cache,--skip-name-resolve,--default_authentication_pluginmysql_native_password,--binlog_transaction_dependency_trackingWRITESET]mysql-shell:container_name: mysql-shellimage: mysql-shell:8.0build: ./mysql-shell-builderrestart: on-failurevolumes:- ./scripts/:/scripts/environment:- MYSQL_HOSTmysql-server-1- MYSQL_PORT3306- MYSQL_USERroot- MYSQL_PASSWORDroot- MYSQLSH_SCRIPT/scripts/init.js# - MYSQL_SCRIPT/scripts/init.sqldepends_on:- mysql-server-1- mysql-server-2- mysql-server-3mysql-router:container_name: mysql-routerimage: mysql/mysql-router:8.0restart: alwaysports:- 3306:6446environment:- MYSQL_HOSTmysql-server-1- MYSQL_PORT3306- MYSQL_USERroot- MYSQL_PASSWORDroot- MYSQL_INNODB_NUM_MEMBERS3 #Wait for this number of cluster instances to be online.- MYSQL_CREATE_ROUTER_USER0depends_on:- mysql-server-1- mysql-server-2- mysql-server-3- mysql-shell后台启动
docker compose up -d后记
官方平台对服务透明自动故障处理想要的功能它都有。就是首次配置可能需要多花点时间但是参考本文相信你可以对这套实践有更快的理解欢迎点赞收藏
