This post is about setting up MySQL InnoDB Cluster with 5 nodes on a sandbox deployment. Here, we focus on implementation part, the core concepts will be explained in separate posts.
Prerequisites:
- MySQL Engine
- MySQL Shell
- MySQL Router
Deploying MySQL InnoDB Cluster involves the following steps:
- Deploying MySQL Engine (Sandbox Instance)
- Creating an InnoDB Cluster
- Adding nodes to InnoDB Cluster
- Configuring MySQL Router for High Availability.
- Testing High Availability.
Deploying MySQL Engine:
If the MySQL engines are already installed on all the nodes, you can skip this step and directly move into creating an InnoDB Cluster part.
I am deploying 5 Sandbox instances (which is in-built on MySQL Shell application) on a same machine. On production system, there will be separate nodes for each MySQL Engines. Let’s begin with the deployments:
To open MySQL Shell : Start -> cmd -> Type mysqlsh (OR) Start -> MySQL Shell
To change script mode : \JS – JavaScript Mode | \PY – Python Mode | \SQL – SQL Mode
MySQL JS > dba.deploySandboxInstance(port)
deploySandboxInstance()module will deploy new Sandbox Instance on the mentioned port, let’s deploy the following 5 Sandbox instances:
dba.deploySandboxInstance (3307)
dba.deploySandboxInstance (3308)
dba.deploySandboxInstance (3309)
dba.deploySandboxInstance (3310)
dba.deploySandboxInstance (3311)
Sample Output:
MySQL JS > dba.deploySandboxInstance (3307)
A new MySQL sandbox instance will be created on this host in
C:\Users\rathish.kumar\MySQL\mysql-sandboxes\3307
Warning: Sandbox instances are only suitable for deploying and running on your local machine for testing purposes and are not accessible from external networks.
Please enter a MySQL root password for the new instance: ***
Deploying new MySQL instance…
Instance localhost: 3307 successfully deployed and started.
Use shell.connect(‘root@localhost:3307’); to connect to the instance.
MySQL JS >
To connect the deployed sandbox instance:
MySQL JS > \connect user@host:portand enter the password when prompted. (OR)
MySQL JS > shell.connect(‘user@host:port’)
Sample Output:
MySQL localhost: 3307 ssl JS > \connect root@localhost:3307
Creating a session to ‘root localhost: 3307’
Enter password: ***
Fetching schema names for auto completion… Press ^C to stop.
Closing old connection…
Your MySQL connection id is 16
Server version: 8.0.11 MySQL Community Server – GPL
No default schema selected; type \use
MySQL localhost: 3307 ssl JS > \ssl
Switching to SQL mode… Commands end with;
MySQL localhost: 3307 ssl SQL > select @@port;
+——–+
| @@port |
+——–+
| 3307 |
+——–+
1 row in set (0.0006 sec)
MySQL localhost: 3307 ssl SQL >
Creating InnoDB Cluster:
To create an InnoDB cluster, connect to seed (primary) server, which contains the original data by using above method and follow the below steps:
var cluster = dba.createCluster(‘ClusterName’)
Sample Output:
MySQL localhost:3307 ssl JS > var cluster = dba.createCluster(‘DBCluster’)
A new InnoDB cluster will be created on instance ‘root@localhost:3307’.
Validating instance at localhost:3307…Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.
This instance reports its own address as L-IS-RATHISH
Instance configuration is suitable.
Creating InnoDB cluster ‘DBCluster’ on ‘root@localhost:3307’…
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.
Adding nodes to InnoDB Cluster:
The secondary replication nodes will be added to cluster by using the addInstance() method.
mysql-js> cluster.addInstance(‘user@host:port’)
Let us add the nodes, one by one:
cluster.addInstance(‘root@localhost:3308’);
cluster.addInstance(‘root@localhost:3309’);
cluster.addInstance(‘root@localhost:3310’);
cluster.addInstance(‘root@localhost:3311’);
Sample Output:
MySQL localhost:3307 ssl JS > cluster.addInstance(‘root@localhost:3311’);
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.
Please provide the password for ‘root@localhost:3311’: ***
Adding instance to the cluster …
Validating instance at localhost:3311…
Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.
This instance reports its own address as L-IS-RATHISH
Instance configuration is suitable.
The instance ‘root@localhost:3311’ was successfully added to the cluster.
Configuring MySQL Router for High Availability:
MySQL Router routes client connections to servers in the cluster and it provides separate ports for Read and Read/Write operations.
MySQL Router takes its configuration from InnoDB Cluster’s metadata and configure itself by using –-bootstrap option. It is recommended to install MySQL Router on a separate server or can be installed on the application server.
The MySQL Router command is given below, this should be run on the server with Read/Write (R/W) role.
shell> mysqlrouter –bootstrap user@host:port
The server roles can be checked by using the status() method. Let us check the status of our cluster:
MySQL localhost:3307 ssl JS > cluster.status()
{
"clusterName": "DBCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "localhost:3307",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to 2 failures.",
"topology": {
"localhost:3307": {
"address": "localhost:3307",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"localhost:3308": {
"address": "localhost:3308",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"localhost:3309": {
"address": "localhost:3309",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"localhost:3310": {
"address": "localhost:3310",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"localhost:3311": {
"address": "localhost:3311",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
},
"groupInformationSourceMember": "mysql://root@localhost:3307"
}
MySQL localhost:3307 ssl JS >
The server root@localhost:3307 is currently assigned with R/W role. Configure MySQL Router on this server:
C:\Windows\system32>mysqlrouter –bootstrap root@localhost:3307
Please enter MySQL password for root:
Reconfiguring system MySQL Router instance…
WARNING: router_id 1 not found in metadata
MySQL Router has now been configured for the InnoDB cluster ‘DBCluster’.
The following connection information can be used to connect to the cluster.
Classic MySQL protocol connections to cluster ‘DBCluster’:
– Read/Write Connections: localhost:6446
– Read/Only Connections: localhost:6447
X protocol connections to cluster ‘DBCluster’:
– Read/Write Connections: localhost:64460
– Read/Only Connections: localhost:64470
Existing configurations backed up to ‘C:/Program Files/MySQL/MySQL Router 8.0/mysqlrouter.conf.bak’
Connecting InnoDB Cluster:
From MySQL Router configuration, we get the connection information, by default, port 6446 used for Read /Write connections and Port 6447 used for Read/Only connections. MySQL Router allows to configure custom port numbers for R/W and R/O client connections.
Let us connect to first connect to Read/Write port and then connect to Read/Only port for testing.
Read/Write Instance:
C:\Users\rathish.kumar>mysql -u root -h localhost -P6446 -p
Enter password: *
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 176
Server version: 8.0.11 MySQL Community Server – GPL
Copyright (c) 2000, 2018, 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 @@port;
+——–+
| @@port |
+——–+
| 3307 |
+——–+
1 row in set (0.00 sec)
mysql> create database ClustDB;
Query OK, 1 row affected (0.09 sec)
mysql> use ClustDB;
Database changed
mysql> create table t1 (id int auto_increment primary key);
Query OK, 0 rows affected (0.18 sec)
mysql> insert into t1 (id) values(1);
Query OK, 1 row affected (0.06 sec)
Read/Only Instance:
C:\Users\rathish.kumar>mysql -u root -h localhost -P6447 -p
Enter password: *
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 8.0.11 MySQL Community Server – GPL
Copyright (c) 2000, 2018, 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 @@port;
+——–+
| @@port |
+——–+
| 3308 |
+——–+
1 row in set (0.00 sec)
mysql> select * from ClustDB.t1;
+—-+
| id |
+—-+
| 1 |
+—-+
1 row in set (0.00 sec)
mysql> insert into ClustDB.t1 (id) values (2);
ERROR 1290 (HY000): The MySQL server is running with the –super-read-only option so it cannot execute this statement
mysql>
Testing High Availability:
We have connected to R/W and R/O instances, and it is working as expected. Now let’s test the High Availability by killing primary seed node (3307) and Read/Only instance (3308).
dba.killSandboxInstance(3307)
dba.killSandboxInstance(3308)
Sample output:
MySQL localhost:3307 ssl JS > dba.killSandboxInstance(3307);
The MySQL sandbox instance on this host in
C:\Users\rathish.kumar\MySQL\mysql-sandboxes\3307 will be killed
Killing MySQL instance…
Instance localhost:3307 successfully killed.
Now refresh run the query on the existing Read/Write and Read/Only connections and check the port:
Read/Only Instance:
mysql> select @@port;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 38
Current database: *** NONE ***
+——–+
| @@port |
+——–+
| 3310 |
+——–+
1 row in set (1.30 sec)
mysql>
This error is due to connection rerouting while we are still connected to server. This error will not occur on new connections. Let us try with Read/Write connections:
Read/Write Instance:
C:\Users\rathish.kumar>mysql -u root -h localhost -P6446 -p
Enter password: *
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.11 MySQL Community Server – GPL
Copyright (c) 2000, 2018, 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 @@port;
+——–+
| @@port |
+——–+
| 3311 |
+——–+
1 row in set (0.00 sec)
mysql>
There is no changes required from applications, the InnoDB Cluster will identify the changes and automatically configure itself and high availability achieved with the help of MySQL Router.
I suggest you to test InnoDB Cluster on lab environment and share your findings on comment section for other readers. I will be coming with other articles on working with InnoDB Cluster and Troubleshooting InnoDB Cluster. Need of any assistance on InnoDB Cluster, please share it on comment section.
via Planet MySQL
How to set up MySQL InnoDB Cluster? Part One