MySQL高可用InnoDB Cluster

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

简单爱你心所爱

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

阅读量338

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/evil_wdpp/article/details/132741049

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

InnoDB Cluster

InnoDB Cluster是MySQL官方推出的一个全栈高可用解决方案。

InnoDB Cluster包含3个核心组件

MySQL Group Replication（MGR组复制）

MySQL Shell（MySQL高级客户端）

MySQL Router（MySQL路由）

MySQL Shell安装与配置

1、安装MySQL Shell
[root@node01 ~]# wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.33-linux-glibc2.12-x86-64bit.tar.gz
[root@node01 ~]# tar -xf mysql-shell-8.0.33-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local
[root@node01 ~]# ln -s /usr/local/mysql-shell-8.0.33-linux-glibc2.12-x86-64bit/ /usr/local/mysqlsh
[root@node01 ~]# mysqlsh --help
MySQL Shell 8.0.33


Usage examples:
$ mysqlsh --login-path=server1 --sql
$ mysqlsh root@localhost/schema
$ mysqlsh mysqlx://root@some.server:3307/world_x
$ mysqlsh --uri root@localhost --py -f sample.py sample param
$ mysqlsh root@targethost:33070 -s world_x -f sample.js
$ mysqlsh -- util check-for-server-upgrade root@localhost --output-format=JSON

2、使用MySQL Shell
root@localhost [(none)]> create user 'root'@'%' identified by 'oracle';
Query OK, 0 rows affected (0.07 sec)
root@localhost [(none)]> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.01 sec)
[root@node01 ~]# mysql -hnode01 -uroot -p
root@node01 [(none)]> 

[root@node01 ~]# mysqlsh --mysql -hnode01 --port 3306 -uroot -poracle
MySQL Shell 8.0.33

Copyright (c) 2016, 2023, 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.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@node01:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 62
Server version: 8.0.33 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  node01:3306 ssl  JS > 

 MySQL  node01:3306 ssl  JS > session
<ClassicSession:root@node01:3306>

 MySQL  node01:3306 ssl  JS > \py
Switching to Python mode...
 MySQL  node01:3306 ssl  Py > \js
Switching to JavaScript mode...

 MySQL  node01:3306 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  node01:3306 ssl  SQL > 
 
root@node01 ~]# mysqlsh --mysqlx -hnode01 --port 33060 -uroot -poracle
MySQL Shell 8.0.33

Copyright (c) 2016, 2023, 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.
WARNING: Using a password on the command line interface can be insecure.
Creating an X protocol session to 'root@node01:33060'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 63 (X protocol)
Server version: 8.0.33 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  node01:33060+ ssl  JS > session
<Session:root@node01:33060>
 MySQL  node01:33060+ ssl  JS >

MySQL Router安装与配置

1、安装MySQL Router
[root@node01 ~]# wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xz
[root@node01 ~]# tar -xvf mysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
[root@node01 ~]# ln -s /usr/local/mysql-router-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysqlrouter
[root@node01 ~]# mysqlrouter --help
MySQL Router  Ver 8.0.33 for Linux on x86_64 (MySQL Community - GPL)
Copyright (c) 2015, 2023, 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.

Configuration read from the following files in the given order (enclosed
in parentheses means not available for reading):
  (/usr/local/mysql-router-8.0.33-linux-glibc2.12-x86_64/bin/.././mysqlrouter.conf)
  (/usr/local/mysql-router-8.0.33-linux-glibc2.12-x86_64/bin/.././mysqlrouter.ini)
  (/root/.mysqlrouter.conf)
  (/root/.mysqlrouter.ini)
# Examples

Bootstrap for use with InnoDB cluster into system-wide installation

    sudo mysqlrouter --bootstrap root@clusterinstance01 --user=mysqlrouter

Start router

    sudo mysqlrouter --user=mysqlrouter

Bootstrap for use with InnoDb cluster in a self-contained directory

    mysqlrouter --bootstrap root@clusterinstance01 -d myrouter

Start router                     

    myrouter/start.sh  

