InnoDB Cluster 介绍

MySQL InnoDB 集群为 MySQL 提供了完整的高可用性解决方案。 MySQL Shell包含AdminAPI，能轻松配置和管理MySQL实例组，组内的每个MySQL实例都运行MGR，MGR提供了InnoDB集群内部数据复制和内置的故障转移功能。 MySQL Router 能够根据部署的集群进行自我配置，将连接客户端的应用转移到后端服务器实例上。

InnoDB Cluster 概述

如图所示，3个MySQL server 实例，组成了单主模式的高可用集群；MySQL router 负责将客户端应用的连接请求转发到后端MySQL实例上；MySQL Shell包含AdminAPI，可通过dba全局变量部署，配置和管理InnoDB的集群。

InnoDB Cluster 创建

连接实例

通过MySQL shell 连接任意一个实例：
cd /opt/soft/mysqlshell
./mysqlsh –mysql –uri root@localhost:3008
……
Server version: 8.0.18 MySQL Community Server - GPL
No default schema selected; type \use to set one.
MySQL localhost:3008 ssl JS >

创建集群

准备好MySQL实例，基于单主模式的MGR之前已经部署好（上一篇文章），需要创建一个 Cluster，将MGR实例加入到该集群中。
在MySQL shell 中，通过 dba.createCluster() 方法创建集群：testcluster

MySQL  localhost:3008 ssl  JS >  dba.createCluster('testcluster')
A new InnoDB cluster will be created on instance 'localhost:3008'.

You are connected to an instance that belongs to an unmanaged replication group.
Do you want to setup an InnoDB cluster based on this replication group? [Y/n]: Y

Creating InnoDB cluster 'testcluster' on 'bjm6-184-40.com.cn:3008'...

Adding Seed Instance...
Adding Instance 'bjm6-184-40.com.cn:3008'...
Adding Instance 'bjm6-196-59.com.cn:3009'...
Adding Instance 'bjm6-196-85.com.cn:3010'...
Resetting distributed recovery credentials across the cluster...
Cluster successfully created based on existing replication group.

<Cluster:testcluster>

查看集群状态

集群状态

在MySQL shell 中创建一个 Cluster 对象，方便后面进一步对集群操作

 MySQL  localhost:3008 ssl  JS > var cluster = dba.getCluster()
 MySQL  localhost:3008 ssl  JS > cluster.status()
{
    "clusterName": "testcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "bjm6-184-40.com.cn:3008",
        "ssl": "DISABLED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "bjm6-184-40.com.cn:3008": {
                "address": "bjm6-184-40.com.cn:3008",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.18"
            },
            "bjm6-196-59.com.cn:3009": {
                "address": "bjm6-196-59.com.cn:3009",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.18"
            },
            "bjm6-196-85.com.cn:3010": {
                "address": "bjm6-196-85.com.cn:3010",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.18"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "bjm6-184-40.com.cn:3008"
}

集群描述

 MySQL  localhost:3008 ssl  JS > cluster.name
testcluster
 MySQL  localhost:3008 ssl  JS > cluster.describe()
{
    "clusterName": "testcluster",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "bjm6-184-40.com.cn:3008",
                "label": "bjm6-184-40.com.cn:3008",
                "role": "HA"
            },
            {
                "address": "bjm6-196-59.com.cn:3009",
                "label": "bjm6-196-59.com.cn:3009",
                "role": "HA"
            },
            {
                "address": "bjm6-196-85.com.cn:3010",
                "label": "bjm6-196-85.com.cn:3010",
                "role": "HA"
            }
        ],
        "topologyMode": "Single-Primary"
    }
}

常用方法

 MySQL  localhost:3008 ssl  JS > cluster.
addInstance()                    rejoinInstance()
checkInstanceState()             removeInstance()
describe()                       removeRouterMetadata()
disconnect()                     rescan()
dissolve()                       resetRecoveryAccountsPassword()
forceQuorumUsingPartitionOf()    setInstanceOption()
getName()                        setOption()
help()                           setPrimaryInstance()
listRouters()                    status()
name                             switchToMultiPrimaryMode()
options()                        switchToSinglePrimaryMode()

