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.
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:port
and 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
to set one.
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.
The reasons for forwarding the communication to another end can be a firewall on the network, router, or proxy server. A firewall is a security system that comes with predetermined rules. It monitors and controls incoming and outgoing traffic on the network. https://192-168-1-1login.net/
ReplyDeleteWhere is the continuation of the article?
ReplyDeleteRead more; https://proweb365.com/website-design/