Bo's Oracle Station › 博客首页 ›Oracle技术› Linux-MySQL-Java › 查看内容

【博客文章2024】MySQL Innodb Cluster 8.0.36（Single-Primary）的安装和MySQL Router的连接测试

2024-2-26 15:39| 发布者: admin| 查看: 32| 评论: 0|原作者: Bo Tang

摘要: 本博客描述了一套7个节点的MySQL Innodb Cluster 8.0.36（Single-Primary Mode）的安装和MySQL Router的连接测试的详细过程。分为环境和架构、mysqld上要做的准备、使用MySQL Shell配置7个节点的每个节点的mysqld实例、创建MySQL Innodb Cluster、添加MySQL Innodb Cluster的mysqld节点、验证MySQL Innodb Cluster的信息、连接测试以及Failover测试来介绍。

【博客文章2024】MySQL Innodb Cluster 8.0.36（Single-Primary Mode）的安装和MySQL Router的连接测试

Author： Bo Tang

1. 环境和架构：

将要安装一套7个节点的MySQL Innodb Cluster（Single-Primary Mode），版本为8.0.36。所有节点的操作系统都是RedHat Linux8.3（4.18.0-240.el8.x86_64 #1 SMP Wed Sep 23 05:13:10 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux）。MySQL Innodb Cluster集群的示意图如下：

2. 配置Innodb集群之前，在7个节点的每个节点的mysqld上要做的准备：

在7个节点的每个节点上都要安装mysqld，并配置好root@'%'用户：

mysql> set password='oracle_4U';
Query OK, 0 rows affected (0.04 sec)

mysql> update mysql.user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

最重要的是，要在7个节点的每个节点上都进行授权：

mysql> GRANT BACKUP_ADMIN, CLONE_ADMIN, PERSIST_RO_VARIABLES_ADMIN, REPLICATION_APPLIER, SYSTEM_VARIABLES_ADMIN ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

3. 使用MySQL Shell配置7个节点的每个节点的mysqld实例：

[root@node0 ~]# mysqlsh
MySQL Shell 8.0.21

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
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.
MySQL JS > dba.configureInstance('root@node0:3306');
Please provide the password for 'root@node0:3306': *********
Save password for 'root@node0:3306'? [Y]es/[N]o/Ne[v]er (default No):
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

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

NOTE: Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| 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 |
| server_id                | 1             |     | 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...
The instance 'node0.example.com:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at node0.example.com:3306 was restarted.

MySQL JS > dba.configureInstance('root@node1:3306');
Please provide the password for 'root@node1:3306': *********
Configuring MySQL instance at node1.example.com:3306 for use in an InnoDB cluster...
......
Restarting MySQL...
NOTE: MySQL server at node1.example.com:3306 was restarted.

MySQL JS > dba.configureInstance('root@node2:3306');
Please provide the password for 'root@node2:3306': *********
Configuring MySQL instance at node2.example.com:3306 for use in an InnoDB cluster...
......
Restarting MySQL...
NOTE: MySQL server at node2.example.com:3306 was restarted.

MySQL JS > dba.configureInstance('root@node3:3306');
Please provide the password for 'root@node3:3306': *********
Configuring MySQL instance at node3.example.com:3306 for use in an InnoDB cluster...
......
Restarting MySQL...
NOTE: MySQL server at node3.example.com:3306 was restarted.

MySQL JS > dba.configureInstance('root@node4:3306');
Please provide the password for 'root@node4:3306': *********
Configuring MySQL instance at node4.example.com:3306 for use in an InnoDB cluster...
......
Restarting MySQL...
NOTE: MySQL server at node4.example.com:3306 was restarted.

MySQL JS > dba.configureInstance('root@node5:3306');
Please provide the password for 'root@node5:3306': *********
Configuring MySQL instance at node5.example.com:3306 for use in an InnoDB cluster...
......
Restarting MySQL...
NOTE: MySQL server at node5.example.com:3306 was restarted.

MySQL JS > dba.configureInstance('root@node6:3306');
Please provide the password for 'root@node6:3306': *********
Configuring MySQL instance at node6.example.com:3306 for use in an InnoDB cluster...
......
Restarting MySQL...
NOTE: MySQL server at node6.example.com:3306 was restarted.

