篇幅介绍

• 利用MySQL Shell构建MGR集群
• 对在线的MGR集群添加一个新节点
• 部署MySQL Router，实现读写分离以及故障自动转移

MySQL InnoDB Cluster 简介

基于 MySQL Group Replication构建，提供自动成员管理、容错、自动故障转移等功能。InnoDB Cluster 通常以单主模式运行，具有一个主实例（读写）和多个从实例（只读）。

• MySQL Server，核心是Group Replication（组复制），简称MGR。
• MySQL Shell，可编程的高级客户端，支持标准SQL语法、JavaScript语法、Python语法，以及API接口，可以更方便的管理和使用MySQL服务器。
• MySQL Router，轻量级中间件，支持透明路由规则（读写分离及读负载均衡）。
• 建议 MGR集群采用单主（single-primary）模式

image

配置hosts

以下安装需要分别在所有服务器端安装

# 所有机器设置hosts
echo '192.168.66.101 mysql01' >> /etc/hosts
echo '192.168.66.102 mysql02' >> /etc/hosts
echo '192.168.66.103 mysql03' >> /etc/hosts

安装MySQL

MGR集群由三个实例构成，按下面规划分配：

• MySQL 8.0.27
• mysql-router-community-8.0.27
• mysql-shell-8.0.27
  | 实例 | IP | 端口 | datadir |
  | --- | --- | --- | --- |
  | mysql01 | 191.168.66.101 | 3306 | /data/mysql827/ |
  | mysql02 | 191.168.66.102 | 3306 | /data/mysql827/ |
  | mysql03 | 191.168.66.103 | 3306 | /data/mysql827/ |

shell> rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
shell> tar -xf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
shell> cd /usr/local/
shell> ln -s mysql-8.0.27-linux-glibc2.12-x86_64/ mysql
shell> mkdir -p /data/mysql827
shell> groupadd mysql
shell> useradd -g mysql -s /bin/nologin mysql
shell> chown -R mysql:mysql /data/mysql827/
shell> echo "export PATH=$PATH:/usr/local/mys
cp mysql/support-files/mysql.server /etc/init.d/mysqldql/bin" >> /etc/profile
shell> source /etc/profile
# 配置my.cnf，文末会附上一份配置供参考
shell> vi /etc/my.cnf

# 初始化
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize  --user=mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

# 启动
service mysqld start

# 修改密码
grep 'temporary password' /data/mysql827/error.log

mysql> alter user current_user() identified by '123456';

# 创建一个超级用户
mysql> create user 'super_admin'@'%' identified by '123456';
mysql> grant all on *.* to 'super_admin'@'%' with grant option;

安装 router 和 mysqlshell

简单的使用 yum 安装即可

# 安装源
yum -y install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
# 安装社区版
yum install -y mysql-shell mysql-router

利用MySQL Shell构建MGR集群

利用MySQL Shell构建MGR集群主要简单几个命令就可以了
只需三步：
1.检查实例是否满足条件。
2.创建并初始化一个集群。
3.逐个添加实例。

连接mysqlsh

[root@mysql01 ~]# mysqlsh
MySQL Shell 8.0.27

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
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.

# 利用 socket 方式连接
 MySQL  JS > \connect root@localhost?socket=(/data/mysql827/mysql.sock)
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost': ******    -- 输入密码
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): y -- 是否存储密码，下次登陆不需要输入密码，视情况而定
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 31
Server version: 8.0.27 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.  -- 成功连接到服务器端

 MySQL  localhost  JS > \sql select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.0001 sec)

1.检查是否满足安装集群的条件

MySQL  localhost  JS > dba.configureInstance();
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as mysql01: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.

# 提示root账号不能运行MGR服务，需要创建新的专用账号
ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.

1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel

# 选择2，创建最小权限的账户
Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: mgr_user  -- 输入账号名
Password for new account: ******   -- 密码
Confirm password: ******

applierWorkerThreads will be set to the default value of 4.

