标签：8.0 node01 Cluster 0.0 MGR mysqlrouter MySQL 3306 root
[root@node04 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.32-1.el7.x86_64.rpm
[root@node04 ~]# yum localinstall mysql-shell-8.0.32-1.el7.x86_64.rpm -y
[root@node04 ~]# mysqlsh
MySQL JS > \connect root@node01:3306
MySQL node01:3306 ssl JS > var cluster = dba.createCluster('myCluster', {adoptFromGR: true})
A new InnoDB Cluster will be created based on the existing replication group on instance 'node01:3306'.
Creating InnoDB Cluster 'myCluster' on 'node01:3306'...
Adding Seed Instance...
Adding Instance 'node01:3306'...
Adding Instance 'node02:3306'...
Adding Instance 'node03:3306'...
Resetting distributed recovery credentials across the cluster...
Cluster successfully created based on existing replication group.
MySQL node01:3306 ssl JS > cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "node01:3306",
"ssl": "DISABLED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"node01:3306": {
"address": "node01:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
},
"node02:3306": {
"address": "node02:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
},
"node03:3306": {
"address": "node03:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "node01:3306"
}
MySQL node01:3306 ssl JS > cluster.setupRouterAccount('mysqlrouter',{password:'abcd.1234'})
Creating user mysqlrouter@%.
Account mysqlrouter@% was successfully created.
MySQL node01:3306 ssl JS > \quit
Bye!
[root@node04 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-community-8.0.32-1.el7.x86_64.rpm
[root@node04 ~]# rpm -ivh mysql-router-community-8.0.32-1.el7.x86_64.rpm
[root@node04 ~]# mysqlrouter --bootstrap root@node01:3306 --name=router01 --user=mysqlrouter --account=mysqlrouter
Please enter MySQL password for root:
# Bootstrapping system MySQL Router instance...
Please enter MySQL password for mysqlrouter:
- 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 /etc/mysqlrouter/mysqlrouter.conf
Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'
# MySQL Router configured for the InnoDB Cluster 'myCluster'
After this MySQL Router has been started with the generated configuration
$ /etc/init.d/mysqlrouter restart
or
$ systemctl start mysqlrouter
or
$ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
InnoDB Cluster 'myCluster' 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@node04 ~]# /etc/mysqlrouter/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
name=router01
user=mysqlrouter
keyring_path=/var/lib/mysqlrouter/keyring
master_key_path=/etc/mysqlrouter/mysqlrouter.key
connect_timeout=5
read_timeout=30
dynamic_state=/var/lib/mysqlrouter/state.json
client_ssl_cert=/var/lib/mysqlrouter/router-cert.pem
client_ssl_key=/var/lib/mysqlrouter/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED
...
[logger]
level=INFO
[metadata_cache:bootstrap]
cluster_type=gr
router_id=1
user=mysqlrouter
metadata_cluster=myCluster
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0
[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://myCluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic
[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://myCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic
...
[root@node04 ~]# cat /var/lib/mysqlrouter/state.json
{
"metadata-cache": {
"group-replication-id": "8d1945a5-5c74-4ba0-8240-e9d731110753",
"cluster-metadata-servers": [
"mysql://node01:3306",
"mysql://node02:3306",
"mysql://node03:3306"
]
},
"version": "1.0.0"
[root@node04 ~]# systemctl start mysqlrouter
[root@node04 ~]# systemctl status mysqlrouter
[root@node04 ~]# netstat -tunlp | grep mysqlrouter
tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 17825/mysqlrouter
tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 17825/mysqlrouter
tcp 0 0 0.0.0.0:6448 0.0.0.0:* LISTEN 17825/mysqlrouter
tcp 0 0 0.0.0.0:6449 0.0.0.0:* LISTEN 17825/mysqlrouter
tcp 0 0 0.0.0.0:8443 0.0.0.0:* LISTEN 17825/mysqlrouter
[root@node04 ~]# mysql -uroot -pabcd.1234 -P6446 -hnode04
(root@node04) > select @@hostname;
+------------+
| @@hostname |
+------------+
| node01 |
+------------+
1 row in set (0.00 sec)
[root@node01 ~]# /etc/init.d/mysql.server stop
(root@node04) > select @@hostname;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 150
Current database: *** NONE ***
+------------+
| @@hostname |
+------------+
| node02 |
+------------+
1 row in set (0.04 sec)
[root@node04 ~]# mysqlsh
MySQL JS > \connect root@node02:3306
MySQL node02:3306 ssl JS > var cluster = dba.getCluster()
MySQL node02:3306 ssl JS > cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "node02:3306",
"ssl": "DISABLED",
"status": "OK_NO_TOLERANCE_PARTIAL",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.",
"topology": {
"node01:3306": {
"address": "node01:3306",
"memberRole": "SECONDARY",
"mode": "n/a",
"readReplicas": {},
"role": "HA",
"shellConnectError": "MySQL Error 2003: Could not open connection to 'node01:3306': Can't connect to MySQL server on 'node01:3306' (111)",
"status": "(MISSING)"
},
"node02:3306": {
"address": "node02:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
},
"node03:3306": {
"address": "node03:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "node02:3306"
}
[root@node01 ~]# /etc/init.d/mysql.server start
(root@node01) > start group_replication;
Query OK, 0 rows affected (11.01 sec)
MySQL node02:3306 ssl JS > cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "node02:3306",
"ssl": "DISABLED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"node01:3306": {
"address": "node01:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
},
"node02:3306": {
"address": "node02:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
},
"node03:3306": {
"address": "node03:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.32"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "node02:3306"
}
标签：8.0,
node01,
Cluster,
0.0,
MGR,
mysqlrouter,
MySQL,
3306,
root
From： https://blog.51cto.com/dbprofessional/7239714