当前位置：首页 > news >正文

InnoDB Cluster集群Mysql Router代理层最佳实践

news 2025/7/12 9:20:12

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_id=1","--report_host=$HOSTNAME", # 通信的ip地址"--report_port=3306", # 通信的端口"--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_slave_updates=ON","--master_info_repository=TABLE","--relay_log_info_repository=TABLE","--transaction_write_set_extraction=XXHASH64","--user=mysql","--skip-host-cache","--skip-name-resolve","--default_authentication_plugin=mysql_native_password","--binlog_transaction_dependency_tracking=WRITESET"]

1.2、使用mysql-shell建立集群

# 手动方式
mysqlsh --uri "root@mysql-server-1"

// init.js
var password = "root"
var clusterName = "mysqlCluster"try {print('Setting up InnoDB cluster...\n');shell.connect('root@127.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_tries=10
attempt_num=0
until (echo > "/dev/tcp/$MYSQL_HOST/$MYSQL_PORT") >/dev/null 2>&1; 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
fi

1.3.2、Dockerfile

FROM alpine:3.18 as downloadARG pkg='mysql-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 pkg='mysql-shell-8.0.33-linux-glibc2.12-x86-64bit'
COPY --from=download "/$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_HOST=mysql-server-1- MYSQL_PORT=3306- MYSQL_USER=root- MYSQL_PASSWORD=root- MYSQLSH_SCRIPT=/scripts/init.js- MYSQL_SCRIPT=/scripts/init.sqldepends_on:- mysql-server-1- mysql-server-2- mysql-server-3

2、Mysql Router代理层

# docker-compose
services:mysql-router:container_name: mysql-routerimage: mysql/mysql-router:8.0restart: alwaysports:- 3306:6446environment:- MYSQL_HOST=mysql-server-1- MYSQL_PORT=3306- MYSQL_USER=root- MYSQL_PASSWORD=root- MYSQL_INNODB_NUM_MEMBERS=3 #Wait for this number of cluster instances to be online.- MYSQL_CREATE_ROUTER_USER=0depends_on:- mysql-server-1- mysql-server-2- mysql-server-3- mysql-shell

3、整合

3.1、目录结构

- <PROJECT_DIRECTORY>- mysql-shell-builder* docker-entrypoint.sh    * Dockerfile- scripts* init.js* init.sql [optional]* docker-compose.yaml

3.2、docker-compose.yaml

version: '3'services: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_id=1","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_slave_updates=ON","--master_info_repository=TABLE","--relay_log_info_repository=TABLE","--transaction_write_set_extraction=XXHASH64","--user=mysql","--skip-host-cache","--skip-name-resolve","--default_authentication_plugin=mysql_native_password","--binlog_transaction_dependency_tracking=WRITESET"]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_id=2","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_slave_updates=ON","--master_info_repository=TABLE","--relay_log_info_repository=TABLE","--transaction_write_set_extraction=XXHASH64","--user=mysql","--skip-host-cache","--skip-name-resolve","--default_authentication_plugin=mysql_native_password","--binlog_transaction_dependency_tracking=WRITESET"]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_id=3","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_slave_updates=ON","--master_info_repository=TABLE","--relay_log_info_repository=TABLE","--transaction_write_set_extraction=XXHASH64","--user=mysql","--skip-host-cache","--skip-name-resolve","--default_authentication_plugin=mysql_native_password","--binlog_transaction_dependency_tracking=WRITESET"]mysql-shell:container_name: mysql-shellimage: mysql-shell:8.0build: ./mysql-shell-builderrestart: on-failurevolumes:- ./scripts/:/scripts/environment:- MYSQL_HOST=mysql-server-1- MYSQL_PORT=3306- MYSQL_USER=root- MYSQL_PASSWORD=root- 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_HOST=mysql-server-1- MYSQL_PORT=3306- MYSQL_USER=root- MYSQL_PASSWORD=root- MYSQL_INNODB_NUM_MEMBERS=3 #Wait for this number of cluster instances to be online.- MYSQL_CREATE_ROUTER_USER=0depends_on:- mysql-server-1- mysql-server-2- mysql-server-3- mysql-shell

后台启动

docker compose up -d

后记

官方平台，对服务透明，自动故障处理，想要的功能它都有。就是首次配置可能需要多花点时间，但是参考本文，相信你可以对这套实践有更快的理解，欢迎点赞收藏！

前言