# 节点初始化完毕
The instance 'mysql01:3306' is valid to be used in an InnoDB cluster.

# MGR管理账号创建完毕，当前机器集群环境已准备好
Cluster admin user 'mgr_user'@'%' created.
The instance 'mysql01:3306' is already ready to be used in an InnoDB cluster.

Successfully enabled parallel appliers.

同样的方法 dba.configureInstance(); 在其他节点同样执行一遍

2.创建集群

各节点初始化完成后，使用mysqlsh，用新建的mgr管理用户登陆后创建MGR集群

# 若退出了可使用 mysqlsh --uri mgr_user@192.168.66.101:3306 登陆
MySQL  localhost  JS > \connect mgr_user@192.168.66.101:3306
Creating a session to 'mgr_user@192.168.66.101:3306'
Please provide the password for 'mgr_user@192.168.66.101:3306': ******
Save password for 'mgr_user@192.168.66.101:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 35
Server version: 8.0.27 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  192.168.66.101:3306 ssl  JS > \sql select user();
+-------------------------+
| user()                  |
+-------------------------+
| mgr_user@192.168.66.101 |
+-------------------------+
1 row in set (0.0003 sec)

#在PRIMARY节点上开始创建MGR集群,集群命名为 greatsqlMGR
MySQL  192.168.66.101:3306 ssl  JS > var mgrcluster = dba.createCluster('yqtestMGR');
A new InnoDB cluster will be created on instance '192.168.66.101:3306'.

Validating instance configuration at 192.168.66.101:3306...

This instance reports its own address as mysql01:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysql01:33061'. Use the localAddress option to override.

Creating InnoDB cluster 'yqtestMGR' on 'mysql01: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.

3.添加其他节点

当前是在PRIMARY节点上直接添加其他节点，也可以用mysqlsh客户端登入其他节点执行添加节点操作

# 添加其他MGR节点
MySQL  192.168.66.101:3306 ssl  JS > mgrcluster.addInstance('mgr_user@192.168.66.102:3306');

WARNING: A GTID set check of the MySQL instance at 'mysql02:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

mysql02:3306 has the following errant GTIDs that do not exist in the cluster:
9d4c3a9a-51f5-11ec-b711-000c290b0ee7:1

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of mysql02:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.

# #选择恢复模式：克隆/终止，默认是终止
Please select a recovery method [C]lone/[A]bort (default Abort): C
Validating instance configuration at 192.168.66.102:3306...

This instance reports its own address as mysql02:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysql02:33061'. Use the localAddress option to override.

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.

#从mysql01节点克隆数据
* Waiting for clone to finish...
NOTE: mysql02:3306 is being cloned from mysql01:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: mysql02:3306 is shutting down...

* Waiting for server restart... ready  -- 重启完毕
* mysql02:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 73.65 MB transferred in about 1 second (~73.65 MB/s)

State recovery already finished for 'mysql02:3306'

The instance 'mysql02:3306' was successfully added to the cluster.





# 添加第二个节点
MySQL  192.168.66.101:3306 ssl  JS > mgrcluster.addInstance('mgr_user@192.168.66.103:3306');

NOTE: The target instance 'mysql03: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 'mysql03: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 the same 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): C
Validating instance configuration at 192.168.66.103:3306...

This instance reports its own address as mysql03:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'mysql03:33061'. Use the localAddress option to override.

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: mysql03:3306 is being cloned from mysql01:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: mysql03:3306 is shutting down...

* Waiting for server restart... ready
* mysql03:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 73.66 MB transferred in about 1 second (~73.66 MB/s)

State recovery already finished for 'mysql03:3306'

The instance 'mysql03:3306' was successfully added to the cluster.

4.查看集群状态

# 由于之前退出了，所以重新定义下 mgrcluster
MySQL  192.168.66.101:3306 ssl  JS > var mgrcluster = dba.getCluster('yqtestMGR');

