#MySQL #InnoDB cluster is a collection of products that work together to provide a complete High Availability solution for MySQL.
A group of MySQL servers can be configured to create a cluster using MySQL Shell.
In the default single-primary mode, the cluster of servers has a single read-write primary.
Multiple secondary servers are replicas of the primary.
Creating a cluster with at least three servers ensures a high availability cluster.
A client application is connected to the primary via MySQL Router.
If the primary fails, a secondary is automatically promoted to the role of primary,
and MySQL Router routes requests to the new primary.
Advanced users can also configure a cluster to have multiple-primaries.
On my previous post i already created my group replication, but to maximize the use feature of mysql router, we need a shell.
+--------------------------------------+----------------+-------------+--------------+-------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_TYPE | +--------------------------------------+----------------+-------------+--------------+-------------+ | a1c5e25e-2715-11e7-bbe4-0800273fb9a2 | 192.168.56.103 | 3306 | ONLINE | PRIMARY | | a1e3e0e0-2715-11e7-b2cd-0800274048c8 | 192.168.56.101 | 3306 | ONLINE | SECONDARY | | a293fead-2715-11e7-bdf9-080027d1779e | 192.168.56.102 | 3306 | ONLINE | SECONDARY | +--------------------------------------+----------------+-------------+--------------+-------------+ 3 rows in set (0.00 sec) mysql> exit Bye [admin@lnxgrprouter ~]$
Let’s install both MySQL Router and Shell
[admin@lnxgrprouter tmp]$ sudo rpm -ivh mysql-shell-commercial-1.0.9-1.1.el7.x86_64.rpm warning: mysql-shell-commercial-1.0.9-1.1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-shell-commercial-1.0.9-1.1.################################# [100%] [admin@lnxgrprouter tmp]$ [admin@lnxgrprouter tmp]$ sudo rpm -ivh mysql-shell-commercial-1.0.9-1.1.el7.x86_64.rpm warning: mysql-shell-commercial-1.0.9-1.1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-shell-commercial-1.0.9-1.1.################################# [100%] [admin@lnxgrprouter tmp]$ [admin@lnxgrprouter ~]$ mysqlrouter --version MySQL Router v2.1.3 on Linux (64-bit) (Commercial) [admin@lnxgrprouter ~]$
It’s time to configure the cluster using shell.
+--------------------------------------+----------------+-------------+--------------+-------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_TYPE | +--------------------------------------+----------------+-------------+--------------+-------------+ | a1c5e25e-2715-11e7-bbe4-0800273fb9a2 | 192.168.56.103 | 3306 | ONLINE | PRIMARY | | a1e3e0e0-2715-11e7-b2cd-0800274048c8 | 192.168.56.101 | 3306 | ONLINE | SECONDARY | | a293fead-2715-11e7-bdf9-080027d1779e | 192.168.56.102 | 3306 | ONLINE | SECONDARY | +--------------------------------------+----------------+-------------+--------------+-------------+ 3 rows in set (0.00 sec) [admin@lnxgrprouter ~]$ mysqlsh --uri root@192.168.56.103:3306 Creating a Session to 'root@192.168.56.103:3306' Enter password: Classic Session successfully established. No default schema selected. Welcome to MySQL Shell 1.0.9 Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit. Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries. mysql-js> mysql-js> var cluster = dba.createCluster('enets2_cluster', {adoptFromGR: true}); A new InnoDB cluster will be created on instance 'root@192.168.56.103:3306'. Creating InnoDB cluster 'enets2_cluster' on 'root@192.168.56.103: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. mysql-js>
using mysql shell, let’s check our cluster..
mysql-js> var cluster = dba.getCluster(); mysql-js> cluster.status(); { "clusterName": "enets2_cluster", "defaultReplicaSet": { "name": "default", "primary": "192.168.56.103:3306", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "192.168.56.101:3306": { "address": "192.168.56.101:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "192.168.56.102:3306": { "address": "192.168.56.102:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "192.168.56.103:3306": { "address": "192.168.56.103:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } } } mysql-js>
if you check the databases owned by system.. you will noticed a new database.
When you bootstrap a mysqlrouter it perform a query on mysql_innodb_cluster_metadata database.
root@localhost [(none)]> show databases; +-------------------------------+ | Database | +-------------------------------+ | information_schema | | mysql | | mysql_innodb_cluster_metadata | | performance_schema | | sys | +-------------------------------+ 9 rows in set (0.00 sec) root@localhost [(none)]>
now we can configure the router..
[admin@lnxgrprouter ~]$ mysqlrouter --bootstrap 192.168.56.101:3306 --directory /tmp/myrouter --conf-use-sockets Please enter MySQL password for root: Bootstrapping MySQL Router instance at /tmp/myrouter... MySQL Router has now been configured for the InnoDB cluster 'enets2_cluster'. The following connection information can be used to connect to the cluster. Classic MySQL protocol connections to cluster 'enets2_cluster': - Read/Write Connections: localhost:6446 - Read/Write Connections: /tmp/myrouter/mysql.sock - Read/Only Connections: localhost:6447 - Read/Only Connections: /tmp/myrouter/mysqlro.sock X protocol connections to cluster 'enets2_cluster': - Read/Write Connections: localhost:64460 - Read/Write Connections: /tmp/myrouter/mysqlx.sock - Read/Only Connections: localhost:64470 - Read/Only Connections: /tmp/myrouter/mysqlxro.sock [admin@lnxgrprouter ~]$ if you query the mysq.user, you will notice a new user with prefix of router. root@localhost [(none)]> select user,host from mysql.user; +----------------------------------+----------------+ | user | host | +----------------------------------+----------------+ | dbadmin | % | | mysql_innodb_cluster_rp432230738 | % | | mysql_router1_rjgltxyx0e3s | % | | root | % | | dbadmin | 192.168.56.% | | rpl_user | 192.168.56.10% | | _gr_user | localhost | | mysql.sys | localhost | | root | localhost | +----------------------------------+----------------+ 9 rows in set (0.00 sec)
Start the router.
shell> cd /tmp/myrouter [admin@lnxgrprouter myrouter]$ ./start.sh [admin@lnxgrprouter myrouter]$ PID 7588 written to /tmp/myrouter/mysqlrouter.pid [admin@lnxgrprouter myrouter]$
on the router log file..
2017-04-22 11:46:17 INFO [7f2d54953700] [routing:enets2_cluster_default_ro] started: listening on 0.0.0.0:6447; read-only 2017-04-22 11:46:17 WARNING [7f2d54953700] Socket file /tmp/myrouter/mysqlro.sock already exists, but seems to be unused. Deleting and retrying... 2017-04-22 11:46:17 INFO [7f2d54953700] [routing:enets2_cluster_default_ro] started: listening using /tmp/myrouter/mysqlro.sock; read-only 2017-04-22 11:46:17 INFO [7f2d54152700] [routing:enets2_cluster_default_rw] started: listening on 0.0.0.0:my; read-write 2017-04-22 11:46:17 WARNING [7f2d54152700] Socket file /tmp/myrouter/mysql.sock already exists, but seems to be unused. Deleting and retrying... 2017-04-22 11:46:17 INFO [7f2d54152700] [routing:enets2_cluster_default_rw] started: listening using /tmp/myrouter/mysql.sock; read-write 2017-04-22 11:46:17 INFO [7f2d53951700] [routing:enets2_cluster_default_x_ro] started: listening on 0.0.0.0:64470; read-only 2017-04-22 11:46:17 WARNING [7f2d53951700] Socket file /tmp/myrouter/mysqlxro.sock already exists, but seems to be unused. Deleting and retrying... 2017-04-22 11:46:17 INFO [7f2d53951700] [routing:enets2_cluster_default_x_ro] started: listening using /tmp/myrouter/mysqlxro.sock; read-only 2017-04-22 11:46:17 INFO [7f2d53150700] [routing:enets2_cluster_default_x_rw] started: listening on 0.0.0.0:64460; read-write 2017-04-22 11:46:17 WARNING [7f2d53150700] Socket file /tmp/myrouter/mysqlx.sock already exists, but seems to be unused. Deleting and retrying... 2017-04-22 11:46:17 INFO [7f2d53150700] [routing:enets2_cluster_default_x_rw] started: listening using /tmp/myrouter/mysqlx.sock; read-write 2017-04-22 11:46:17 INFO [7f2d55154700] Starting Metadata Cache 2017-04-22 11:46:17 INFO [7f2d55154700] Connections using ssl_mode 'PREFERRED' 2017-04-22 11:46:17 INFO [7f2d55154700] Connected with metadata server running on 192.168.56.103:3306 2017-04-22 11:46:17 INFO [7f2d55154700] Changes detected in cluster 'enets2_cluster' after metadata refresh 2017-04-22 11:46:17 INFO [7f2d55154700] Metadata for cluster 'enets2_cluster' has 1 replicasets: 2017-04-22 11:46:17 INFO [7f2d55154700] 'default' (3 members, single-master) 2017-04-22 11:46:17 INFO [7f2d55154700] 192.168.56.103:3306 / 33060 - role=HA mode=RO 2017-04-22 11:46:17 INFO [7f2d55154700] 192.168.56.101:3306 / 33060 - role=HA mode=RW 2017-04-22 11:46:17 INFO [7f2d55154700] 192.168.56.102:3306 / 33060 - role=HA mode=RO 2017-04-22 11:46:17 INFO [7f2d5094b700] Connected with metadata server running on 192.168.56.103:3306 [admin@lnxgrprouter log]$
basic check…
[admin@lnxgrprouter ~]$ mysql -u root -h 192.168.56.111 --port=6446 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 58 Server version: 5.7.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select @@global.server_uuid; +--------------------------------------+ | @@global.server_uuid | +--------------------------------------+ | a1e3e0e0-2715-11e7-b2cd-0800274048c8 | +--------------------------------------+ 1 row in set (0.00 sec) mysql> exit Bye [admin@lnxgrprouter ~]$ mysql -u root -h 192.168.56.111 --port=6447 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 56 Server version: 5.7.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select @@global.server_uuid; +--------------------------------------+ | @@global.server_uuid | +--------------------------------------+ | a293fead-2715-11e7-bdf9-080027d1779e | +--------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+----------------+-------------+--------------+ | group_replication_applier | a1c5e25e-2715-11e7-bbe4-0800273fb9a2 | 192.168.56.103 | 3306 | ONLINE | | group_replication_applier | a1e3e0e0-2715-11e7-b2cd-0800274048c8 | 192.168.56.101 | 3306 | ONLINE | | group_replication_applier | a293fead-2715-11e7-bdf9-080027d1779e | 192.168.56.102 | 3306 | ONLINE | +---------------------------+--------------------------------------+----------------+-------------+--------------+ 3 rows in set (0.00 sec) mysql> +--------------------------------------+----------------+-------------+--------------+-------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_TYPE | +--------------------------------------+----------------+-------------+--------------+-------------+ | a1e3e0e0-2715-11e7-b2cd-0800274048c8 | 192.168.56.101 | 3306 | ONLINE | PRIMARY | | a1c5e25e-2715-11e7-bbe4-0800273fb9a2 | 192.168.56.103 | 3306 | ONLINE | SECONDARY | | a293fead-2715-11e7-bdf9-080027d1779e | 192.168.56.102 | 3306 | ONLINE | SECONDARY | +--------------------------------------+----------------+-------------+--------------+-------------+ 3 rows in set (0.00 sec) mysql>
Lets try stopping 1 of the nodes..
mysql-js> cluster.status();
{
"clusterName": "enets2_cluster",
"defaultReplicaSet": {
"name": "default",
"primary": "192.168.56.101:3306",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
"topology": {
"192.168.56.101:3306": {
"address": "192.168.56.101:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"192.168.56.102:3306": {
"address": "192.168.56.102:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"192.168.56.103:3306": {
"address": "192.168.56.103:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
}
}
}
}
mysql-js>
Lets stop 1 more node..
mysql-js> cluster.status();
{
"clusterName": "enets2_cluster",
"defaultReplicaSet": {
"name": "default",
"primary": "192.168.56.101:3306",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 2 members are not active",
"topology": {
"192.168.56.101:3306": {
"address": "192.168.56.101:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"192.168.56.102:3306": {
"address": "192.168.56.102:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
},
"192.168.56.103:3306": {
"address": "192.168.56.103:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
}
}
}
}
mysql-js>
On our 1 remaining node, lets check if we can do anything.
dbadmin@192.168.56.111 [(none)]>
+--------------------------------------+----------------+-------------+--------------+-------------+
| MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_TYPE |
+--------------------------------------+----------------+-------------+--------------+-------------+
| a1e3e0e0-2715-11e7-b2cd-0800274048c8 | 192.168.56.101 | 3306 | ONLINE | PRIMARY |
+--------------------------------------+----------------+-------------+--------------+-------------+
1 row in set (0.00 sec)
dbadmin@192.168.56.111 [(none)]>
dbadmin@192.168.56.111 [(none)]> create database test2nodesdown;
Query OK, 1 row affected (0.01 sec)
dbadmin@192.168.56.111 [(none)]>
Now lets bring all two nodes we stop earlier.
dbadmin@192.168.56.111 [(none)]> +--------------------------------------+----------------+-------------+--------------+-------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_TYPE | +--------------------------------------+----------------+-------------+--------------+-------------+ | a1e3e0e0-2715-11e7-b2cd-0800274048c8 | 192.168.56.101 | 3306 | ONLINE | PRIMARY | | a1c5e25e-2715-11e7-bbe4-0800273fb9a2 | 192.168.56.103 | 3306 | ONLINE | SECONDARY | | a293fead-2715-11e7-bdf9-080027d1779e | 192.168.56.102 | 3306 | ONLINE | SECONDARY | +--------------------------------------+----------------+-------------+--------------+-------------+ 3 rows in set (0.00 sec) dbadmin@192.168.56.111 [(none)]> show databases; +-------------------------------+ | Database | +-------------------------------+ | information_schema | | dbadmin_446 | | mysql | | mysql_innodb_cluster_metadata | | performance_schema | | sys | | test | | test2nodesdown | | test_rw | | x1 | | x2 | +-------------------------------+ 11 rows in set (0.00 sec) dbadmin@192.168.56.111 [(none)]> exit Bye [admin@lnxmygrp02 ~]$
Finally we have a MySQL InnoDB Cluster running..
my DBA blog 