MySql高可用集群MySQL Router负载均衡读写分离

发布时间：2025-01-20 10:38:54

37 阅读

0 评论

文章标签： role bootstrap

名词解释
MGR: Mysql Group Replication 组复制，多台MySQL服务器在同一组中会自动保持同步状态，当某台服务器故障时，整个复制组依然可以保持正常并对外提供服务。

InnoDB 副本集（ReplicaSet） 无缝衔接其他 MySQL 官方提供的应用程序（MySQL Shell、MySQL Router），提供了另一种易于使用的编程方式来处理复制，属于复制（Replication）的简易增强版。

组复制（Group Replication） 是一种弹性伸缩、高可用、容错的复制拓扑，属于复制（Replication）的分布式高可用版本，但其本身不提供客户端连接重定向、故障转移、负载均衡等功能。

InnoDB 集群（Cluster） 是InnoDB 副本集（ReplicaSet）与组复制（Group Replication）的合成版，是InnoDB 副本集（ReplicaSet）的高可用版，是组复制（Group Replication）的简易、自动化、可编程的增强版。

MySQL InnoDB集群（Cluster）提供了一个集成的，本地的，HA解决方案。Mysq Innodb Cluster是利用组复制的 pxos 协议，保障数据一致性，组复制支持单主模式和多主模式。

MySQL InnoDB集群组成部分

MySQL Server 8.0.32 数据库存储
MySQL Shell 8.0.40 通过内置的管理API创建及管理Innodb集群。
MySQL Router 8.0.40 确保客户端请求是负载平衡的，并在任何数据库故障时路由到正确的服务器。

设置域名

在所有机器上设置域名

设置域名

cat << EOF >> /etc/hosts
172.16.183.15 Linux01 
172.16.183.16 Linux02 
172.16.183.17 Linux03 
EOF

确保3台服务器要互相访问正常，关闭防火墙

# 关闭       
systemctl stop firewalld     
# 开机禁用      
systemctl disable firewalld

设置SSH免登录

用ssh-keygen生成公钥，用ssh-copy-id将本地公钥复制到远程主机的authorized_keys文件。每台机器都要执行，注意执行时修改远程机器ip。

[root@Linux03 ~]# ssh-keygen -t rsa
#一路回车

#用ssh-copy-id将本地公钥复制到远程主机的authorized_keys文件
ssh-copy-id -i ~/.ssh/id_rsa.pub 172.16.183.16
ssh-copy-id -i ~/.ssh/id_rsa.pub 172.16.183.17
#按照提示输入目标主机的密码即可，最后验证码免密登录 ssh 182.16.183.16 即可

测试免登陆在Linux01上ssh Linux03

[root@Linux01 ~]# ssh mysql03

mysql server 8.0.32 安装

查看系统是否自带mariadb并卸载（防止mysql与mariadb的文件发生冲突）

rpm -qa | grep mariadb
#如果存在就删除即可

下载mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz

从mysql官网上下载mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz,进到cd /usr/local/soft目录使用wget组件下载

cd /usr/local/soft
tar -xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
解压后改名目录为
mysql-8.0.32
移到
mv mysql-8.0.32 /usr/local

创建mysql用户和组并修改权限

groupadd mysql
useradd -g mysql mysql
chown -R mysql.mysql /usr/local/mysql-8.0.32

进到 mysql-8.0.32 目录下，创建data，logs，tmp三个目录，并且给与权限 chmod -R 777 data,logs,tmp

cd /usr/local/mysql-8.0.32
mkdir data
chmod -R 777 data

mkdir logs
chmod -R 777 logs

mkdir tmp
chmod -R 777 tmp

在/etc目录下创建my.cnf文件并配置保存

