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

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

news 2025/6/29 4:32:35

InnoDB Cluster 集群 & Mysql-Router 代理层

前言

Mysql是现今最常用的关系型数据库之一，高可用一直是我们对软件服务的要求。常见的Mysql高可用是主从配置，在主节点挂掉后需要依赖监控脚本进行主从切换将从节点升级，后台服务代码层面也需要进行相关配置。那有没有更简约的办法做到后台代码零侵入呢，答案是有的，本文就采用 Mysql 官方的集群模式加官方的 Router 代理层实现 Mysql 对后台服务的隐藏，后台服务只需要像连接普通 Mysql 服务一样连接到 Router 即可。

这种方案优势非常明显：

  1. MySQL Router 是官方出品，是轻量级代理程序，后台应用不可见。
  2. Router 可自己实现读写分离。
  3. 数据库服务器故障，业务可以正常运行。由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

后记

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

http://www.lryc.cn/news/90311.html

相关文章：

  • RabbitMQ系列-概念及安装
  • 进程间通信之共享内存
  • 网络连接中的舔狗协议
  • 一分钟了解乐观锁、悲观锁、共享锁、排它锁、行锁、表锁以及使用场景
  • 【C++】C++ 中的 IO 流
  • QFuture的使用
  • 通过dockerfile将nginx、前端和后端封装成一个镜像
  • 如何利用CiteSpace快速锁定领域内最新研究热点并制作精美的可视化专题图？
  • 嵌入式开发从入门到精通之第二十二节：蓝牙芯片CC254x ADC 电压测量
  • 北邮22信通：二叉树显示路径的两种方法 递归函数保存现场返回现场的实例
  • vue 3 第二十八章：组件十二（组件的v-model、多v-model）
  • LCD 显示
  • 互联网医院开发|在线问诊系统架构设计功能有哪些？
  • 数据安全运营有效管理-数据安全复合治理框架和模型解读(1)
  • 【刷题之路】LeetCode 面试题 03.02. 栈的最小值
  • 如何处理图片排重（精准排重，相似排重）
  • 盐城北大青鸟“北大青鸟杯”IT精英挑战赛设中心评审隆重开赛
  • Pluma 插件管理框架
  • Leetcode11 盛最多水的容器
  • Java
  • 第十四章行为性模式—策略模式
  • Leaflet基本用法
  • Unity | HDRP高清渲染管线学习笔记：示例场景解析
  • 【Netty】Netty 编码器（十三）
  • Netty和Tomcat的区别、性能对比
  • chatgpt赋能python：Python函数调用局部变量-深入了解
  • Android 12.0 NavigationBarView 导航栏 左边显示的修改
  • Mybatis源码细节探究：二级缓存Cache对象是在什么时候创建的？
  • 【数据库】无效数据：软件测试对无效数据的处理
  • 高精度电压源如何设计出来的