由 SuKai December 7, 2024
- 创建MySQL InnoDB Cluster
- 初始化mysql router配置
- 启动mysql router
安装MySQL Shell和MySQL Router
sudo yum install percona-mysql-router percona-mysql-shell
[rocky@sukai-db04 ~]$ sudo yum install percona-mysql-router percona-mysql-shell
Last metadata expiration check: 1:56:56 ago on Thu 05 Dec 2024 06:21:11 AM UTC.
Dependencies resolved.
=======================================================================================================================================================================================================
Package Architecture Version Repository
=======================================================================================================================================================================================================
Installing:
percona-mysql-router x86_64 8.0.39-30.1.el8 ps-80-release-x86_64
percona-mysql-shell x86_64 8.0.37-1.el8 ps-80-release-x86_64
Installing dependencies:
libicu x86_64 60.3-2.el8_1 baseos
Transaction Summary
=======================================================================================================================================================================================================
Install 3 Packages
Total download size: 95 M
Installed size: 551 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): libicu-60.3-2.el8_1.x86_64.rpm
(2/3): percona-mysql-router-8.0.39-30.1.el8.x86_64.rpm
(3/3): percona-mysql-shell-8.0.37-1.el8.x86_64.rpm 21 kB/s | 81 MB 65:03
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 25 kB/s | 95 MB 65:04
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : libicu-60.3-2.el8_1.x86_64 1/3
Running scriptlet: libicu-60.3-2.el8_1.x86_64 1/3
Installing : percona-mysql-shell-8.0.37-1.el8.x86_64 2/3
Running scriptlet: percona-mysql-router-8.0.39-30.1.el8.x86_64 3/3
Installing : percona-mysql-router-8.0.39-30.1.el8.x86_64 3/3
Running scriptlet: percona-mysql-router-8.0.39-30.1.el8.x86_64 3/3
Verifying : libicu-60.3-2.el8_1.x86_64 1/3
Verifying : percona-mysql-router-8.0.39-30.1.el8.x86_64 2/3
Verifying : percona-mysql-shell-8.0.37-1.el8.x86_64 3/3
Installed:
libicu-60.3-2.el8_1.x86_64 percona-mysql-router-8.0.39-30.1.el8.x86_64 percona-mysql-shell-8.0.37-1.el8.x86_64
Complete!
创建MySQL InnoDB Cluster账户
mysql> CREATE USER IF NOT EXISTS `icadmin`@`192.168.0.%` IDENTIFIED BY '66666666666666666' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `icadmin`@`192.168.0.%` WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> GRANT BACKUP_ADMIN,CLONE_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,SYSTEM_VARIABLES_ADMIN ON *.* TO `icadmin`@`192.168.0.%` WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `icadmin`@`192.168.0.%` WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata`.* TO `icadmin`@`192.168.0.%` WITH GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_bkp`.* TO `icadmin`@`192.168.0.%` WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_previous`.* TO `icadmin`@`192.168.0.%` WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
创建MySQL InnoDB Cluster
[rocky@sukai-db03 ~]$ mysqlsh --uri icadmin@192.168.0.135:3306
Please provide the password for 'icadmin@192.168.0.135:3306': ********************
Save password for 'icadmin@192.168.0.135:3306'? [Y]es/[N]o/Ne[v]er (default No): No
MySQL Shell 8.0.37
Copyright (c) 2016, 2024, 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.
Creating a Classic session to 'icadmin@192.168.0.135:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 42923
Server version: 8.0.39-30 Percona Server (GPL), Release 30, Revision 41ebc5d9
No default schema selected; type \use <schema> to set one.
MySQL 192.168.0.135:3306 ssl JS > var cluster = dba.createCluster('sukaicluster', {adoptFromGR: true});
A new InnoDB Cluster will be created based on the existing replication group on instance 'sukai-db03:3306'.
Creating InnoDB Cluster 'sukaicluster' on 'sukai-db03:3306'...
Adding Seed Instance...
Adding Instance 'sukai-db03:3306'...
Adding Instance 'sukai-db04:3306'...
Adding Instance 'sukai-db05:3306'...
Resetting distributed recovery credentials across the cluster...
Cluster successfully created based on existing replication group.
MySQL 192.168.0.135:3306 ssl JS > sukaicluster.status()
ReferenceError: sukaicluster is not defined
MySQL 192.168.0.135:3306 ssl JS > cluster.status()
{
"clusterName": "sukaicluster",
"defaultReplicaSet": {
"name": "default",
"primary": "sukai-db03:3306",
"ssl": "DISABLED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"sukai-db03:3306": {
"address": "sukai-db03:3306",
"instanceErrors": [
"NOTE: The required parallel-appliers settings are not enabled on the instance. Use dba.configureInstance() to fix it."
],
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.39"
},
"sukai-db04:3306": {
"address": "sukai-db04:3306",
"instanceErrors": [
"NOTE: The required parallel-appliers settings are not enabled on the instance. Use dba.configureInstance() to fix it."
],
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.39"
},
"sukai-db05:3306": {
"address": "sukai-db05:3306",
"instanceErrors": [
"NOTE: The required parallel-appliers settings are not enabled on the instance. Use dba.configureInstance() to fix it."
],
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.39"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "sukai-db03:3306"
}
MySQL 192.168.0.135:3306 ssl JS > \q
Bye!
初始化MySQL Router
[rocky@sukai-db03 ~]$ sudo mysqlrouter --bootstrap icadmin@192.168.0.135 --directory /data/mysqlrouter --conf-use-sockets --user=mysqlrouter --name=mysql_router_sukai --conf-bind-address=192.168.0.135 --account-host="192.168.0.%"
Please enter MySQL password for icadmin:
# Bootstrapping MySQL Router 8.0.39 (Percona Server (GPL), Release 30, Revision 41ebc5d9) instance at '/data/mysqlrouter'...
- Creating account(s) (only those that are needed, if any)
Failed changing the authentication plugin for account 'mysql_router1_lommlu2'@'192.168.0.%': mysql_native_password which is deprecated is the default authentication plugin on this server.
- 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/mysqlrouter/mysqlrouter.conf
# MySQL Router 'mysql_router_sukai' configured for the InnoDB Cluster 'sukaicluster'
After this MySQL Router has been started with the generated configuration
$ mysqlrouter -c /data/mysqlrouter/mysqlrouter.conf
InnoDB Cluster 'sukaicluster' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446, /data/mysqlrouter/mysql.sock
- Read/Only Connections: localhost:6447, /data/mysqlrouter/mysqlro.sock
## MySQL X protocol
- Read/Write Connections: localhost:6448, /data/mysqlrouter/mysqlx.sock
- Read/Only Connections: localhost:6449, /data/mysqlrouter/mysqlxro.sock
[rocky@sukai-db03 ~]$
[rocky@sukai-db03 mysqlrouter]$ sudo cp mysqlrouter.conf /etc/mysqlrouter/
[rocky@sukai-db03 mysqlrouter]$ sudo systemctl start mysqlrouter
[rocky@sukai-db03 mysqlrouter]$ sudo more log/mysqlrouter.log
2024-12-05 12:53:12 main SYSTEM [7f5037499dc0] Starting 'MySQL Router', version: 8.0.39 (Percona Server (GPL), Release 30, Revision 41ebc5d9)
2024-12-05 12:53:12 io INFO [7f5037499dc0] starting 8 io-threads, using backend 'linux_epoll'
2024-12-05 12:53:12 http_server INFO [7f5037499dc0] listening on 0.0.0.0:8443
2024-12-05 12:53:12 metadata_cache_plugin INFO [7f502a260700] Starting Metadata Cache
2024-12-05 12:53:12 metadata_cache INFO [7f502a260700] Connections using ssl_mode 'PREFERRED'
2024-12-05 12:53:12 metadata_cache INFO [7f502825c700] Starting metadata cache refresh thread
2024-12-05 12:53:12 routing INFO [7f4fe3fff700] [routing:bootstrap_ro] started: routing strategy = round-robin-with-fallback
2024-12-05 12:53:12 routing INFO [7f4fe3fff700] Start accepting connections for routing routing:bootstrap_ro listening on '192.168.0.135:6447'
2024-12-05 12:53:12 routing INFO [7f4fe3fff700] Start accepting connections for routing routing:bootstrap_ro listening on '/data/mysqlrouter/mysqlro.sock'
2024-12-05 12:53:12 routing INFO [7f50027fc700] [routing:bootstrap_x_ro] started: routing strategy = round-robin-with-fallback
2024-12-05 12:53:12 routing INFO [7f50027fc700] Start accepting connections for routing routing:bootstrap_x_ro listening on '192.168.0.135:6449'
2024-12-05 12:53:12 routing INFO [7f5001ffb700] [routing:bootstrap_x_rw] started: routing strategy = first-available
2024-12-05 12:53:12 routing INFO [7f5001ffb700] Start accepting connections for routing routing:bootstrap_x_rw listening on '192.168.0.135:6448'
2024-12-05 12:53:12 routing INFO [7f50027fc700] Start accepting connections for routing routing:bootstrap_x_ro listening on '/data/mysqlrouter/mysqlxro.sock'
2024-12-05 12:53:12 routing INFO [7f5001ffb700] Start accepting connections for routing routing:bootstrap_x_rw listening on '/data/mysqlrouter/mysqlx.sock'
2024-12-05 12:53:12 routing INFO [7f50037fe700] [routing:bootstrap_rw] started: routing strategy = first-available
2024-12-05 12:53:12 routing INFO [7f50037fe700] Start accepting connections for routing routing:bootstrap_rw listening on '192.168.0.135:6446'
2024-12-05 12:53:12 routing INFO [7f50037fe700] Start accepting connections for routing routing:bootstrap_rw listening on '/data/mysqlrouter/mysql.sock'
2024-12-05 12:53:12 metadata_cache INFO [7f502825c700] Connected with metadata server running on sukai-db03:3306
2024-12-05 12:53:12 metadata_cache INFO [7f502825c700] Potential changes detected in cluster after metadata refresh (view_id=0)
2024-12-05 12:53:12 metadata_cache INFO [7f502825c700] Metadata for cluster 'sukaicluster' has 3 member(s), single-primary:
2024-12-05 12:53:12 metadata_cache INFO [7f502825c700] sukai-db03:3306 / 33060 - mode=RW
2024-12-05 12:53:12 metadata_cache INFO [7f502825c700] sukai-db04:3306 / 33060 - mode=RO
2024-12-05 12:53:12 metadata_cache INFO [7f502825c700] sukai-db05:3306 / 33060 - mode=RO
[rocky@sukai-db03 mysqlrouter]$
MySQL Router配置文件
[rocky@sukai-db03 ~]$ sudo more /etc/mysqlrouter/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
name=mysql_router_sukai
user=mysqlrouter
logging_folder=/data/mysqlrouter/log
runtime_folder=/data/mysqlrouter/run
data_folder=/data/mysqlrouter/data
keyring_path=/data/mysqlrouter/data/keyring
master_key_path=/data/mysqlrouter/mysqlrouter.key
connect_timeout=5
read_timeout=30
dynamic_state=/data/mysqlrouter/data/state.json
client_ssl_cert=/data/mysqlrouter/data/router-cert.pem
client_ssl_key=/data/mysqlrouter/data/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED
unknown_config_option=error
[logger]
level=INFO
[metadata_cache:bootstrap]
cluster_type=gr
router_id=1
user=mysql_router1_lommlu2
metadata_cluster=sukaicluster
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0
[routing:bootstrap_rw]
bind_address=192.168.0.135
bind_port=6446
socket=/data/mysqlrouter/mysql.sock
destinations=metadata-cache://sukaicluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic
[routing:bootstrap_ro]
bind_address=192.168.0.135
bind_port=6447
socket=/data/mysqlrouter/mysqlro.sock
destinations=metadata-cache://sukaicluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic
[routing:bootstrap_x_rw]
bind_address=192.168.0.135
bind_port=6448
socket=/data/mysqlrouter/mysqlx.sock
destinations=metadata-cache://sukaicluster/?role=PRIMARY
routing_strategy=first-available
protocol=x
[routing:bootstrap_x_ro]
bind_address=192.168.0.135
bind_port=6449
socket=/data/mysqlrouter/mysqlxro.sock
destinations=metadata-cache://sukaicluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x
[http_server]
port=8443
ssl=1
ssl_cert=/data/mysqlrouter/data/router-cert.pem
ssl_key=/data/mysqlrouter/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
[rocky@sukai-db03 ~]$