4. 使用MySQL Shell创建MySQL Innodb Cluster：

MySQL JS > \connect root@node0:3306
Creating a session to 'root@node0:3306'
Please provide the password for 'root@node0:3306': *********
Save password for 'root@node0:3306'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9
Server version: 8.0.36 MySQL Community Server - GPL
No default schema selected; type \use to set one.
MySQL node0:3306 ssl JS > var cluster = dba.createCluster('testCluster');
A new InnoDB cluster will be created on instance 'node0:3306'.

Validating instance configuration at node0:3306...

This instance reports its own address as node0.example.com:3306

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

Creating InnoDB cluster 'testCluster' on 'node0.example.com: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 Innodb Cluster至少要有3个mysqld节点参与才能实现1个节点的Failover。接下来就要添加mysqld节点。

5. 使用MySQL Shell添加MySQL Innodb Cluster的mysqld节点（剩余的6个节点）：

选择“Clone”这种默认方式进行添加mysqld节点：

MySQL node0:3306 ssl JS > cluster.addInstance('root@node1:3306');
Please provide the password for 'root@node1:3306': *********
Save password for 'root@node1:3306'? [Y]es/[N]o/Ne[v]er (default No):

NOTE: The target instance 'node1.example.com: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 'node1.example.com: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):
NOTE: Group Replication will communicate with other members using 'node1.example.com:33061'. Use the localAddress option to override.

Validating instance configuration at node1:3306...

This instance reports its own address as node1.example.com:3306

Instance configuration is suitable.
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: node1.example.com:3306 is being cloned from node0.example.com:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY ############################################################ 100% Completed
    PAGE COPY ############################################################ 100% Completed
    REDO COPY ############################################################ 100% Completed

NOTE: node1.example.com:3306 is shutting down...

* Waiting for server restart... ready
* node1.example.com:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 76.39 MB transferred in about 1 second (~76.39 MB/s)

State recovery already finished for 'node1.example.com:3306'

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

MySQL node0:3306 ssl JS > cluster.addInstance('root@node2:3306');
Please provide the password for 'root@node2:3306': *********
Save password for 'root@node2:3306'? [Y]es/[N]o/Ne[v]er (default No):
......
The instance 'node2:3306' was successfully added to the cluster.

MySQL node0:3306 ssl JS > cluster.addInstance('root@node3:3306');
Please provide the password for 'root@node3:3306': *********
Save password for 'root@node3:3306'? [Y]es/[N]o/Ne[v]er (default No):
......
The instance 'node3:3306' was successfully added to the cluster.

MySQL node0:3306 ssl JS > cluster.addInstance('root@node4:3306');
Please provide the password for 'root@node4:3306': *********
Save password for 'root@node4:3306'? [Y]es/[N]o/Ne[v]er (default No):
......
The instance 'node4:3306' was successfully added to the cluster.

MySQL node0:3306 ssl JS > cluster.addInstance('root@node5:3306');
Please provide the password for 'root@node5:3306': *********
Save password for 'root@node5:3306'? [Y]es/[N]o/Ne[v]er (default No):
......
The instance 'node5:3306' was successfully added to the cluster.

MySQL node0:3306 ssl JS > cluster.addInstance('root@node6:3306');
Please provide the password for 'root@node6:3306': *********
Save password for 'root@node6:3306'? [Y]es/[N]o/Ne[v]er (default No):
......
The instance 'node6:3306' was successfully added to the cluster.

6. 使用MySQL Shell验证MySQL Innodb Cluster的信息：

这套MySQL Innodb Cluster能够对抗3个节点同时出现故障：

