MyCluster集群部署及测试

最新推荐文章于 2024-07-01 15:12:58 发布

lq2003

最新推荐文章于 2024-07-01 15:12:58 发布

阅读量940

点赞数 23

文章标签： database 系统架构 mysql

本文链接：https://blog.csdn.net/lq2003/article/details/135747112

版权

我们一直在等待它！它现在已经可用了！MySQL中的读/写分离！！

在大规模应用中，我们将读操作分发到副本之间，但这需要在应用程序中进行管理：将写操作指向某处，将读操作指向另外的地方。---MySQL router 8.2之前是通过不同端口来实现读写分离。

在MySQL 8.2中，MySQL路由器现在能够识别读操作和写操作，并将它们路由到InnoDB集群的主实例或将写操作路由到异步复制源，而将读操作路由到非主实例或副本。

原文网址：MySQL 8.2 – transparent read/write splitting – lefred blog: tribulations of a MySQL Evangelist

------本文基于此且结合沃音乐数据库业务现状完成架构更新----------------

MyCluster介绍

官方MySQL InnoDB Cluster 为 MySQL 提供了一个完整的高可用解决方案。它由三个组件组成：MySQL Shell、MySQL Router、MySQL Server（MGR）。

官方方案存在两个问题；

读写分离采用两个不同的端口来完成，这需要业务方配置时候需要区别对待；
MySQL router存在单点故障隐患。

本方案根据联通业务需求，套件由MySQL8.2系列组件组成，具备集群内故障自动转移、读写自动分离、相比官方方案无单点隐患，真正做到高可用、高性能、高扩展能力。

以下为本方案的架构图和部署、测试文档。

Mycluster 集群至少由三个 MySQL 服务器实例组成，并提供高可用性和可扩展性功能。将使用到以下 MySQL 技术：

MySQL Shell，它是MySQL官方提供的高级客户端和代码编辑器，提供了集群管理的能力。

MySQL Server 和 Group Replication(组复制)，它们配合工作可以使一组MySQL实例对外提供高可能性。InnoDB Cluster提供了另一种易于使用的编程方式来使用Group Replication(组复制)功能。

MySQL Router，一个能在应用程序和InnoDB集群之间提供透明路由的轻量级中间件，是官方提供的MySQL实例负载均衡器(不再需要借助类似HAProxy的第三方负载均衡器了)。

环境准备

IP地址	角色	操作系统
192.168.85.81	MySQL Router+MASTER	Centos7.7_x64
192.168.85.82	MySQL Router+BACKUP	Centos7.7_x64
192.168.85.80	VIP
192.168.85.83	master	Centos7.7_x64
192.168.85.84	slave01	Centos7.7_x64
192.168.85.85	slave02	Centos7.7_x64
192.168.232.5	slave03 ---异地机房容灾	Centos7.7_x64
172.16.23.12	Slave04 ---异地机房容灾	Centos7.7_x64

keepalived版本：2.0.8（1.3.5）

Mysql-shell:mysql-shell-8.2.1

mysql-community-server-8.2.0-1

mysql-router-community-8.2.0-1

MySQL（MGR）安装

3.1各节点主机环境初始化

#每个节点执行

cat >> /etc/hosts << EOF

192.168.85.81 router01

192.168.85.82 router02

192.168.85.83 Udb-nd03

192.168.85.84 Udb-nd04

192.168.85.85 Udb-nd05

192.168.232.5 Udb-nd06 ####白云联通机房

172.16.23.12 Udb-nd07 ####韶关华韶机房

EOF

#在node1执行

hostnamectl set-hostname router01;bash ###router01为主机名

#在每个节点执行 --关闭防火墙

systemctl disable firewalld

systemctl stop firewalld

#在每个节点执行 ---关闭selinux

sed -i 's/^ *SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

#reboot

3.2 Mysql 8.2版本软件安装（node01~07）

[root@udb-nd03 ~]# cat /etc/redhat-release

CentOS Linux release 7.7.1908 (Core)

Download it from the MySQL Yum Repository page (MySQL :: Download MySQL Yum Repository) in the MySQL Developer Zone.