生成集群元数据

当创建好集群后，会生成元数据库

mysql> show tables;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| async_cluster_members                   |
| async_cluster_views                     |
| clusters                                |
| instances                               |
| router_rest_accounts                    |
| routers                                 |
| schema_version                          |
| v2_ar_clusters                          |
| v2_ar_members                           |
| v2_clusters                             |
| v2_gr_clusters                          |
| v2_instances                            |
| v2_router_rest_accounts                 |
| v2_routers                              |
| v2_this_instance                        |
+-----------------------------------------+
15 rows in set (0.01 sec)

mysqlrouter

用户权限

指定任意一个实例，根据集群元数据自动生成 router 的相关配置；所以用于启动MySQLrouter的账号（可以是超级账号，也可以是有权限的普通账号）必须对源数据库有权限；

1	GRANT SELECT, EXECUTE ON mysql_innodb_cluster_metadata.* TO 'mysql_router1_3crooltaqbd0'@'%'": Access denied for user 'root'@'192.168.184.40' to database 'mysql_innodb_cluster_metadata'

启动命令

指定 router 的安装目录；指定集群中的任意一个实例；

[root(cuigenlu)@bjm6-184-40 bin]# ./mysqlrouter --bootstrap localhost:3008 --directory /work/myrouter --force --user root
Please enter MySQL password for root:
# Bootstrapping MySQL Router instance at '/work/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 /work/myrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'testcluster'

After this MySQL Router has been started with the generated configuration

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

the 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:64460
- Read/Only Connections:  localhost:64470

生成目录文件

/work/myrouter/
├── data
│ ├── keyring
│ └── state.json
├── log
│ └── mysqlrouter.log
├── mysqlrouter.conf
├── mysqlrouter.key
├── run
├── start.sh
└── stop.sh

查看配置

cat  mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
user=root
logging_folder=/work/myrouter/log
runtime_folder=/work/myrouter/run
data_folder=/work/myrouter/data
keyring_path=/work/myrouter/data/keyring
master_key_path=/work/myrouter/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/work/myrouter/data/state.json

[logger]
level = INFO

[metadata_cache:testcluster]
cluster_type=gr
router_id=1
user=mysql_router1_mqehrdp7cykn
metadata_cluster=testcluster
ttl=0.5
use_gr_notifications=0

[routing:testcluster_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://testcluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:testcluster_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://testcluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:testcluster_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://testcluster/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:testcluster_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://testcluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

遇到问题

P01

[root(cuigenlu)@bjm6-184-40 bin]# ./mysqlrouter --bootstrap 192.168.184.40:3008 --directory /work/myrouter --user root
Please enter MySQL password for root:
# Bootstrapping MySQL Router instance at '/work/myrouter'...

- Creating account(s) (only those that are needed, if any)
FATAL ERROR ENCOUNTERED, attempting to undo new accounts that were created
- New accounts cleaned up successfully
Error: Error creating MySQL account for router (GRANTs stage): Error executing MySQL query "GRANT SELECT, EXECUTE ON mysql_innodb_cluster_metadata.* TO 'mysql_router1_3crooltaqbd0'@'%'": Access denied for user 'root'@'192.168.184.40' to database 'mysql_innodb_cluster_metadata' (1044)

解决：报错是因为，启动router时，账号没有对元数据的访问权限，先赋权，再启动router

P02

[root(cuigenlu)@bjm6-184-40 bin]# ./mysqlrouter --bootstrap localhost:3008 --directory /work/myrouter --user root
Please enter MySQL password for root:
# Bootstrapping MySQL Router instance at '/work/myrouter'...

Error: It appears that a router instance named '' has been previously configured in this host. If that instance no longer exists, use the --force option to overwrite it.
解决： 报错是因为之前重复启动过router，有异常信息  用 --force强制覆盖