架构图
参考文档
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