[root@udb-nd03 ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm

--2023-12-15 09:27:11-- https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm

Connecting to 192.168.28.11:2811... connected.

Proxy request sent, awaiting response... 302 Moved Temporarily

Location: https://repo.mysql.com//mysql80-community-release-el7-11.noarch.rpm [following]

--2023-12-15 09:27:11-- https://repo.mysql.com//mysql80-community-release-el7-11.noarch.rpm

Connecting to 192.168.28.11:2811... connected.

Proxy request sent, awaiting response... 200 OK

Length: 14064 (14K) [application/x-redhat-package-manager]

Saving to: ‘mysql80-community-release-el7-11.noarch.rpm’

100%[===================================================>] 14,064 --.-K/s in 0s

2023-12-15 09:27:14 (271 MB/s) - ‘mysql80-community-release-el7-11.noarch.rpm’ saved [14064/14064]

[root@k8s-master2 ~]# rpm -ivh mysql80-community-release-el7-11.noarch.rpm

警告：mysql80-community-release-el7-11.noarch.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY

准备中... #################################### [100%]

正在升级/安装...

1:mysql80-community-release-el7-11 ################################# [100%]

[root@udb-nd03 yum.repos.d]# yum repolist enabled | grep mysql.*-community

mysql-connectors-community/x86_64 MySQL Connectors Community 234

mysql-tools-community/x86_64 MySQL Tools Community 102

mysql80-community/x86_64 MySQL 8.0 Community Server 446

yum -y install yum-utils ---解决 yum-config-manager缺失

yum-config-manager --disable mysql80-community

yum-config-manager --enable mysql-innovation-community

[root@udb-nd03 ~]# vim /etc/yum.repos.d/mysql-community.repo

---------激活innovation相关的源----------------

3.3安装MySQL-server8.2.0版本

[root@udb-nd03 ~]# yum install mysql-community-server

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: ftp.sjtu.edu.cn

* extras: ftp.sjtu.edu.cn

* updates: mirror.lzu.edu.cn

base | 3.6 kB 00:00:00

extras | 2.9 kB 00:00:00

mysql-connectors-community | 2.6 kB 00:00:00

mysql-innovation-community | 2.6 kB 00:00:00

mysql-tools-community | 2.6 kB 00:00:00

updates | 2.9 kB 00:00:00

Resolving Dependencies

--> Running transaction check

---> Package mysql-community-server.x86_64 0:8.2.0-1.el7 will be installed

--> Processing Dependency: mysql-community-common(x86-64) = 8.2.0-1.el7 for package: mysql-community-server-8.2.0-1.el7.x86_64

--> Processing Dependency: mysql-community-icu-data-files = 8.2.0-1.el7 for package: mysql-community-server-8.2.0-1.el7.x86_64

。。。。。。

--> Running transaction check

---> Package mysql-community-libs-compat.x86_64 0:8.2.0-1.el7 will be obsoleting

---> Package postfix.x86_64 2:2.10.1-7.el7 will be updated

---> Package postfix.x86_64 2:2.10.1-9.el7 will be an update

--> Finished Dependency Resolution

Dependencies Resolved

==================================================================

Package Arch Version Repository Size

====================================================================

Installing:

mysql-community-libs x86_64 8.2.0-1.el7 mysql-innovation-community 1.5 M

。。。。。。。。。。。。

5/10

Verifying : mysql-community-common-8.2.0-1.el7.x86_64 6/10

Verifying : mysql-community-client-plugins-8.2.0-1.el7.x86_64 7/10

Verifying : mysql-community-server-8.2.0-1.el7.x86_64 8/10

Verifying : 1:mariadb-libs-5.5.64-1.el7.x86_64 9/10

Verifying : 2:postfix-2.10.1-7.el7.x86_64 10/10

Installed:

mysql-community-libs.x86_64 0:8.2.0-1.el7 mysql-community-libs-compat.x86_64 0:8.2.0-1.el7 mysql-community-server.x86_64 0:8.2.0-1.el7

Dependency Installed:

mysql-community-client.x86_64 0:8.2.0-1.el7 mysql-community-client-plugins.x86_64 0:8.2.0-1.el7 mysql-community-common.x86_64 0:8.2.0-1.el7 mysql-community-icu-data-files.x86_64 0:8.2.0-1.el7

Dependency Updated:

postfix.x86_64 2:2.10.1-9.el7

Replaced:

mariadb-libs.x86_64 1:5.5.64-1.el7

Complete!

[root@udb-nd03 ~]# systemctl start mysqld

[root@udb-nd03 ~]# systemctl status mysqld

● mysqld.service - MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: active (running) since Fri 2023-12-15 10:22:52 CST; 12s ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process: 18540 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Main PID: 18611 (mysqld)

Status: "Server is operational"

CGroup: /system.slice/mysqld.service

└─18611 /usr/sbin/mysqld

Dec 15 10:22:40 udb-nd03 systemd[1]: Starting MySQL Server...

Dec 15 10:22:52 udb-nd03 systemd[1]: Started MySQL Server.

[root@udb-nd03 ~]# grep 'temporary password' /var/log/mysqld.log

2023-12-15T02:22:45.543263Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !tl9&cTdIwg5

#登录并修改root用户初始密码

[root@node1 ~]# mysql -u root -p

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'My+2P)ass4!'; ------mysql_native_password 确保能被老旧客户端连接

#修改root访问权限

mysql> use mysql;

mysql> update user set host='%' where user='root';

mysql> grant all on *.* to 'root'@'%' ;

Query OK, 0 rows affected (0.02 sec)

3.4安装MySQL-shell 8.2.0版本

[root@udb-nd05 ~]# yum install mysql-shell -y

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: ftp.sjtu.edu.cn

* extras: ftp.sjtu.edu.cn

* updates: mirror.lzu.edu.cn

base | 3.6 kB 00:00:00

extras | 2.9 kB 00:00:00

mysql-connectors-community | 2.6 kB 00:00:00

mysql-innovation-community | 2.6 kB 00:00:00

mysql-tools-community | 2.6 kB 00:00:00

mysql-tools-innovation-community | 2.6 kB 00:00:00

updates | 2.9 kB 00:00:00

mysql-tools-innovation-community/x86_64/primary_db | 10 kB 00:00:00

Resolving Dependencies

--> Running transaction check

---> Package mysql-shell.x86_64 0:8.2.1-1.el7 will be installed

--> Processing Dependency: libyaml-0.so.2()(64bit) for package: mysql-shell-8.2.1-1.el7.x86_64

--> Running transaction check

---> Package libyaml.x86_64 0:0.1.4-11.el7_0 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================

Package Arch Version Repository Size

======================================================================

Installing:

mysql-shell x86_64 8.2.1-1.el7 mysql-tools-innovation-community 39 M

Installing for dependencies:

libyaml x86_64 0.1.4-11.el7_0 base 55 k

Transaction Summary

=====================================================================

Install 1 Package (+1 Dependent package)

Total download size: 39 M

Installed size: 242 M

Downloading packages:

(1/2): libyaml-0.1.4-11.el7_0.x86_64.rpm | 55 kB 00:00:00

(2/2): mysql-shell-8.2.1-1.el7.x86_64.rpm | 39 MB 00:00:06

--------------------------------------------------------

Total 6.4 MB/s | 39 MB 00:00:06

。。。。。。

Installing : mysql-shell-8.2.1-1.el7.x86_64 2/2

Verifying : libyaml-0.1.4-11.el7_0.x86_64 1/2

Verifying : mysql-shell-8.2.1-1.el7.x86_64 2/2

Installed:

mysql-shell.x86_64 0:8.2.1-1.el7

Dependency Installed:

libyaml.x86_64 0:0.1.4-11.el7_0

Complete!

3.5 验证MySQL-shell

[root@udb-nd07 ~]# mysqlsh --uri root@localhost:3306

Please provide the password for 'root@localhost:3306': ***********

Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): y