先执行 vim /etc/my.cnf
[client]
	port = 3306
	
	#根据实际情况调整mysql.sock配置
	socket = /usr/local/mysql-8.0.32/tmp/mysql.sock
	
	[mysqld]
	#Mysql服务的唯一编号 每个mysql服务Id需唯一
	server-id = 1
	
	#服务端口号 默认3306
	port = 3306
	
	#mysql安装根目录
	basedir = /usr/local/mysql-8.0.32
	
	#mysql数据文件所在位置
	datadir = /usr/local/mysql-8.0.32/data
	
	#pid
	pid-file = /usr/local/mysql-8.0.32/tmp/mysql.pid
	
	#设置socke文件所在目录
	socket = /usr/local/mysql-8.0.32/tmp/mysql.sock
	
	#设置临时目录
	tmpdir = /usr/local/mysql-8.0.32/tmp
	
	# 用户
	user = mysql
	# 允许访问的IP网段
	bind-address = 0.0.0.0
	
	#错误日志
	log_error=/usr/local/mysql-8.0.32/logs/mysql-error.log
	#单位为秒
	long_query_time=3
	slow_query_log_file=/usr/local/mysql-8.0.32/logs/mysql_slow_query.log
	
	#问题原因
	#MySQL8.0新增data dictionary的概念，数据初始化的时候在Linux下默认使用lower-case-table-names=0参数，数据库启动的时候读取my.cnf文件中的值。若二者值不一致则在MySQL的错误日志
	#中记录报错信息。
	lower_case_table_names = 1
	
	#设置认证插件
	authentication_policy = mysql_native_password
	
	#设置sqlmode(根据需求自定义)
	sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
	
	# 跳过密码登录
	#skip-grant-tables
	
	#主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容
	skip-external-locking
	
	#只能用IP地址检查客户端的登录，不用主机名
	skip_name_resolve = 1
	
	#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
	character-set-server = utf8mb4
	
	#数据库字符集对应一些排序等规则，注意要和character-set-server对应
	collation-server = utf8mb4_general_ci
	
	#设置client连接mysql时的字符集,防止乱码
	init_connect='SET NAMES utf8mb4'
	
	#是否对sql语句大小写敏感，1表示不敏感
	lower_case_table_names = 1
	
	#最大连接数
	max_connections = 2000
	
	#最大错误连接数
	max_connect_errors = 5000
	
	#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
	explicit_defaults_for_timestamp = true
	
	#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
	max_allowed_packet = 128M
	
	#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
	#MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
	interactive_timeout = 1800
	wait_timeout = 1800
	
	#内部内存临时表的最大值 ，设置成128M。
	#比如大数据量的group by ,order by时可能用到临时表，
	#超过了这个值将写入磁盘，系统IO压力增大
	tmp_table_size = 134217728
	max_heap_table_size = 134217728
	
	#mysql binlog日志文件保存的过期时间，过期后自动删除
	binlog_expire_logs_seconds = 30

初始化

在mysql server 8.0.32/bin目录下
./mysqld --user=mysql --basedir=/usr/local/mysql-8.0.32 --datadir=/usr/local/mysql-8.0.32/data/ --initialize
去日志目录下查询日志，找到初始化的密码

配置开机启动

vim /usr/lib/systemd/system/mysql.service
[Unit]
	Description=MySQL Server 8.0
	Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
	After=network.target
	After=syslog.target
	 
	[Service]
	User=mysql
	Group=mysql
	LimitNOFILE=65536
	ExecStart=/usr/local/mysql-8.0.32/bin/mysqld --defaults-file=/etc/my.cnf
	 
	[Install]
	WantedBy=multi-user.target

	#设置开机自启动
	systemctl enable mysql.service
 	# 停止开机自启动
	systemctl disable mysql.service
	#停止服务
	systemctl stop mysql.service  
	#重新启动服务
	systemctl restart mysql.service
  #查看服务当前状态
	systemctl status mysql.service

配置mysql环境，设置软连接

vim /etc/profile
#mysql环境配置
export PATH=$PATH:/usr/local/mysql-8.0.32/bin

#使配置文件生效
source /etc/profile

执行 mysql -u root
-bash: mysql: command not found

原因:这是由于系统默认会查找/usr/bin下的命令，如果这个命令不在这个目录下，当然会找不到命令，我们需要做的就是映射一个链接到/usr/bin目录下，相当于建立一个链接文件。

首先得知道mysql命令或mysqladmin命令的完整路径，比如mysql的路径是：/usr/local/mysql-8.0.32/bin/mysql，我们则可以这样执行命令：

ln -s /usr/local/mysql-8.0.32/bin/mysql /usr/bin

连接mysql服务

mysql -uroot -h 127.0.0.1 -p

设置密码

#密码有'()'的使用无密码登录模式登录后执行
update user set authentication_string=PASSWORD("123456") where user='root';
#密码登录后执行 
alter user 'root'@'localhost' identified by '123456';
#刷新
flush privileges;

设置允许远程登录

update user set host='%' where user='root';
#刷新
flush privileges;
#查看用户信息是否被修改
select host, user, authentication_string from user;

到此mysql服务安装结束，每台服务器都安装此步骤执行。

mysql集群组复制配置

修改各个节点/etc/my.cnf 配置 MGR, 组复制信息，注意每台机器server_id，loose-group_replication_local_address需要不同

[mysqld]
#Mysql服务的唯一编号 每个mysql服务Id需唯一
server-id = 1
#组复制的基本配置
loose-group_replication_group_name="MGR"
loose-group_replication_local_address="172.16.183.15:3306"
loose-group_replication_group_seeds="172.16.183.15:3306,172.16.183.16:3306,172.16.183.17:3306"
loose-group_replication_auto_increment_offset=1
# 启用单主模式
loose-group_replication_single_primary_mode=TRUE

给MySQL root用户授权（才能配置InnoDB集群实例）
登录每台mysql执行

