You will need few installations to do accomplish this. You have to install below installations and environment variables to be set up.

01. MySql Server 5.7.17 with group replication facility
02. MySQL Shell 1.0.6 and MySQL Router 2.1.1
03. Microsoft Visual C++ 2015 Redistributable 
04. OpenSSL 1.0.2j binary

Installation process

First of all install C++ 2015 Redistributable package and then install MySQL server. When you install MySQL server just select the server only radio button in the set up process. Then install MySQL Shell and the Router. You can right click on mysql-innodb-cluster-labs201612-windows-x86-64bit.zip and extract all to see shell and router. Those shell and router also zip files and you have to extract them inside “C:\Program Files\MySQL\”  . Next installation is OpenSSL. It is a mobile installation and you just have to extract OpenSSL in to C: folder. Since developer accessibility purposes on command line we have to set environment variables as below.

  • C:\Program Files\MySQL\mysql-router-2.1.1-windows-x86-64bit\bin
  • C:\Program Files\MySQL\mysql-shell-1.0.6-labs-windows-x86-64bit\bin
  • C:\Program Files\MySQL\MySQL Server 5.7\bin
  • C:\OpenSSL

SandBox configuration for InnoDB cluster

Lets test the cluster using sandbox.

Type mysqlsh on your cmd to start MySQL shell it will enter javascript shell mode. Now we are going to create the innoDB cluster. First of all you have to create sandbox instances such as below. Use passwords as you want and better to use same for all.

mysql-js> dba.deploySandboxInstance(3310);
mysql-js> dba.deploySandboxInstance(3320);
mysql-js> dba.deploySandboxInstance(3330);

Out of these three instance you can pick one as cluster. I have used 3310.

mysql-js> shell.connect(‘root@localhost:3310’); 
var cluster = dba.createCluster(‘devCluster’);

You can check the status of cluster using below command.

mysql-js> cluster.status();

Now you should be able to see the status as ONLINE

Now you can see only one instance on the cluster and for fault tolerance handling you can add more instance like below.

mysql-js> cluster.addInstance(‘root@localhost:3320’); 
mysql-js> cluster.addInstance(‘root@localhost:3330’);

Now we are going to bootstrap mysql router with a certain node. That allocated node will perform routing options. You have to use below command for this. I have selected 3310 as the routing node.

C:\> mysqlrouter –bootstrap root@localhost:3310 -d %HOMEPATH%\mysql-router

Above command will configure the router and now you can start it as a router.

C:\> start /B mysqlrouter -c %HOMEPATH%\mysql-router\mysqlrouter.conf

Sandbox testing

Now we are going to test the innoDB cluster we have set up.
First you have to connect to MySQL server through the MySQL router.
C:\> mysqlsh root@localhost:6446 
Put the password and now you will be connected to InnoDB cluster. Now you can check the status of cluster using below commands.

mysql-js> var cluster = dba.getCluster(); 
mysql-js> cluster.status();

Get to know the which port is connected , you can move to sql console from js console.

mysql-js> \sql mysql-sql> SELECT @@port;

Now you can see the port is 3310. And now you can kill this node and check cluster is connecting to any other node since 3310 is down. First you have to exist from sql console and kill the instance using below commands.

mysql-sql> \js 
mysql-js> dba.killSandboxInstance(3310);

Now that instance is killed and we can test what is the new node port now.

mysql-js> \sql 
mysql-sql> SELECT @@port;

New port will be 3320 or another. Not 3310. Now we can understand that cluster is functioning properly.

Moreover if you check cluster status again you can see that 3310 node is missing and other two nodes are online. Now test is passed and you can re join the 3310 using below commands. Moveover for sandbox testing purpose we have consider all the nodes in same pc. But in production environments you have to cluster multiple MySQL instances run on different machines. Therefore you have to use IP address instead localhost if you need to use remote pc MySQL instances to same cluster. Below I have added the windows mysqlrouter config path and example of config file where you can manually edit the configurations and restart to router.

C:/usr/local/mysql-router/mysqlrouter.conf

mysqlrouter.conf file example is below.

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=/opt/routers/myrouter/log
runtime_folder=/opt/routers/myrouter/run
data_folder=/opt/routers/myrouter/data
keyring_path=/opt/routers/router/data/keyring
master_key_path=/opt/routers/myrouter/mysqlrouter.key
connect_timeout=30
read_timeout=30

[logger]
level = INFO

[metadata_cache:mycluster]
router_id=5
bootstrap_server_addresses=mysql://localhost:3310,mysql://localhost:3320,mysql://localhost:3330
user=mysql_router5_6owf3spq1c6n
metadata_cluster=mycluster
ttl=5

[routing:mycluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://mycluster/default?role=PRIMARY
routing_strategy=round-robin
protocol=classic

[routing:mycluster_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://mycluster/default?role=SECONDARY
routing_strategy=round-robin
protocol=classic

[routing:mycluster_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://mycluster/default?role=PRIMARY
routing_strategy=round-robin
protocol=x

[routing:mycluster_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://mycluster/default?role=SECONDARY
routing_strategy=round-robin
protocol=x

Reference URLs

https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-production-deployment.html

https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-configuration-file-locations.html

https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-configuration-file-example.html

Leave a Reply

Your email address will not be published. Required fields are marked *

WP Facebook Auto Publish Powered By : XYZScripts.com