MySQL Shell 8.2.1

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.

Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.

Creating a session to 'root@localhost:3306'

Fetching schema names for auto-completion... Press ^C to stop.

Your MySQL connection id is 10

Server version: 8.2.0 MySQL Community Server - GPL

No default schema selected; type \use <schema> to set one.

MySQL localhost:3306 ssl JS > var cluster=dba.getCluster()

Dba.getCluster: This function is not available through a session to a standalone instance (MYSQLSH 51300)

MySQL localhost:3306 ssl JS > \quit

Bye!

--至此，MySQL-shell安装完成！------------------

3.6 Innodb cluster MGR配置

3.6.1各节点配置

[root@udb-nd03 ~]# mysqlsh -uri root@localhost:3306 ----主库登陆

MySQL Shell 8.2.1

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.

Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.

Creating a session to 'root@localhost:3306'

Fetching schema names for auto-completion... Press ^C to stop.

Your MySQL connection id is 23

Server version: 8.2.0 MySQL Community Server - GPL

No default schema selected; type \use <schema> to set one.

MySQL localhost:3306 ssl JS > dba.checkInstanceConfiguration('udb-nd04:3306')

Please provide the password for 'root@udb-nd04:3306': ***********

Save password for 'root@udb-nd04:3306'? [Y]es/[N]o/Ne[v]er (default No): y

Validating MySQL instance at udb-nd04:3306 for use in an InnoDB cluster...

ERROR: The account 'root'@'%' is missing privileges required to manage an InnoDB cluster:

GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'root'@'%' WITH GRANT OPTION;

--需要udb-nd04节点授权

For more information, see the online documentation.

Dba.checkInstanceConfiguration: The account 'root'@'%' is missing privileges required to manage an InnoDB cluster. (RuntimeError)

MySQL localhost:3306 ssl JS > dba.checkInstanceConfiguration('udb-nd04:3306')

Validating MySQL instance at udb-nd04:3306 for use in an InnoDB cluster...

This instance reports its own address as udb-nd04:3306

Clients and other cluster members will communicate with it through this address by default. If this is notcorrect, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...

No incompatible tables detected

Checking instance configuration...

NOTE: Some configuration options need to be fixed:

+----------------------------------------+---------------+----------------+----

+----------------------------------------+---------------+----------------+----

| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |

| gtid_mode | OFF | ON | Update read-only variable and restart the server |

+----------------------------------------+---------------+----------------+----

Some variables need to be changed, but cannot be done dynamically on the server.

NOTE: Please use the dba.configureInstance() command to repair these issues.