grant all privileges on *.* to `root`@`%` with grant option;
flush privileges;

安装MySQLShell

从官网下载MySQL Shell，将压缩包上传到每台机器/usr/local/soft下，解压、授权并配置环境变量

「链接」MySQL Shell官网

cd /usr/local/soft
#解压并配置
tar xvf mysql-shell-8.0.32-linux-glibc2.12-x86-64bit.tar.gz
#重命名
mv mysql-shell-8.0.32-linux-glibc2.12-x86-64bit mysql-shell-8.0.32
#移到/usr/local目录
mv mysql-shell-8.0.32 /usr/local
#授权给mysql用户 --必须
chown -R mysql.mysql /usr/local/mysql-shell-8.0.32

#设置环境变量
vim /etc/profile
#mysql Shell的配置
export PATH=$PATH:/usr/local/mysql-shell-8.0.40/bin

节点配置检查

进入MySQL shell

mysqlsh

在MySQL shell中执行，每台机器都要执行，注意修改主机名， Linux01 为当前节点

#检查当前节点是否配置正常
dba.configureInstance();
dba.checkInstanceConfiguration('root@Linux01:3306');

创建集群

在MySQL shell中

#执行下面的语句，此语句默认把当前节点添加到集群中
var cluster = dba.createCluster('mysqlCluster');

添加集群

cluster.addInstance('root@Linux02:3306');
cluster.addInstance('root@Linux03:3306');

已有集群添加机器

var cluster=dba.getCluster('mysqlCluster'); 
cluster.addInstance('root@Linux04:3306');

注意：在集群搭建之际，如果是对主服务的镜像予以克隆，致使所有节点的服务 UUID 皆保持一致。于此情境下，当在集群中增添节点时，便会出现 UUID 冲突的报错提示。

cluster.addInstance('root@Linux04:3306');
ERROR: RuntimeError: Cannot add an instance with the same server UUID (63e9282f-a9dd-11ed-9c7a-080027e7f5ef) of an active member of the cluster 'mysql01:3306'. Please change the server UUID of the instance to add, all members must have a unique server UUID.

解决方案：
1、利用uuid函数生成新的uuid

登录mysql服务执行以下sql

select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| ca25743b-bd29-11ef-9687-000c29b86bcb |
+--------------------------------------+

2、查看配置文件目录

show variables like 'datadir';

3、编辑配置文件目录

vim /usr/local/mysql-8.0.32/data/auto.cnf

4、uuid修改新生成的uuid

[auto]
server-uuid=ca25743b-bd29-11ef-9687-000c29b86bcb

重启服务即可

查看集群状态

#执行mysqlsh
mysqlsh

var cluster=dba.getCluster('mysqlCluster');
cluster.status();

{
    "clusterName": "mysqlCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "Linux01:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "Linux01:3306": {
                "address": "Linux01:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "Linux02:3306": {
                "address": "Linux02:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "Linux03:3306": {
                "address": "Linux03:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "Linux01:3306"
}

这段信息是关于一个名为 "mysqlCluster" 的 InnoDB 集群的状态报告，它使用 MySQL Shell 的 Cluster.status()方法获取。下面是对这段信息的详细解释：

集群名称 (clusterName):"mysqlCluster": 这是集群的名称。
默认复制集 (defaultReplicaSet):描述了集群中配置的默认复制集的信息。
复制集名称 (name):"default": 复制集的名称。
主节点 (primary):"Linux01:3306": 当前作为主节点（负责处理写操作）的 MySQL 实例的地址。
SSL 要求 (ssl):"REQUIRED": 集群中的通信要求使用 SSL 加密。
状态 (status 和 statusText):"OK": 集群的整体状态是良好的。"Cluster is ONLINE and can tolerate up to ONE failure.": 集群在线，并且能够容忍一个节点的故障。这意味着如果集群中的任何一个节点（除了主节点）发生故障，集群仍然能够继续提供服务（尽管性能可能会受到影响，并且故障的节点需要尽快恢复）。
拓扑结构 (topology):列出了集群中所有实例的详细信息。"Linux01:3306": 主节点的信息，包括地址、角色（PRIMARY）、模式（R/W，即读写）、读副本（没有配置）、复制延迟（正在应用队列中的事务）、角色（HA，即高可用）、状态（ONLINE）和版本（8.0.32）。"Linux02:3306" 和 "Linux03:3306": 两个副本节点的信息，它们都是只读的（R/O），没有配置读副本，复制延迟正在被应用，角色是高可用（HA），状态是在线（ONLINE），版本是 8.0.32。
拓扑模式 (topologyMode):"Single-Primary": 集群配置为单主模式，即只有一个实例（主节点）可以处理写操作，而其他实例（副本节点）只能处理读操作。
组信息源成员 (groupInformationSourceMember):"Linux01:3306": 这是当前作为组信息源（Group Information Source）的 MySQL 实例的地址。组信息源负责存储和分发集群的元数据，如成员列表、配置等。

#其他的api
cluster.dissolve({ force: true });

cluster.removeInstance('172.16.183.15:3306');

dba.rebootClusterFromCompleteOutage("mysqlCluster");

注意：在整个机器关机或者宕机后开启mysql服务，重启集群的时候会报错

集群中所有服务器重启后，执行命令var cluster = dba.getCluster("testCluster")时报：

Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) (MYSQLSH 51314)

