MySQL8分片集群搭建

架构图

参考文档

https://severalnines.com/database-blog/mysql-innodb-cluster-80-complete-deployment-walk-through-part-one
https://severalnines.com/database-blog/mysql-innodb-cluster-80-complete-operation-walk-through-part-two

MySQL InnoDB Cluster包含三个组件

• MySQL Group Replication (一组互相复制的容错集群).
• MySQL Router (连接健康节点的查询路由))
• MySQL Shell (帮助，客户端和配置工具)

环境：

• CentOS7
• MySQL8

10.1.1.10   db1
10.1.1.11   db2
10.1.1.12   db3

三台机器修改主机名和hosts文件

hostnamectl set-hostname db1
hostnamectl set-hostname db2
hostnamectl set-hostname db3

vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.1.1.10   db1
10.1.1.11   db2
10.1.1.12   db3

安装

配置MySQL数据库集群

下载mysql包

http://dev.mysql.com/downloads/cluster/
选择红帽7x86版本RPM Bundle下载

或者rpm包安装yum源

rpm -ivh https://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm

使用yum源安装mysql参考

https://www.tecmint.com/install-latest-mysql-on-rhel-centos-and-fedora/

三台innodb server安装mysql和shell管理工具

sed -i 's/gpgcheck=1/gpgcheck=0/g' /etc/yum.repos.d/mysql-community.repo
yum clean all
yum makecache
yum install -y mysql-server mysql-shell

启动三台mysql服务器

systemctl start mysqld

###获取密码

grep temporary /var/log/mysqld.log

本地登录mysql，设置密码

mysqladmin -uroot -p password tO0rtest911*#lsda

配置

创建mysql集群

三台db都运行

mysqlsh
dba.configureLocalInstance("root@localhost:3306");

完整的应答流程如下：

MySQL Shell 8.0.28