2、配置MySQL Router
root@node01 [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id                            | member_host   | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 66d72c0c-4634-11ee-a0bc-000c29004cdf | 192.168.6.101 |        3306 | ONLINE       | PRIMARY     |
| 9844cd81-4634-11ee-87f2-000c297a8cee | 192.168.6.102 |        3306 | ONLINE       | SECONDARY   |
| a3dac279-4634-11ee-9220-000c296e813f | 192.168.6.103 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

使用bootstrap的方式初始化MySQL Router的前提条件是MGR搭建成功，MySQL Shell已装好且InnoDB Cluster配置成功
[root@node01 ~]# mysqlrouter --bootstrap root@192.168.6.101:3306 --user=mysql --directory=/data/myrouter/6446 --conf-use-sockets --report-host='192.168.6.101'
Please enter MySQL password for root: 
Error: Expected MySQL Server '192.168.6.101:3306' to contain the metadata of MySQL InnoDB Cluster, but the schema does not exist.
Checking version of the metadata schema failed with: Error executing MySQL query "SELECT * FROM mysql_innodb_cluster_metadata.schema_version": Unknown database 'mysql_innodb_cluster_metadata' (1049)

See https://dev.mysql.com/doc/mysql-shell/en/deploying-production-innodb-cluster.html for instructions on setting up a MySQL Server to act as an InnoDB Cluster Metadata server

InnoDB Cluster集群规划

IP	主机名	高可用	角色	MySQL版本	server-id	端口
192.168.6.101	node01	读写	Primary	8.0.33	1001	6446
192.168.6.102	node02	只读	Secondary	8.0.33	1002	6447
192.168.6.103	node03	只读	Secondary	8.0.33	1003	6447

初始化MySQL

1、准备安装环境
关闭防火墙
[root@node01 ~]# systemctl stop firewalld
[root@node01 ~]# systemctl disablefirewalld

关闭selinux
[root@node01 ~]# sed -n '/SELINUX=enforcing/c SELINUX=disabled' /etc/sysconfig/selinux 
SELINUX=disabled

2、准备安装包
[root@node01 ~]# id mysql
uid=54321(mysql) gid=54321(mysql) groups=54321(mysql)
[root@node01 ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
[root@node01 ~]# ln -s /usr/local/cluster/mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql

3、创建数据目录
[root@node01 ~]# mkdir -p /data/mysql/3306/data
[root@node01 ~]# chown -R mysql:mysql /data/mysql/3306/data

4、修改配置文件
[root@node01 ~]# vim /etc/my.cnf
[client]
port=3306
socket=/data/mysql/3306/data/mysql.sock

[mysql]
prompt="\\u@\\h [\\d]> "

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3306/data
user=mysql
port=3306
pid-file=/data/mysql/3306/data/mysql.pid
socket=/data/mysql/3306/data/mysql.sock
log-error=/data/mysql/3306/data/mysql.err
log-timestamps=system
skip-name-resolve
report-host="192.168.6.101"
report-port=3306
disabled-storage-engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#gtid
server-id=1001
log-bin=mysql-bin
binlog-format=row
log-slave-updates=on
gtid-mode=on
enforce-gtid-consistency=on
master-info-repository=table
relay-log-info-repository=table
binlog-transaction-dependency-tracking=writeset
transaction-write-set-extraction=xxhash64
#mult-thread repl
slave-parallel-type=logical_clock
slave-preserve-commit-order=on
slave-parallel-workers=4

node02的配置文件修改的部分
report-host="192.168.6.102"
server-id=1002

node03的配置文件修改的部分
report-host="192.168.6.103"
server-id=1003

5、初始化实例
[root@node01 ~]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize-insecure

6、启动实例
[root@node01 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

配置实例

1、创建超级管理员账号，3个节点都要创建
root@localhost [(none)]> set session sql_log_bin=0;
root@localhost [(none)]> create user 'root'@'%' identified by 'oracle';
root@localhost [(none)]> grant all privileges on *.* to 'root'@'%';
root@localhost [(none)]> set session sql_log_bin=1;

2、配置node01
[root@node01 ~]# mysqlsh --mysql -hnode01 --port 3306 -uroot -poracle
MySQL Shell 8.0.33

Copyright (c) 2016, 2023, 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.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@node01:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 10
Server version: 8.0.33 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
mysql-js> session
<ClassicSession:root@node01:3306>

mysql-js> dba.configureInstance('root:oracle@192.168.6.101:3306',{clusterAdmin:'cluster_admin',clusterAdminPassword:'cluster_pass'})
Configuring local MySQL instance listening at port 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;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'root'@'%' WITH GRANT OPTION;
For more information, see the online documentation.
Dba.configureInstance: The account 'root'@'%' is missing privileges required to manage an InnoDB cluster. (RuntimeError)

授权需要添加with grant option

root@localhost [(none)]> grant all privileges on *.* to 'root'@'%' with grant option;

mysql-js> dba.configureInstance('root:oracle@192.168.6.101:3306',{clusterAdmin:'cluster_admin',clusterAdminPassword:'cluster_pass'})
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 192.168.6.101:3306
Assuming full account name 'cluster_admin'@'%' for cluster_admin

applierWorkerThreads will be set to the default value of 4.

The instance '192.168.6.101:3306' is valid to be used in an InnoDB cluster.

Creating user cluster_admin@%.
Account cluster_admin@% was successfully created.

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

Successfully enabled parallel appliers.

2、配置node02
[root@node02 ~]# mysqlsh --mysql -hnode02 --port 3306 -uroot -poracle
MySQL Shell 8.0.33

Copyright (c) 2016, 2023, 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.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@node02:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 8
Server version: 8.0.33 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  node02:3306 ssl  JS > dba.configureInstance('root:oracle@192.168.6.102:3306',{clusterAdmin:'cluster_admin',clusterAdminPassword:'cluster_pass'})
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 192.168.6.102:3306
Assuming full account name 'cluster_admin'@'%' for cluster_admin

applierWorkerThreads will be set to the default value of 4.

The instance '192.168.6.102:3306' is valid to be used in an InnoDB cluster.

Creating user cluster_admin@%.
Account cluster_admin@% was successfully created.

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

Successfully enabled parallel appliers.

3、配置node03
[root@node03 ~]# mysqlsh --mysql -hnode03 --port 3306 -uroot -poracle
MySQL Shell 8.0.33

Copyright (c) 2016, 2023, 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.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@node03:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 9
Server version: 8.0.33 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  node03:3306 ssl  JS > dba.configureInstance('root:oracle@192.168.6.103:3306',{clusterAdmin:'cluster_admin',clusterAdminPassword:'cluster_pass'})
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 192.168.6.103:3306
Assuming full account name 'cluster_admin'@'%' for cluster_admin

applierWorkerThreads will be set to the default value of 4.

The instance '192.168.6.103:3306' is valid to be used in an InnoDB cluster.

Creating user cluster_admin@%.
Account cluster_admin@% was successfully created.

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

Successfully enabled parallel appliers.

创建InnoDB Cluster


1、创建cluster，在node01上面执行
[root@node01 ~]# mysqlsh --mysql -hnode01 --port 3306 -uroot -poracle
mysql-js> shell.connect('cluster_admin:cluster_pass@192.168.6.101:3306')
Creating a session to 'cluster_admin@192.168.6.101:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 11
Server version: 8.0.33 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
<ClassicSession:cluster_admin@192.168.6.101:3306>

mysql-js> dba.createCluster('cluster01',{disableClone:false})
A new InnoDB Cluster will be created on instance '192.168.6.101:3306'.

Validating instance configuration at 192.168.6.101:3306...

This instance reports its own address as 192.168.6.101:3306

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

* Checking connectivity and SSL configuration...

Creating InnoDB Cluster 'cluster01' on '192.168.6.101: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.

<Cluster:cluster01>

2、创建Cluster对象
mysql-js> cluster = dba.getCluster('cluster01')
<Cluster:cluster01>

3、添加node02
mysql-js> cluster.addInstance('cluster_admin:cluster_pass@192.168.6.102:3306')

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

192.168.6.102:3306 has the following errant GTIDs that do not exist in the cluster:
2dba3c9d-47cb-11ee-915d-000c297a8cee:1-3

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of 192.168.6.102: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.6.102:3306...

This instance reports its own address as 192.168.6.102:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '192.168.6.102: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: 192.168.6.102:3306 is being cloned from 192.168.6.101:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: 192.168.6.102:3306 is shutting down...

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

4、添加nod03
mysql-js> cluster.addInstance('cluster_admin:cluster_pass@192.168.6.103:3306')

5、查看集群状态
mysql-js> cluster.status()
{
    "clusterName": "cluster01", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.6.101:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.6.101:3306": {
                "address": "192.168.6.101:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.33"
            }, 
            "192.168.6.102:3306": {
                "address": "192.168.6.102:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.33"
            }, 
            "192.168.6.103:3306": {
                "address": "192.168.6.103:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.33"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "192.168.6.101:3306"
}

部署MySQL Router

1、创建目录
[root@node01 ~]# mkdir -p /data/myrouter/6446

2、初始化router
[root@node01 ~]# mysqlrouter --bootstrap cluster_admin@192.168.6.101:3306 --user=mysql --directory=/data/myrouter/6446 --conf-use-sockets --report-host='192.168.6.101'
Please enter MySQL password for cluster_admin: cluster_pass
# Bootstrapping MySQL Router instance at '/data/myrouter/6446'...

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

# MySQL Router configured for the InnoDB Cluster 'cluster01'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /data/myrouter/6446/mysqlrouter.conf

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

## MySQL Classic protocol

- Read/Write Connections: 192.168.6.101:6446, /data/myrouter/6446/mysql.sock
- Read/Only Connections:  192.168.6.101:6447, /data/myrouter/6446/mysqlro.sock

## MySQL X protocol

- Read/Write Connections: 192.168.6.101:6448, /data/myrouter/6446/mysqlx.sock
- Read/Only Connections:  192.168.6.101:6449, /data/myrouter/6446/mysqlxro.sock

启动MySQL Router

[root@node01 ~]# ll -h /data/myrouter/6446/start.sh 
-rwx------ 1 mysql mysql 387 Sep  1 18:00 /data/myrouter/6446/start.sh

[root@node01 ~]# /data/myrouter/6446/start.sh
[root@node01 ~]# PID 1836 written to '/data/myrouter/6446/mysqlrouter.pid'
stopping to log to the console. Continuing to log to filelog

[root@node01 ~]# cat /data/myrouter/6446/mysqlrouter.pid
1836
 
[root@node01 ~]# netstat -ntlup | grep mysqlrouter
tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      1836/mysqlrouter    
tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      1836/mysqlrouter    
tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      1836/mysqlrouter    
tcp        0      0 0.0.0.0:6448            0.0.0.0:*               LISTEN      1836/mysqlrouter    
tcp        0      0 0.0.0.0:6449            0.0.0.0:*               LISTEN      1836/mysqlrouter

测试MySQL Router

1、高可用
当Primary节点宕机，集群是否还提供写服务

2、读写分离
读操作的负载均衡

3、首先测试读操作的负载均衡连接6447端口
[root@node01 ~]# mysql -hnode01 -P 6447 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1002 |
+-------------+
[root@node01 ~]# mysql -hnode01 -P 6447 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1003 |
+-------------+
[root@node01 ~]# mysql -hnode01 -P 6447 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1002 |
+-------------+
[root@node01 ~]# mysql -hnode01 -P 6447 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1003 |
+-------------+

可以看到读操作实现了负载均衡

测试高可用连接6446端口，只连接node01节点
[root@node01 ~]# mysql -hnode01 -P 6446 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1001 |
+-------------+

[root@node01 ~]# mysql -hnode01 -P 6446 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1001 |
+-------------+

4、模拟primary节点故障

[root@node01 ~]# /usr/local/mysql/support-files/mysql.server status
 SUCCESS! MySQL running (1111)
[root@node01 ~]# /usr/local/mysql/support-files/mysql.server stop
Shutting down MySQL....... SUCCESS! 

此时Primary节点切换到了node02节点，node01已经从集群中剔除了
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id                            | member_host   | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 2dba3c9d-47cb-11ee-915d-000c297a8cee | 192.168.6.102 |        3306 | ONLINE       | PRIMARY     |
| 2ddaa8db-47cb-11ee-a248-000c296e813f | 192.168.6.103 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+---------------+-------------+--------------+-------------+
2 rows in set (0.00 sec)

[root@node01 ~]# mysql -hnode01 -P 6446 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1002 |
+-------------+

[root@node01 ~]# mysql -hnode01 -P 6446 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1002 |
+-------------+

恢复node01，可以看到node01添加到集群中了，成为了Secondary节点
[root@node01 ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL. SUCCESS! 

root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id                            | member_host   | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 2dba3c9d-47cb-11ee-915d-000c297a8cee | 192.168.6.102 |        3306 | ONLINE       | PRIMARY     |
| 2ddaa8db-47cb-11ee-a248-000c296e813f | 192.168.6.103 |        3306 | ONLINE       | SECONDARY   |
| 2fafbd0f-47cb-11ee-9e21-000c29004cdf | 192.168.6.101 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)


读操作负载均衡

[root@node01 ~]# mysql -hnode01 -P 6447 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1001 |
+-------------+

[root@node01 ~]# mysql -hnode01 -P 6447 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1003 |
+-------------+

[root@node01 ~]# mysql -hnode01 -P 6447 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1001 |
+-------------+

[root@node01 ~]# mysql -hnode01 -P 6447 -uroot -poracle -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|        1003 |
+-------------+

单主模式切换新的Primary

1、节点状态
root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id                            | member_host   | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 2dba3c9d-47cb-11ee-915d-000c297a8cee | 192.168.6.102 |        3306 | ONLINE       | PRIMARY     |
| 2ddaa8db-47cb-11ee-a248-000c296e813f | 192.168.6.103 |        3306 | ONLINE       | SECONDARY   |
| 2fafbd0f-47cb-11ee-9e21-000c29004cdf | 192.168.6.101 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

mysql-js> cluster.status()
{
    "clusterName": "cluster01", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.6.102:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.6.101:3306": {
                "address": "192.168.6.101:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.33"
            }, 
            "192.168.6.102:3306": {
                "address": "192.168.6.102:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.33"
            }, 
            "192.168.6.103:3306": {
                "address": "192.168.6.103:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.33"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "192.168.6.102:3306"
}


2、当前primary是node2，切换成node03
mysql-js> cluster.setPrimaryInstance('192.168.6.103:3306')
Setting instance '192.168.6.103:3306' as the primary instance of cluster 'cluster01'...

Instance '192.168.6.101:3306' remains SECONDARY.
Instance '192.168.6.102:3306' was switched from PRIMARY to SECONDARY.
Instance '192.168.6.103:3306' was switched from SECONDARY to PRIMARY.

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

root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id                            | member_host   | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 2dba3c9d-47cb-11ee-915d-000c297a8cee | 192.168.6.102 |        3306 | ONLINE       | SECONDARY   |
| 2ddaa8db-47cb-11ee-a248-000c296e813f | 192.168.6.103 |        3306 | ONLINE       | PRIMARY     |
| 2fafbd0f-47cb-11ee-9e21-000c29004cdf | 192.168.6.101 |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

mysql-js> cluster.status()
{
    "clusterName": "cluster01", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.6.103:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.6.101:3306": {
                "address": "192.168.6.101:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.33"
            }, 
            "192.168.6.102:3306": {
                "address": "192.168.6.102:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.33"
            }, 
            "192.168.6.103:3306": {
                "address": "192.168.6.103:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.33"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "192.168.6.103:3306"
}

单主模式切换到多主模式

mysql-js> cluster.switchToMultiPrimaryMode()
Switching cluster 'cluster01' to Multi-Primary mode...

Instance '192.168.6.103:3306' remains PRIMARY.
Instance '192.168.6.102:3306' was switched from SECONDARY to PRIMARY.
Instance '192.168.6.101:3306' was switched from SECONDARY to PRIMARY.

The cluster successfully switched to Multi-Primary mode.

root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id                            | member_host   | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 2dba3c9d-47cb-11ee-915d-000c297a8cee | 192.168.6.102 |        3306 | ONLINE       | PRIMARY     |
| 2ddaa8db-47cb-11ee-a248-000c296e813f | 192.168.6.103 |        3306 | ONLINE       | PRIMARY     |
| 2fafbd0f-47cb-11ee-9e21-000c29004cdf | 192.168.6.101 |        3306 | ONLINE       | PRIMARY     |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

多主模式切换到单主模式

mysql-js> cluster.switchToSinglePrimaryMode('192.168.6.101:3306')
Switching cluster 'cluster01' to Single-Primary mode...

Instance '192.168.6.101:3306' remains PRIMARY.
Instance '192.168.6.102:3306' was switched from PRIMARY to SECONDARY.
Instance '192.168.6.103:3306' was switched from PRIMARY to SECONDARY.

WARNING: Existing connections that expected a R/W connection must be disconnected, i.e. instances that became SECONDARY.

The cluster successfully switched to Single-Primary mode.


root@localhost [(none)]> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members;
+--------------------------------------+---------------+-------------+--------------+-------------+
| member_id                            | member_host   | member_port | member_state | member_role |
+--------------------------------------+---------------+-------------+--------------+-------------+
| 2dba3c9d-47cb-11ee-915d-000c297a8cee | 192.168.6.102 |        3306 | ONLINE       | SECONDARY   |
| 2ddaa8db-47cb-11ee-a248-000c296e813f | 192.168.6.103 |        3306 | ONLINE       | SECONDARY   |
| 2fafbd0f-47cb-11ee-9e21-000c29004cdf | 192.168.6.101 |        3306 | ONLINE       | PRIMARY     |
+--------------------------------------+---------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)