MySQL node0:3306 ssl JS > cluster.status();
{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "node0.example.com:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to 3 failures.",
        "topology": {
            "node0.example.com:3306": {
                "address": "node0.example.com:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node1.example.com:3306": {
                "address": "node1.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node2.example.com:3306": {
                "address": "node2.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node3.example.com:3306": {
                "address": "node3.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node4.example.com:3306": {
                "address": "node4.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node5.example.com:3306": {
                "address": "node5.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node6.example.com:3306": {
                "address": "node6.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "node0.example.com:3306"
}
MySQL node0:3306 ssl JS >

进入MySQL Shell的sql模式，查看performance_schema.replication_group_members：

7. MySQL Router的连接测试：

7.1 配置MySQL Router：
安装MySQL Router（rpm -ivh mysql-router-community-8.0.36-1.el8.x86_64.rpm）后，需要进行配置。首先需要生成mysqlrouter.conf这个配置文件：

[root@node0 ~]# mysqlrouter --bootstrap root@node0:3306 --directory /usr/myrouter --user=mysql
Please enter MySQL password for root:
# Bootstrapping MySQL Router 8.0.36 (MySQL Community - GPL) instance at '/usr/myrouter'...

- 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 /usr/myrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'testCluster'

After this MySQL Router has been started with the generated configuration

$ mysqlrouter -c /usr/myrouter/mysqlrouter.conf

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

## MySQL Classic protocol

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

## MySQL X protocol

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

[root@node0 ~]#

MySQL Router的读写连接的端口是6446；MySQL Router的只读连接的端口是6447。

7.2 启动MySQL Router：

[root@node0 ~]# cd /usr/myrouter/
[root@node0 myrouter]# ls
data log mysqlrouter.conf mysqlrouter.key run start.sh stop.sh
[root@node0 myrouter]# ./start.sh
[root@node0 myrouter]# PID 11881 written to '/usr/myrouter/mysqlrouter.pid'
stopping to log to the console. Continuing to log to filelog

7.3 使用客户端连接6446端口，进行single-primary的读写连接测试：

因为只有node0是读写节点（Single-Primary Mode），所以三次连接都是连接到node0。

7.4 使用客户端连接6447端口，进行只读连接的负载均衡测试（因为有6个只读节点可供进行负载均衡选择）：

因为有node1-node6共6个只读节点，所以六次连接会负载均衡到6个节点。

8. MySQL Innodb Cluster节点的Failover故障模拟：

模拟故障，我们关闭唯一的那个读写节点node0上面的mysqld：

[root@node0 myrouter]# systemctl stop mysqld

Innodb Cluster会立即开始Failover（这套MySQL Innodb Cluster能够对抗2个节点同时出现故障），我们的环境中node5成为新的读写节点（primary node）：

MySQL node0:6447 ssl mytest JS > \connect root@node1:3306
Creating a session to 'root@node1:3306'
Please provide the password for 'root@node1:3306': *********
Save password for 'root@node1:3306'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 259
Server version: 8.0.36 MySQL Community Server - GPL
No default schema selected; type \use to set one.
MySQL node1:3306 ssl JS > var cluster=dba.getCluster();

MySQL node1:3306 ssl JS > cluster.status() ;
{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "node5.example.com:3306",
        "ssl": "REQUIRED",
        "status": "OK_PARTIAL",
        "statusText": "Cluster is ONLINE and can tolerate up to 2 failures. 1 member is not active",
        "topology": {
            "node0.example.com:3306": {
                "address": "node0.example.com:3306",
                "mode": "n/a",
                "readReplicas": {},
                "role": "HA",
                "shellConnectError": "MySQL Error 2003 (HY000): Can't connect to MySQL server on 'node0.example.com' (111)",
                "status": "(MISSING)"
            },
            "node1.example.com:3306": {
                "address": "node1.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node2.example.com:3306": {
                "address": "node2.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node3.example.com:3306": {
                "address": "node3.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node4.example.com:3306": {
                "address": "node4.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node5.example.com:3306": {
                "address": "node5.example.com:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            },
            "node6.example.com:3306": {
                "address": "node6.example.com:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.36"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "node5.example.com:3306"
}
MySQL node1:3306 ssl JS >

观察performance_schema.replication_group_members得到一致的信息：

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：【博客文章2024】MySQL Shell 8.0.36和MySQL Workbench 8.0.36在RHEL8上安装出错的处理办法及升级mysqlsh后Innodb Cluster的metadata的升级过程

【博客文章2024】MySQL Innodb Cluster 8.0.36（Single-Primary）的安装和MySQL Router的连接测试

相关分类