You will need few installations to do accomplish this. You have to install below installations and environment variables to be set up.
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
SandBox configuration for InnoDB cluster
Lets test the cluster using sandbox.
Out of these three instance you can pick one as cluster. I have used 3310.
var cluster = dba.createCluster(‘devCluster’);
You can check the status of cluster using below command.
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.
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
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();
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.
Now that instance is killed and we can test what is the new node port now.
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.
mysqlrouter.conf file example is below.# File automatically generated during MySQL Router bootstrap
level = INFO