{

"config_errors": [

{

"action": "server_update",

"current": "COMMIT_ORDER",

"option": "binlog_transaction_dependency_tracking",

"required": "WRITESET"

{

"action": "server_update+restart",

"current": "OFF",

"option": "enforce_gtid_consistency",

"required": "ON"

{

"action": "server_update+restart",

"current": "OFF",

"option": "gtid_mode",

"required": "ON"

{

"action": "server_update+restart",

"current": "1",

"option": "server_id",

"required": "<unique ID>"

}

"status": "error"

}

--------列举了存在不适配的参数明细-----------

MySQL localhost:3306 ssl JS > dba.configureInstance('udb-nd04:3306');

Configuring MySQL instance at udb-nd04:3306 for use in an InnoDB cluster...

This instance reports its own address as udb-nd04:3306

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:

+----------------------------------------+---------------+----------------+----

+----------------------------------------+---------------+----------------+----

| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |

| gtid_mode | OFF | ON | Update read-only variable and restart the server |

+----------------------------------------+---------------+----------------+----

Some variables need to be changed, but cannot be done dynamically on the server.

Do you want to perform the required configuration changes? [y/n]: y

Do you want to restart the instance after configuring it? [y/n]: y

Configuring instance...

WARNING: '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. (Code 1287).

The instance 'udb-nd04:3306' was configured to be used in an InnoDB cluster.

Restarting MySQL...

NOTE: MySQL server at udb-nd04:3306 was restarted.

MySQL localhost:3306 ssl JS >

--------修正存在不适配的参数并重启MySQL-----------

MySQL localhost:3306 ssl JS > dba.configureInstance('udb-nd04:3306');

Configuring MySQL instance at udb-nd04:3306 for use in an InnoDB cluster...

This instance reports its own address as udb-nd04:3306

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

applierWorkerThreads will be set to the default value of 4.

The instance 'udb-nd04:3306' is valid to be used in an InnoDB cluster.

The instance 'udb-nd04:3306' is already ready to be used in an InnoDB cluster.

Successfully enabled parallel appliers.

MySQL localhost:3306 ssl JS >

--------按上述步骤逐次完成剩下节点（udb-nd03、udb-nd05）的配置---------------------

3.6.2创建集群mycluster

MySQL localhost:3306 ssl JS > var cluster = dba.createCluster('mycluster');

--以本机为主，创建集群----

A new InnoDB Cluster will be created on instance 'udb-nd03:3306'.

Validating instance configuration at localhost:3306...

This instance reports its own address as udb-nd03:3306

Instance configuration is suitable.

NOTE: Group Replication will communicate with other members using 'udb-nd03:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

Creating InnoDB Cluster 'mycluster' on 'udb-nd03:3306'...

Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.

At least 3 instances are needed for the cluster to be able to withstand up to

one server failure.

MySQL localhost:3306 ssl JS > cluster.addInstance('udb-nd04'); ---添加节点nd04

NOTE: The target instance 'udb-nd04:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.

The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'udb-nd04:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains thesame GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): ----默认回车克隆

Validating instance configuration at udb-nd04:3306...

This instance reports its own address as udb-nd04:3306

Instance configuration is suitable.

NOTE: Group Replication will communicate with other members using 'udb-nd04:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

A new instance will be added to the InnoDB Cluster. Depending on the amount of

data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.

Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the

server does not support the RESTART command or does not come back after a

while, you may need to manually start it back.

* Waiting for clone to finish...

NOTE: udb-nd04:3306 is being cloned from udb-nd03:3306

** Stage DROP DATA: Completed

** Clone Transfer

FILE COPY #############################=============================== 48% In Progress

PAGE COPY ============================================================ 0% Not Started

REDO COPY ============================================================ 0% Not Started

。。。。

FILE COPY ############################################################ 100% Completed

PAGE COPY ############################################################ 100% Completed

REDO COPY ############################################################ 100% Completed

NOTE: udb-nd04:3306 is shutting down...

* Waiting for server restart... ready

* udb-nd04:3306 has restarted, waiting for clone to finish...

** Stage RESTART: Completed

* Clone process has finished: 1.51 GB transferred in 14 sec (108.11 MB/s)

State recovery already finished for 'udb-nd04:3306'

The instance 'udb-nd04:3306' was successfully added to the cluster.

MySQL localhost:3306 ssl JS >cluster.addInstance('udb-nd05'); ---添加节点nd05

。。。。。

NOTE: udb-nd05:3306 is shutting down...

* Waiting for server restart... ready

* udb-nd05:3306 has restarted, waiting for clone to finish...

** Stage RESTART: Completed

* Clone process has finished: 1.51 GB transferred in 15 sec (100.90 MB/s)

State recovery already finished for 'udb-nd05:3306'

The instance 'udb-nd05:3306' was successfully added to the cluster.

3.6.3查看cluster集群成员

MySQL localhost:3306 ssl JS >var cluster =dba.getCluster();

MySQL localhost:3306 ssl JS > cluster.status();

{

"clusterName": "mycluster",

"defaultReplicaSet": {

"name": "default",

"primary": "udb-nd03:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"udb-nd03:3306": {

"address": "udb-nd03:3306",

"memberRole": "PRIMARY",

"mode": "R/W",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.2.0"

"udb-nd04:3306": {

"address": "udb-nd04:3306",

"memberRole": "SECONDARY",

"mode": "R/O",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.2.0"

"udb-nd05:3306": {

"address": "udb-nd05:3306",

"memberRole": "SECONDARY",

"mode": "R/O",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.2.0"

}

"topologyMode": "Single-Primary"

"groupInformationSourceMember": "udb-nd03:3306"

}

MySQL localhost:3306 ssl JS >

====================================================================

3.6.4验证同步能力

22222222222222主库灌入300万行数据 22222222222222222

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| club03-account |

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

mysql> use club03-account;

Database changed

mysql> select count(*) from bill;

+----------+

| count(*) |

+----------+

| 3691508 |

+----------+

1 row in set (0.59 sec)

222222222222主库灌入300万行数据 222222222222222222222222222222222222222222222

验证各节点数据同步情况

[root@udb-nd04 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 8.2.0 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

ERROR 2013 (HY000): Lost connection to MySQL server during query

No connection. Trying to reconnect...

Connection id: 61

Current database: mysql

+-------------------------------+

| Database |

+-------------------------------+

| club03-account |

| information_schema |

| mysql |

| mysql_innodb_cluster_metadata |

| performance_schema |

| sys |

+-------------------------------+

6 rows in set (0.03 sec)

mysql> use club03-account;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+--------------------------+

| Tables_in_club03-account |

+--------------------------+

| bill |

+--------------------------+

1 row in set (0.00 sec)

mysql> select count(*) from bill;

+----------+

| count(*) |

+----------+

| 3691508 |

+----------+

1 row in set (0.77 sec)

mysql>

四、MySQL Router安装（router01、router02）

4.1 mysql-router安装

[root@router01 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-community-8.2.0-1.el7.x86_64.rpm

--2023-12-15 12:43:50-- https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-community-8.2.0-1.el7.x86_64.rpm

Connecting to 192.168.28.11:2811... connected.

Proxy request sent, awaiting response... 302 Moved Temporarily

Location: https://cdn.mysql.com//Downloads/MySQL-Router/mysql-router-community-8.2.0-1.el7.x86_64.rpm [following]

--2023-12-15 12:43:50-- https://cdn.mysql.com//Downloads/MySQL-Router/mysql-router-community-8.2.0-1.el7.x86_64.rpm

Connecting to 192.168.28.11:2811... connected.

Proxy request sent, awaiting response... 200 OK

Length: 5445060 (5.2M) [application/x-redhat-package-manager]

Saving to: ‘mysql-router-community-8.2.0-1.el7.x86_64.rpm’

100%[======================================================>] 5,445,060 --.-K/s in 0.1s

2023-12-15 12:43:50 (36.7 MB/s) - ‘mysql-router-community-8.2.0-1.el7.x86_64.rpm’ saved [5445060/5445060]

[root@router01 ~]# ls

anaconda-ks.cfg mysql80-community-release-el7-11.noarch.rpm mysql-router-community-8.2.0-1.el7.x86_64.rpm openssh-9.4p1.tar.gz openssl

[root@router01 ~]# rpm -ivh mysql-router-community-8.2.0-1.el7.x86_64.rpm

warning: mysql-router-community-8.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY

Preparing... ################################# [100%]

Updating / installing...

1:mysql-router-community-8.2.0-1.el################################# [100%]

4.2 mysql-client安装

[root@router01 yum.repos.d]# yum install mysql-community-client -y

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: mirror.lzu.edu.cn

* extras: ftp.sjtu.edu.cn

* updates: mirror.lzu.edu.cn

Resolving Dependencies

--> Running transaction check

---> Package mysql-community-client.x86_64 0:8.2.0-1.el7 will be installed

--> Processing Dependency: mysql-community-client-plugins = 8.2.0-1.el7 for package: mysql-community-client-8.2.0-1.el7.x86_64

--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.2.0-1.el7.x86_64

。。。。。。

Dependency Installed:

mysql-community-client-plugins.x86_64 0:8.2.0-1.el7 mysql-community-common.x86_64 0:8.2.0-1.el7

Dependency Updated:

postfix.x86_64 2:2.10.1-9.el7

Replaced:

mariadb-libs.x86_64 1:5.5.64-1.el7

Complete!

4.3 mysql-shell安装

[root@router02 yum.repos.d]# yum install mysql-shell -y

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: ftp.sjtu.edu.cn

* extras: mirror.lzu.edu.cn

* updates: mirror.lzu.edu.cn

Resolving Dependencies

--> Running transaction check

---> Package mysql-shell.x86_64 0:8.2.1-1.el7 will be installed

--> Processing Dependency: libyaml-0.so.2()(64bit) for package: mysql-shell-8.2.1-1.el7.x86_64

--> Running transaction check

---> Package libyaml.x86_64 0:0.1.4-11.el7_0 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

============================================================================================

Package Arch Version Repository Size

============================================================================================

Installing:

mysql-shell x86_64 8.2.1-1.el7 mysql-tools-innovation-community 39 M

Installing for dependencies:

libyaml x86_64 0.1.4-11.el7_0 base 55 k

Transaction Summary

============================================================================================

Install 1 Package (+1 Dependent package)

....

Verifying : mysql-shell-8.2.1-1.el7.x86_64 2/2

Installed:

mysql-shell.x86_64 0:8.2.1-1.el7

Dependency Installed:

libyaml.x86_64 0:0.1.4-11.el7_0

Complete!

[root@router02 yum.repos.d]# mysql

mysql mysqlcheck mysqlimport mysqlrouter mysqlrouter_plugin_info mysqlshow

mysqladmin mysql_config_editor mysql_migrate_keyring mysqlrouter_keyring mysql-secret-store-login-path mysqlslap

mysqlbinlog mysqldump mysqlpump mysqlrouter_passwd mysqlsh

4.4获取cluster集群信息（router01-02）

[root@router01 yum.repos.d]# mysqlrouter --bootstrap root@udb-nd03:3306 --user root

==============连接cluster主库udb-nd03以获取集群信息======================

Please enter MySQL password for root:

# Bootstrapping system MySQL Router 8.2.0 (MySQL Community - GPL) instance...

- Creating account(s) (only those that are needed, if any)

- Verifying account (using it to run SQL queries that would be run by Router)

- Storing account in keyring

- Adjusting permissions of generated files

- Creating configuration /etc/mysqlrouter/mysqlrouter.conf

Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'

# MySQL Router configured for the InnoDB Cluster 'mycluster'

After this MySQL Router has been started with the generated configuration

$ /etc/init.d/mysqlrouter restart

$ systemctl start mysqlrouter

$ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

InnoDB Cluster 'mycluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446

- Read/Only Connections: localhost:6447

- Read/Write Split Connections: localhost:6450 ---可读写端口

## MySQL X protocol

- Read/Write Connections: localhost:6448

- Read/Only Connections: localhost:6449

[root@router01 yum.repos.d]#

4.5 Keepalived部署（router01-02）

4.5.1 yum安装

yum install keepalived -y --也可以(默认是1.3.5版本)

4.5.2 源码安装

yum -y install openssl* libnl-dev* gcc-c++ libnl*

wget https://www.keepalived.org/software/keepalived-2.0.18.tar.gz

tar -zxvf /software/keepalived-2.0.18.tar.gz

cd keepalived

./configure --prefix=/usr/local/keepalived

make

make install

4.5.3 配置keepalived（yum安装此节无视）

mkdir /etc/keepalived

cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

cp /keepalived-2.0.18/keepalived/etc/init.d/keepalived /etc/init.d/

cp /keepalived-2.0.18/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

4.5.4修改keepalived配置文件

4.5.4.1 Keepalived MASTER配置

[root@router01 ~]# cat /etc/keepalived/keepalived.conf

#!/bin/bash

global_defs {

router_id router01 ## 标识本节点的字条串，通常为hostname

script_user root

}

vrrp_script chk_mysql {

script "/etc/keepalived/mysql_check.sh"

interval 3

}

vrrp_instance VI_1 {

state BACKUP #定义为主服务器

interface eth0 #承载漂移ip的网卡 7的系统 ens开头

virtual_router_id 215 #定义一个热备组，可以认为这是51号热备组

priority 220 #主服务器优先级要比备服务器高

#nopreempt

advert_int 2 #1秒互相通告一次，检查对方死了没。

authentication {

auth_type PASS #认证类型

auth_pass 1111 #认证密码这些相当于暗号

}

virtual_ipaddress {

192.168.85.80 #漂移ip

}

track_script {

chk_mysql

}

[root@router01 ~]#

4.5.4.2 Keepalived BACKUP配置

[root@router02 ~]# cat /etc/keepalived/keepalived.conf

#!/bin/bash

global_defs {

router_id router02 ## 标识本节点的字条串，通常为hostname

script_user root

}

vrrp_script chk_mysql {

script "/etc/keepalived/mysql_check.sh"

interval 3

}

vrrp_instance VI_1 {

state BACKUP #定义为主服务器

interface eth0 #承载漂移ip的网卡 7的系统 ens开头

virtual_router_id 215 #定义一个热备组，可以认为这是51号热备组

priority 120 #主服务器优先级要比备服务器高

nopreempt

advert_int 2 #1秒互相通告一次，检查对方死了没。

authentication {

auth_type PASS #认证类型

auth_pass 1111 #认证密码这些相当于暗号

}

virtual_ipaddress {

192.168.85.80 #漂移ip

}

track_script {

chk_mysql

}

4.5.4.3 Keepalived 执行脚本

[root@router02 ~]# cat /etc/keepalived/mysql_check.sh

#!/bin/bash

counter=$(netstat -tnlp|grep "6450"|wc -l)

if [ "${counter}" -eq 0 ]; then

nohup mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &

systemctl stop keepalived

sleep 1

systemctl start keepalived

4.5.5启动keepalived

systemctl start keepalived

systemctl enable keepalived

启动参数说明：
使用systemctl start keepalived命令启动服务时，默认会将/etc/sysconfig/keepalived文件中KEEPALIVED_OPTIONS参数作为keepalived服务启动时的参数，并从/etc/keepalived/目录下加载keepalived.conf配置文件，或用-f参数指定配置文件的位置。

4.5.6查看VIP绑定情况

MASTER：

BACKUP：

BACKUP是没有绑定VIP的，只有当MASTER发生故障，触发了VIP切换，BACKUP才会绑定VIP。

五、读写测试

5.1 启动MySQL-router

[root@router01 ~]# nohup mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &

[1] 4203

[root@router01 ~]# nohup: ignoring input and appending output to ‘nohup.out’

[root@router01 ~]# ps -ef|grep mysqlrouter

root 4203 2999 2 13:04 pts/0 00:00:00 mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

root 4236 2999 0 13:04 pts/0 00:00:00 grep --color=auto mysqlrouter

5.2 查看集群情况

MySQL router01:6450 ssl JS > var cluster =dba.getCluster();

MySQL router01:6450 ssl JS > cluster.status();

{

"clusterName": "mycluster",

"defaultReplicaSet": {

"name": "default",

"primary": "udb-nd03:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"udb-nd03:3306": {

"address": "udb-nd03:3306",

"memberRole": "PRIMARY",

"mode": "R/W",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.2.0"

"udb-nd04:3306": {

"address": "udb-nd04:3306",

"memberRole": "SECONDARY",

"mode": "R/O",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.2.0"

"udb-nd05:3306": {

"address": "udb-nd05:3306",

"memberRole": "SECONDARY",

"mode": "R/O",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.2.0"

}

"topologyMode": "Single-Primary"

"groupInformationSourceMember": "udb-nd03:3306"

}

MySQL router01:6450 ssl JS >

sql;select * from performance_schema.replication_group_members;

5.3 读写测试

[root@router01 ~]# for ((i=0;i<=5;i++)); do mysql -h192.168.85.80 -uroot -p'My+2P)ass4!' -P6450 -e"use club03-account;select count(*),@@hostname from bill;"; done;

mysql: [Warning] Using a password on the command line interface can be insecure.

+----------+------------+

| count(*) | @@hostname |

+----------+------------+

| 3691508 | udb-nd05 |

+----------+------------+

mysql: [Warning] Using a password on the command line interface can be insecure.

+----------+------------+

| count(*) | @@hostname |

+----------+------------+

| 3691508 | udb-nd04 |

+----------+------------+

mysql: [Warning] Using a password on the command line interface can be insecure.

+----------+------------+

| count(*) | @@hostname |

+----------+------------+

| 3691508 | udb-nd05 |

+----------+------------+

mysql: [Warning] Using a password on the command line interface can be insecure.

+----------+------------+

| count(*) | @@hostname |

+----------+------------+

| 3691508 | udb-nd04 |

+----------+------------+

mysql: [Warning] Using a password on the command line interface can be insecure.

+----------+------------+

| count(*) | @@hostname |

+----------+------------+

| 3691508 | udb-nd05 |

+----------+------------+

mysql: [Warning] Using a password on the command line interface can be insecure.

+----------+------------+

| count(*) | @@hostname |

----------+------------+

| 3691508 | udb-nd04 |

+----------+------------+

------查询测试显示相关的查询调度到只读节点udb-nd04、udb-nd05，成功！-------

[root@udb-nd04 ~]# mysqlsh -uri root@192.168.85.80:6450

Please provide the password for 'root@192.168.85.80:6450': ***********

Save password for 'root@192.168.85.80:6450'? [Y]es/[N]o/Ne[v]er (default No): y

MySQL Shell 8.2.1

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.

Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.

Creating a session to 'root@192.168.85.80:6450'

Fetching schema names for auto-completion... Press ^C to stop.

Your MySQL connection id is 0

Server version: 8.2.0 MySQL Community Server - GPL

No default schema selected; type \use <schema> to set one.

MySQL 192.168.85.80:6450 ssl JS >

MySQL 192.168.85.80:6450 ssl SQL > select * from performance_schema.replication_group_members;

MySQL 192.168.85.80:6450 ssl SQL > router set access_mode='read_write';

Query OK, 0 rows affected (0.0008 sec)

MySQL 192.168.85.80:6450 ssl - SQL > select @@hostname;

+------------+

| @@hostname |

+------------+

| udb-nd03 |

+------------+

1 row in set (0.0045 sec)

Statement ID: 4940308

MySQL 192.168.85.80:6450 ssl SQL > select @@hostname;

+------------+

| @@hostname |

+------------+

| udb-nd03 |

+------------+

1 row in set (0.0022 sec)

Statement ID: 4940557

MySQL 192.168.85.80:6450 ssl SQL > router set access_mode='read_only';

Query OK, 0 rows affected (0.0005 sec)

MySQL 192.168.85.80:6450 ssl - SQL > select @@hostname;

+------------+

| @@hostname |

+------------+

| udb-nd04 |

+------------+

1 row in set (0.0061 sec)

Statement ID: 2523424

MySQL 192.168.85.80:6450 ssl SQL > select @@hostname;

+------------+

| @@hostname |

+------------+

| udb-nd04 |

+------------+

1 row in set (0.0021 sec)

Statement ID: 2523430

----读写测试显示，调度到读节点udb-nd04、udb-nd05；读写节点udb-nd03，成功！-------

六验证高可用

6.1节点切换

主节点切换为udb-nd05后，验证MySQL Router是否感知到主节点切换

JS > var cluster=dba.getCluster()

JS > cluster.setPrimaryInstance('udb-nd05:3306');

[root@router02 ~]# mysqlsh -uri root@192.168.85.80:6450

MySQL Shell 8.2.1

。。。。。。

MySQL 192.168.85.80:6450 ssl - SQL > \js

Switching to JavaScript mode...

MySQL 192.168.85.80:6450 ssl ★ JS > var cluster=dba.getCluster();

MySQL 192.168.85.80:6450 ssl ★ JS > cluster.status();

{

"clusterName": "mycluster",

"defaultReplicaSet": {

"name": "default",

"primary": "udb-nd03:3306",

"ssl": "REQUIRED",

"status": "OK",

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

"topology": {

"udb-nd03:3306": {

"address": "udb-nd03:3306",

"memberRole": "PRIMARY",

"mode": "R/W",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.2.0"

"udb-nd04:3306": {

"address": "udb-nd04:3306",

"memberRole": "SECONDARY",

"mode": "R/O",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.2.0"

"udb-nd05:3306": {

"address": "udb-nd05:3306",

"memberRole": "SECONDARY",

"mode": "R/O",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.2.0"

"udb-nd07:3306": {

"address": "udb-nd07:3306",

"memberRole": "SECONDARY",

"mode": "R/O",

"readReplicas": {},

"replicationLag": "applier_queue_applied",

"role": "HA",

"status": "ONLINE",

"version": "8.2.0"

}

"topologyMode": "Single-Primary"

"groupInformationSourceMember": "udb-nd03:3306"

}

MySQL 192.168.85.80:6450 ssl ★JS > cluster.setPrimaryInstance('udb-nd05:3306');

Setting instance 'udb-nd05:3306' as the primary instance of cluster 'mycluster'...

Instance 'udb-nd04:3306' remains SECONDARY.

Instance 'udb-nd07:3306' remains SECONDARY.

Instance 'udb-nd05:3306' was switched from SECONDARY to PRIMARY.

Instance 'udb-nd03:3306' was switched from PRIMARY to SECONDARY.

The instance 'udb-nd05:3306' was successfully elected as primary.

MySQL 192.168.85.80:6450 ssl ★ JS > \sql

Switching to SQL mode... Commands end with ;

MySQL 192.168.85.80:6450 ssl SQL > router set access_mode='read_write';

Query OK, 0 rows affected (0.0014 sec)

MySQL 192.168.85.80:6450 ssl - SQL > select @@hostname;

+------------+

| @@hostname |

+------------+

| udb-nd03 | -----测试显示在同一个链接中router是没有感知的！

+------------+ ------但是在关闭这个链接重新链接就可以了!

1 row in set (0.0044 sec)

Statement ID: 4974930

-------------------重新链接后router恢复正常判断-----------

[root@router02 ~]# mysqlsh -uri root@192.168.85.80:6450

MySQL Shell 8.2.1

。。。。。

Creating a session to 'root@192.168.85.80:6450'

Fetching schema names for auto-completion... Press ^C to stop.

Your MySQL connection id is 0

Server version: 8.2.0 MySQL Community Server - GPL

No default schema selected; type \use <schema> to set one.

MySQL 192.168.85.80:6450 ssl JS > \sql

Switching to SQL mode... Commands end with ;

Fetching global names for auto-completion... Press ^C to stop.

MySQL 192.168.85.80:6450 ssl SQL > select @@hostname;

+------------+

| @@hostname |

+------------+

| udb-nd04 |

+------------+

1 row in set (0.0026 sec)

Statement ID: 2524259

MySQL 192.168.85.80:6450 ssl SQL > router set access_mode='read_write';

Query OK, 0 rows affected (0.0008 sec)

MySQL 192.168.85.80:6450 ssl - SQL > select @@hostname;

+------------+

| @@hostname |

+------------+

| udb-nd05 |

+------------+

1 row in set (0.0045 sec)

Statement ID: 3891395

MySQL 192.168.85.80:6450 ssl SQL > router set access_mode='read_only';

Query OK, 0 rows affected (0.0011 sec)

MySQL 192.168.85.80:6450 ssl - SQL > select @@hostname;

+------------+

| @@hostname |

+------------+

| udb-nd04 |

+------------+

1 row in set (0.0047 sec)

Statement ID: 2524289

6.2节点破坏

6.2.1 Router节点破坏（VIP漂移）

Router01：

Router02：

[root@router01 ~]# reboot ---关闭router01后，VIP 192.168.85.50 漂移到router02

在router起来之后，VIP切换回来，日志过程如下：

最终状态为：

Router01：VIP成功切换回来，router进程运行中..........

Router02：VIP成功切换走了，router进程运行中..........

----keepalive只负责MySQL router VIP的切换，双机保持MySQL router进程存活！------

6.2.2 MGR节点破坏

当前master在udb-nd05上：

select * from performance_schema.replication_group_members;

在udb-nd05执行关闭MySQL服务，看看效果：

[root@udb-nd07 ~]# tailf /var/log/mysqld.log ---在udb-nd07上查看日志变化

..........

2023-12-21T06:57:11.539644Z 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: udb-nd05:3306'

2023-12-21T06:57:11.539695Z 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address udb-nd05:3306 left the group. Electing new Primary.'

2023-12-21T06:57:11.539763Z 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address udb-nd04:3306 was elected. The new primary will execute all previous group transactions before allowing writes.'

2023-12-21T06:57:11.540010Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to udb-nd04:3306, udb-nd03:3306, udb-nd07:3306 on view 17026133425141525:13.'

2023-12-21T06:57:11.547447Z 64 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'

2023-12-21T06:57:11.547512Z 64 [System] [MY-011511] [Repl] Plugin group_replication reported: 'This server is working as secondary member with primary member address udb-nd04:3306.'

udb-nd05关闭MySQL服务后，primary被迅速转移到了udb-nd04。

当重启udb-nd05的mysql服务后，集群纳入了udb-nd05作为只读成员。

2023-12-21T06:57:11.539644Z 0 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: udb-nd05:3306'

2023-12-21T06:57:11.539695Z 0 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address udb-nd05:3306 left the group. Electing new Primary.'

2023-12-21T06:57:11.540010Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to udb-nd04:3306, udb-nd03:3306, udb-nd07:3306 on view 17026133425141525:13.'

2023-12-21T06:57:11.547447Z 64 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.'

2023-12-21T06:57:11.547512Z 64 [System] [MY-011511] [Repl] Plugin group_replication reported: 'This server is working as secondary member with primary member address udb-nd04:3306.'

2023-12-21T07:01:50.994327Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to udb-nd04:3306, udb-nd05:3306, udb-nd03:3306, udb-nd07:3306 on view 17026133425141525:14.'

2023-12-21T07:01:57.026258Z 0 [System] [MY-011492] [Repl] Plugin group_replication reported: 'The member with address udb-nd05:3306 was declared online within the replication group.'

根据以上日志udb-nd05的mysql服务后，导致集群波动，udb-nd05先是被集群开除；集群再一次做了选举并将udb-nd04作为primary，最后再接入nd05作为只读节点。