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

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

名词解释
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 协议，保障数据一致性，组复制支持单主模式和多主模式。

mysql8高可用集群

设置域名

在所有机器上设置域名

设置域名

cat > /etc/hosts172.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.16ssh-copy-id -i ~/.ssh/id_rsa.pub 172.16.183.17#按照提示输入目标主机的密码即可，最后验证码免密登录 ssh 182.16.183.16 即可

测试免登陆在Linux01上ssh Linux03

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/softtar -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 mysqluseradd -g mysql mysqlchown -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.32mkdir datachmod -R 777 datamkdir logschmod -R 777 logsmkdir tmpchmod -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

原因:这是由于系统默认会查找/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服务安装结束，每台服务器都安装此步骤执行。

修改各个节点/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;

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

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 shellmysqlsh在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

重启服务即可

查看集群状态#执行mysqlshmysqlshvar 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 实例的地址。组信息源负责存储和分发集群的元数据，如成员列表、配置等。#其他的apicluster.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;

mysql-route功能

装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.xzmv 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=rootlogging_folder=/usr/local/mysql-router-8.0.40/config/logruntime_folder=/usr/local/mysql-router-8.0.40/config/rundata_folder=/usr/local/mysql-router-8.0.40/config/datakeyring_path=/usr/local/mysql-router-8.0.40/config/data/keyringmaster_key_path=/usr/local/mysql-router-8.0.40/config/mysqlrouter.keyconnect_timeout=5read_timeout=30dynamic_state=/usr/local/mysql-router-8.0.40/config/data/state.jsonclient_ssl_cert=/usr/local/mysql-router-8.0.40/config/data/router-cert.pemclient_ssl_key=/usr/local/mysql-router-8.0.40/config/data/router-key.pemclient_ssl_mode=PREFERREDserver_ssl_mode=AS_CLIENTserver_ssl_verify=DISABLEDunknown_config_option=error#允许最大连接数max_total_connections=65000[logger]level=INFO[metadata_cache:bootstrap]cluster_type=grrouter_id=4user=mysql_router4_194fkc9metadata_cluster=mysqlClusterttl=0.5auth_cache_ttl=-1auth_cache_refresh_interval=2use_gr_notifications=0[routing:bootstrap_rw]bind_address=0.0.0.0bind_port=6446destinations=metadata-cache://mysqlCluster/?role=PRIMARYrouting_strategy=first-availableprotocol=classic[routing:bootstrap_ro]bind_address=0.0.0.0bind_port=6447destinations=metadata-cache://mysqlCluster/?role=SECONDARYrouting_strategy=round-robin-with-fallbackprotocol=classic[routing:bootstrap_x_rw]bind_address=0.0.0.0bind_port=6448destinations=metadata-cache://mysqlCluster/?role=PRIMARYrouting_strategy=first-availableprotocol=x[routing:bootstrap_x_ro]bind_address=0.0.0.0bind_port=6449destinations=metadata-cache://mysqlCluster/?role=SECONDARYrouting_strategy=round-robin-with-fallbackprotocol=x[http_server]port=8443ssl=1ssl_cert=/usr/local/mysql-router-8.0.40/config/data/router-cert.pemssl_key=/usr/local/mysql-router-8.0.40/config/data/router-key.pem[http_auth_realm:default_auth_realm]backend=default_auth_backendmethod=basicname=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