How To : Creating an InnoDB Cluster From an Existing Group Replication Deployment

#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..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s