此时可尝试重启集群：

dba.rebootClusterFromCompleteOutage("mysqlCluster");
若依然不能解决，则登录MySQL，然后启动该节点的group replication：
set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;

MySQLRouter路由

装1台MySQL Router

从官网下载MySQL Router，到/usr/local/soft

cd /usr/local/soft
---解压并配置
tar xvf mysql-router-8.0.40-linux-glibc2.12-x86_64.tar.xz
mv mysql-router-8.0.40-linux-glibc2.12-x86_64 mysql-router-8.0.40
#把目录移到/usr/local目录下
mv mysql-router-8.0.40 /usr/local
#授权 -- 必须
chown -R mysql.mysql /usr/local/mysql-router-8.0.40
---设置环境变量
vim /etc/profile
#mysql-router配置
export PATH=$PATH:/usr/local/mysql-router-8.0.40/bin
#:wq保存后使环境变量生效
source /etc/profile

路由初始化,MySQL Router节点执行配置

mysqlrouter --bootstrap root@localhost:3306 -d /usr/local/mysql-router-8.0.40/config --user=root --force

这里会生成/usr/local/mysql-router-8.0.40/config目录, 并在里面生成 mysqlrouter.conf配置文件，接着修改配置文件

vim /usr/local/mysql-router-8.0.40/config/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
user=root
logging_folder=/usr/local/mysql-router-8.0.40/config/log
runtime_folder=/usr/local/mysql-router-8.0.40/config/run
data_folder=/usr/local/mysql-router-8.0.40/config/data
keyring_path=/usr/local/mysql-router-8.0.40/config/data/keyring
master_key_path=/usr/local/mysql-router-8.0.40/config/mysqlrouter.key
connect_timeout=5
read_timeout=30
dynamic_state=/usr/local/mysql-router-8.0.40/config/data/state.json
client_ssl_cert=/usr/local/mysql-router-8.0.40/config/data/router-cert.pem
client_ssl_key=/usr/local/mysql-router-8.0.40/config/data/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED
unknown_config_option=error
#允许最大连接数
max_total_connections=65000

[logger]
level=INFO

[metadata_cache:bootstrap]
cluster_type=gr
router_id=4
user=mysql_router4_194fkc9
metadata_cluster=mysqlCluster
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://mysqlCluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://mysqlCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:bootstrap_x_rw]
bind_address=0.0.0.0
bind_port=6448
destinations=metadata-cache://mysqlCluster/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:bootstrap_x_ro]
bind_address=0.0.0.0
bind_port=6449
destinations=metadata-cache://mysqlCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

[http_server]
port=8443
ssl=1
ssl_cert=/usr/local/mysql-router-8.0.40/config/data/router-cert.pem
ssl_key=/usr/local/mysql-router-8.0.40/config/data/router-key.pem

[http_auth_realm:default_auth_realm]
backend=default_auth_backend
method=basic
name=default_realm

[rest_router]
require_realm=default_auth_realm

[rest_api]

[http_auth_backend:default_auth_backend]
backend=metadata_cache

[rest_routing]
require_realm=default_auth_realm

[rest_metadata_cache]
require_realm=default_auth_realm

[routing:read_writer]
#写节点地址；这表示MySQL Router的写节点绑定在所有可用的网络接口上，即它接受来自任何IP地址的连接。
bind_address=0.0.0.0
#写节点端口
bind_port=33061
#MySQL router提供两种mode：read-only和read-write，设置为read-write，常用于设置destinations为master时，实现master的高可用
#模式:读写模式
mode=read-write
#主节点地址：默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库
destinations=Linux01:3306,Linux02:3306,Linux03:3306
#客户端最大连接数
max_connections=50000

#读节点负载均衡配置
[routing:balancing]
#绑定的IP地址；这表示MySQL Router的读节点绑定在所有可用的网络接口上，即它接受来自任何IP地址的连接。
bind_address=0.0.0.0
#监听的端口
bind_port=33062
#模式:只读模式
mode=read-only
#主节点地址：默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库
destinations=Linux01:3306,Linux02:3306,Linux03:3306
#客户端最大连接数
max_connections=50000

启动mysql-router

启动成功日志

数据工具连接
host:172.16.183.15 端口：6446

读写节点：172.16.183.15 端口：33061
只读节点：172.16.183.15 端口：33062