Copyright (c) 2016, 2022, 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.
 MySQL  JS > dba.configureLocalInstance("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
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

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

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

Please select an option [1]: 1
Please provide a source address filter for the account (e.g: 192.168.% or % etc) or leave empty and press Enter to cancel.
Account Host: 10.1.%

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                               | Current Value | Required Value | Note                                             |
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable                       |
| 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             | <unique ID>    | 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

Cluster admin user 'root'@'10.1.%' created.
Configuring instance...
The instance 'localhost.localdomain:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at localhost.localdomain:3306 was restarted.

开始创建集群

在10.1.1.10上使用mysql shell连接集群管理员这里的管理员名称是初始化cluster时提示的

mysqlsh
shell.connect('root@db1:3306');

应答如下：

MySQL Shell 8.0.28

Copyright (c) 2016, 2022, 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.
 MySQL  JS > shell.connect('root@10.1.1.10:3306');
Creating a session to 'root@10.1.1.10:3306'
Please provide the password for 'root@10.1.1.10:3306': *****************
Save password for 'root@10.1.1.10:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 11
Server version: 8.0.28 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
<ClassicSession:root@10.1.1.10:3306>

创建新的集群

 MySQL  10.1.1.10:3306 ssl  JS > cluster = dba.createCluster('test_innodb_cluster');

应答如下：

A new InnoDB cluster will be created on instance '10.1.1.10:3306'.

Validating instance configuration at 10.1.1.10:3306...

This instance reports its own address as localhost.localdomain:3306

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

Creating InnoDB cluster 'test_innodb_cluster' on 'localhost.localdomain: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:test_innodb_cluster>

检查集群状态

MySQL  10.1.1.10:3306 ssl  JS > cluster.status()

应答如下：

{
    "clusterName": "test_innodb_cluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "db1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "db1:3306": {
                "address": "db1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "db1:3306"
}

注意以上的两个key提示

• “status”: “OK_NO_TOLERANCE”
• “statusText”: “Cluster is NOT tolerant to any failures.”

集群最少需要三个节点

添加其他两个节点：

MySQL|db1:3306 ssl|JS> cluster.addInstance('root@db2:3306');

应答如下：

NOTE: The target instance 'db2: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 'db2: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): 
Validating instance configuration at db2:3306...

This instance reports its own address as db2:3306

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

NOTE: db2:3306 is shutting down...

* Waiting for server restart... ready 
* db2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 73.66 MB transferred in 1 min 9 sec (1.07 MB/s)

State recovery already finished for 'db2:3306'

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

同上步骤添加db3

MySQL  db1:3306 ssl  JS > cluster.addInstance('root@db3:3306');

再次查看集群状态

MySQL  db1:3306 ssl  JS > cluster.status()

输出如下：

{
    "clusterName": "test_innodb_cluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "db1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "db1:3306": {
                "address": "db1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }, 
            "db2:3306": {
                "address": "db2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }, 
            "db3:3306": {
                "address": "db3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.28"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "db1:3306"
}

配置router

hosts配置

vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.1.1.10   db1
10.1.1.11   db2
10.1.1.12   db3
10.1.1.13   router apps

配置hostname

hostnamectl set-hostname router

安装mysql router和client

rpm -ivh https://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm
sed -i 's/gpgcheck=1/gpgcheck=0/g' /etc/yum.repos.d/mysql-community.repo
yum clean all
yum makecache
yum install -y mysql-router mysql-community-client

使用mysqlrouter命令引导

mysqlrouter --bootstrap root@db1:3306 --directory myrouter --user=root

应答如下：

Please enter MySQL password for root: 
# Bootstrapping MySQL Router instance at '/root/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 /root/myrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'test_innodb_cluster'

After this MySQL Router has been started with the generated configuration

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

InnoDB Cluster 'test_innodb_cluster' 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/myrouter/mysqlrouter.conf
内容如下：

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
user=root
logging_folder=/root/myrouter/log
runtime_folder=/root/myrouter/run
data_folder=/root/myrouter/data
keyring_path=/root/myrouter/data/keyring
master_key_path=/root/myrouter/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/root/myrouter/data/state.json
client_ssl_cert=/root/myrouter/data/router-cert.pem
client_ssl_key=/root/myrouter/data/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED

[logger]
level=INFO

[metadata_cache:test_innodb_cluster]
cluster_type=gr
router_id=1
user=mysql_router1_mmyg3eaaa3ui
metadata_cluster=test_innodb_cluster
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

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

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

[routing:test_innodb_cluster_x_rw]
bind_address=0.0.0.0
bind_port=6448
destinations=metadata-cache://test_innodb_cluster/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:test_innodb_cluster_x_ro]
bind_address=0.0.0.0
bind_port=6449
destinations=metadata-cache://test_innodb_cluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

[http_server]
port=8443
ssl=1
ssl_cert=/root/myrouter/data/router-cert.pem
ssl_key=/root/myrouter/data/router-key.pem

[http_auth_realm:default_auth_realm]
backend=default_auth_backend
method=basic
name=default_realm

[rest_router]
require_realm=default_auth_realm

[rest_api]

[http_auth_backend:default_auth_backend]
backend=metadata_cache

[rest_routing]
require_realm=default_auth_realm

[rest_metadata_cache]
require_realm=default_auth_realm

使用如下命令启动myrouter

myrouter/start.sh

输出如下：

[root@router ~]# myrouter/start.sh
[root@router ~]# PID 1867 written to '/root/myrouter/mysqlrouter.pid'
stopping to log to the console. Continuing to log to filelog

检查mysqlrouter监听的端口

[root@router ~]# netstat -nltup| grep mysql
tcp        0      0 0.0.0.0:8443            0.0.0.0:*               LISTEN      1867/mysqlrouter    
tcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      1867/mysqlrouter    
tcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      1867/mysqlrouter    
tcp        0      0 0.0.0.0:6448            0.0.0.0:*               LISTEN      1867/mysqlrouter    
tcp        0      0 0.0.0.0:6449            0.0.0.0:*               LISTEN      1867/mysqlrouter    

现在就可以使用6446端口读写，6447端口只读访问了

连接集群

连接db1，创建测试数据库和用户

mysqlsh root@localhost:3306

切换到sql模式

MySQL  localhost:3306 ssl  JS > \sql
Switching to SQL mode... Commands end with ;

创建数据库

MySQL  localhost:3306 ssl  SQL > CREATE DATABASE test;
Query OK, 1 row affected (0.1867 sec)

创建数据库用户

MySQL  localhost:3306 ssl  SQL > CREATE USER test@'%' IDENTIFIED BY '#@!Test123';
Query OK, 0 rows affected (0.1945 sec)

授权用户数据库权限

MySQL  localhost:3306 ssl  SQL > GRANT ALL PRIVILEGES ON test.* TO test@'%';
Query OK, 0 rows affected (0.0040 sec)

在router服务器上安装sysbench

yum -y install epel-release
yum -y install sysbench

现在可以使用router服务器测试连接mysql集群了

mysql -utest -p -hrouter -P6446 -e 'select user(), @@hostname, @@read_only, @@super_read_only'

输出如下：

Enter password: 
+----------------+------------+-------------+-------------------+
| user()         | @@hostname | @@read_only | @@super_read_only |
+----------------+------------+-------------+-------------------+
| test@10.1.1.13 | db1        |           0 |                 0 |
+----------------+------------+-------------+-------------------+

6447只读端口连接测试

mysql -utest -p -hrouter -P6447 -e 'select user(), @@hostname, @@read_only, @@super_read_only'

输出如下：

Enter password: 
+----------------+------------+-------------+-------------------+
| user()         | @@hostname | @@read_only | @@super_read_only |
+----------------+------------+-------------+-------------------+
| test@10.1.1.13 | db2        |           1 |                 1 |
+----------------+------------+-------------+-------------------+

下边开始压力测试

由于密码验证方式兼容性问题需要现在db1上运行如下命令

mysqlsh root@localhost:3306
MySQL  localhost:3306 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
MySQL  localhost:3306 ssl  SQL > ALTER USER test IDENTIFIED WITH mysql_native_password;
Query OK, 0 rows affected (0.4040 sec)
MySQL  localhost:3306 ssl  SQL > ALTER user 'test'@'%' IDENTIFIED WITH mysql_native_password BY '#@!Test123';
Query OK, 0 rows affected (0.0054 sec)

在router服务器上连接6446端口插入20tables*100000行数据

sysbench \
/usr/share/sysbench/oltp_common.lua \
--db-driver=mysql \
--mysql-user=test \
--mysql-db=test \
--mysql-password='#@!Test123' \
--mysql-port=6446 \
--mysql-host=router \
--tables=20 \
--table-size=100000 \
prepare

在6446端口进行300秒的读写测试

/usr/share/sysbench/oltp_read_write.lua \
--report-interval=2 \
--threads=8 \
--time=300 \
--db-driver=mysql \
--mysql-host=router \
--mysql-port=6446 \
--mysql-user=test \
--mysql-db=test \
--mysql-password='#@!Test123' \
--tables=20 \
--table-size=100000 \
run

6447端口只读测试

/usr/share/sysbench/oltp_read_only.lua \
--report-interval=2 \
--threads=1 \
--time=300 \
--db-driver=mysql \
--mysql-host=router \
--mysql-port=6447 \
--mysql-user=test \
--mysql-db=test \
--mysql-password='#@!Test123' \
--tables=20 \
--table-size=100000 \
run