# 查看集群状态
MySQL  192.168.66.101:3306 ssl  JS > mgrcluster.describe();
{
    "clusterName": "yqtestMGR",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "mysql01:3306",
                "label": "mysql01:3306",
                "role": "HA"
            },
            {
                "address": "mysql02:3306",
                "label": "mysql02:3306",
                "role": "HA"
            },
            {
                "address": "mysql03:3306",
                "label": "mysql03:3306",
                "role": "HA"
            }
        ],
        "topologyMode": "Single-Primary"
    }
}
 MySQL  192.168.66.101:3306 ssl  JS > mgrcluster.status();
{
    "clusterName": "yqtestMGR",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysql01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "mysql01:3306": {
                "address": "mysql01:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.27"
            },
            "mysql02:3306": {
                "address": "mysql02:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.27"
            },
            "mysql03:3306": {
                "address": "mysql03:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.27"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mysql01:3306"
}

 MySQL  192.168.66.101:3306 ssl  JS > \sql select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 53929ed7-51f4-11ec-9930-000c298b7ae4 | mysql01     |        3306 | ONLINE       | PRIMARY     | 8.0.27         | XCom                       |
| group_replication_applier | 9d4c3a9a-51f5-11ec-b711-000c290b0ee7 | mysql02     |        3306 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
| group_replication_applier | 9db0efb9-51f5-11ec-b7be-000c29e8138b | mysql03     |        3306 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.0010 sec)

对在线的MGR集群添加一个新节点

上面的MGR集群已经上线了，现在想添加一个新节点 mysql04:3306，步骤和上面基本上一样，三步走：

• 用有管理权限的账号登入MySQL实例（用mysqlsh客户端）。
• 执行 dba.configureInstance() 函数预处理。
• 执行 cluster.addInstance() 函数加入集群。

详细过程就不赘述了，自行操作。
如果是想删除一个节点，则改成执行 cluster.removeInstance() 函数即可。

部署MySQL Router

使用MySQL Router来实现读写分离，与故障转移自动发现

1.初始化

#
#参数解释
# 参数 --bootstrap 表示开始初始化
# 参数 mgr_user@192.168.66.101:3306 是MGR集群管理员账号
# --user=mysqlrouter 是运行mysqlrouter进程的系统用户名
#

[root@mysql01 ~]# mysqlrouter --bootstrap mgr_user@192.168.66.101:3306 --user=mysqlrouter
Please enter MySQL password for mgr_user:    -- 输入mgr_user密码
# Bootstrapping system MySQL Router instance...

# mysqlrouter开始自动进行初始化，自动读取MGR的元数据信息，自动生成配置文件

- 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 'yqtestMGR'

After this MySQL Router has been started with the generated configuration

    $ /etc/init.d/mysqlrouter restart
or
    $ systemctl start mysqlrouter
or
    $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

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

## MySQL Classic protocol  <-- MySQL协议的两个端口

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL X protocol  <-- MySQL X协议的两个端口

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449

2.启动mysql router

[root@mysql01 ~]# systemctl start mysqlrouter

[root@mysql01 ~]# systemctl status mysqlrouter
● mysqlrouter.service - MySQL Router
   Loaded: loaded (/usr/lib/systemd/system/mysqlrouter.service; disabled; vendor preset: disabled)
   Active: active (running) since Thu 2021-12-02 00:33:32 CST; 4s ago
 Main PID: 41601 (mysqlrouter)
   Status: "running"
   CGroup: /system.slice/mysqlrouter.service
           └─41601 /usr/bin/mysqlrouter

Dec 02 00:33:32 mysql01 systemd[1]: Starting MySQL Router...
Dec 02 00:33:32 mysql01 mysqlrouter[41601]: logging facility initialized, switching logging to loggers specified in configuration
Dec 02 00:33:32 mysql01 systemd[1]: Started MySQL Router.

[root@mysql01 ~]# ss -lntp | grep mysqlrouter
LISTEN     0      128          *:6446                     *:*                   users:(("mysqlrouter",pid=41601,fd=44))
LISTEN     0      128          *:6447                     *:*                   users:(("mysqlrouter",pid=41601,fd=43))
LISTEN     0      128          *:6448                     *:*                   users:(("mysqlrouter",pid=41601,fd=46))
LISTEN     0      128          *:6449                     *:*                   users:(("mysqlrouter",pid=41601,fd=45))
LISTEN     0      128          *:8443                     *:*                   users:(("mysqlrouter",pid=41601,fd=14))

初始化文件的路径是 /etc/mysqlrouter/mysqlrouter.conf
可以根据需要自行修改绑定的IP地址和端口。

3.验证读写分离端口

# 注：当前是在 102 服务器端
[root@mysql02 ~]# mysql -h192.168.66.101 -u mgr_user -p -P6446

mgr_user@mysqldb 00:55:  [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         101 |   -- 主库-primary 的server_id
+-------------+
1 row in set (0.00 sec)

# 注：端口改成了 6447 只读端口，只读流量只会打到只读实例上，会对只读实例进行轮询
[root@mysql02 ~]# mysql -h192.168.66.101 -u mgr_user -p -P6447

mgr_user@mysqldb 00:57:  [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         102 |
+-------------+
1 row in set (0.00 sec)

mgr_user@mysqldb 00:57:  [(none)]> exit
Bye

# 退出再次登陆下，会轮询到另一台只读实例上
[root@mysql02 ~]# mysql -h192.168.66.101 -u mgr_user -p -P6447

mgr_user@mysqldb 00:59:  [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         103 |
+-------------+
1 row in set (0.00 sec)

4.验证router故障自动转移功能

后面MGR进行切换后，router应当能自动感知到那一台被切换为新主了，读写流量分别打到对应的实例上去

[root@mysql01 ~]# mysqlsh --uri mgr_user@192.168.66.101:3306
# 获取集群
MySQL  192.168.66.101:3306 ssl  JS > var mgrcluster = dba.getCluster('yqtestMGR');

# 切换主为 mysql02
 MySQL  192.168.66.101:3306 ssl  JS > mgrcluster.setPrimaryInstance('mysql02:3306');
Setting instance 'mysql02:3306' as the primary instance of cluster 'yqtestMGR'...

Instance 'mysql01:3306' was switched from PRIMARY to SECONDARY.
Instance 'mysql02:3306' was switched from SECONDARY to PRIMARY.
Instance 'mysql03:3306' remains SECONDARY.

WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.getCluster().

The instance 'mysql02:3306' was successfully elected as primary.

# 使用router 读写端口登陆
[root@mysql02 ~]# mysql -h192.168.66.101 -u mgr_user -p -P6446

# 发现主已经是 102 了
mgr_user@mysqldb 01:16:  [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         102 |
+-------------+
1 row in set (0.01 sec)

查看集群状态

 MySQL  192.168.66.101:3306 ssl  JS > mgrcluster.status();
{
    "clusterName": "yqtestMGR",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysql02:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "mysql01:3306": {
                "address": "mysql01:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.27"
            },
            "mysql02:3306": {
                "address": "mysql02:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.27"
            },
            "mysql03:3306": {
                "address": "mysql03:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.27"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mysql01:3306"
}

至此，整个集群部署完成

参考：
https://dev.mysql.com/doc/mysql-shell/8.0/en/deploying-production-innodb-cluster.html
https://mp.weixin.qq.com/s?__biz=MzIyOTUzNjgwNg==&mid=2247484255&idx=1&sn=607ff0c180288bfb4c02cbc1d32d70b9&chksm=e8406616df37ef0045e16492259cda69809d74c9b8260e1aac39d7757eff76ce5a362fe9cdbb&cur_album_id=2019578213269667840&scene=189#wechat_redirect
https://mp.weixin.qq.com/s/1cEd9EXvnSrChOv-_D6drA