MySQL InnoDB Cluster Setup on Windows Platform

MySQL InnoDB Cluster Setup on Windows Platform


This is a sample tutorial to have a setup of MySQL Servers running on Windows to form a 3 Nodes InnoDB Cluster.   This tutorial includes :
- Configuring MySQL Instance
- Installing MySQL as Windows Service
- Using MySQL Shell to Configure Remote Admin User
- Using MySQL Shell to Create the InnoDB Cluster
- Bootstrapping MySQL Router Configuration
- Running MySQL Router with PowerShell
- Installing MySQL Router as Service
- Finally testing the Router Connection to RW and RO Nodes

The video is posted on : https://youtu.be/uvGur_-HhkY 

Environment
Windows Platform
MySQL Version : 8.0.16 :  MySQL Server, MySQL Shell and MySQL Router
Program Installation Path
C:\Program Files\MySQL\MySQL Server 8.0
C:\Program Files\MySQL\MySQL Shell 8.0
C:\Program Files\MySQL\MySQL Router 8.0


The tutorial can be executed on SINGLE window machine.  But ideally, it should be running on 3 physically different machines.  (For multiple machines, M1, M2 and M3 are defined in this tutorial.  But with single machine, M1, M2 and M3 - they are the same machine.)
Data Directory resides on E:\tempdata

Instance 1 : 3310 - E:\tempdata\data1
Instance 2 : 3320 - E:\tempdata\data2
Instance 3 : 3330 - E:\tempdata\data3

File System :
NTFS is used for BOTH C:\ and E:\


Note:
exFAT should not be used.  It does not have privilege setting.  MySQL Router checks for the privilege setting on startup.  exFAT is always 'everyone'.  MySQL Router cannot be started.

In order to have 3 MySQL Instances running on same machines, the port number and data directory must be different.  Please check on the configuration my1.cnf, my2.cnf and my3.cnf for details.

---

Configuration Files

my1.cnf	my2.cnf	my3.cnf
[mysqld]	[mysqld]	[mysqld]
datadir=E:/tempdata/data1	datadir=E:/tempdata/data2	datadir=E:/tempdata/data3
basedir="C:/Program Files/MySQL/MySQL Server 8.0"	basedir="C:/Program Files/MySQL/MySQL Server 8.0"	basedir="C:/Program Files/MySQL/MySQL Server 8.0"
log-error=E:/tempdata/data1/my.error	log-error=E:/tempdata/data2/my.error	log-error=E:/tempdata/data3/my.error
port=3310	port=3320	port=3330
socket=E:/tempdata/data1/my.sock	socket=E:/tempdata/data2/my.sock	socket=E:/tempdata/data3/my.sock
mysqlx-port=33100	mysqlx-port=33200	mysqlx-port=33300
mysqlx-socket=E:/tempdata/data1/myx.sock	mysqlx-socket=E:/tempdata/data2/myx.sock	mysqlx-socket=E:/tempdata/data3/myx.sock
log-bin=logbin	log-bin=logbin	log-bin=logbin
relay-log=logrelay	relay-log=logrelay	relay-log=logrelay
binlog-format=row	binlog-format=row	binlog-format=row
binlog-checksum=NONE	binlog-checksum=NONE	binlog-checksum=NONE
server-id=101	server-id=102	server-id=103
# enable gtid	# enable gtid	# enable gtid
gtid-mode=on	gtid-mode=on	gtid-mode=on
enforce-gtid-consistency=true	enforce-gtid-consistency=true	enforce-gtid-consistency=true
log-slave-updates=true	log-slave-updates=true	log-slave-updates=true
# Table based repositories	# Table based repositories	# Table based repositories
master-info-repository=TABLE	master-info-repository=TABLE	master-info-repository=TABLE
relay-log-info-repository=TABLE	relay-log-info-repository=TABLE	relay-log-info-repository=TABLE
# Extraction Algorithm	# Extraction Algorithm	# Extraction Algorithm
transaction-write-set-extraction=XXHASH64	transaction-write-set-extraction=XXHASH64	transaction-write-set-extraction=XXHASH64

---

Configuring and Initializing Database
As shown above, the my1.cnf, my2.cnf and my3.cnf files are ready.

Assuming MySQL Installation in PATH
Create Configuration File (e.g. my1.cnf, my2.cnf and my3.cnf) – sitting on M1, M2 and M3
On M1 : mysqld --defaults-file=my1.cnf --initialize-insecure
On M2 : mysqld --defaults-file=my2.cnf --initialize-insecure
On M3 : mysqld --defaults-file=my3.cnf --initialize-insecure

NOTE :
--initialize-insecure is to initialize MySQL Database with super user ‘root@localhost’ having NO PASSWORD
PLEASE CHANGE the PASSWORD for the SUPER USER ‘root@localhost’  (for security reason)



Installing MySQL as Service
The service name is named as 'mysql3310', 'mysql3320', 'mysql3330'.


Assuming MySQL Binary in PATH

M1:mysqld --install mysql3310 --defaults-file=my1.cnf 

M2:mysqld --install mysql3320 --defaults-file=my2.cnf 

M3:mysqld --install mysql3330 --defaults-file=my3.cnf 

If you have the MySQL Notifier Installed, the mysql3310/mysql3320/mysql3330 services are registered to the MySQL Notifier.








Starting up MySQL services On Windows Service, Start the Service
Or
Using COMMAND
M1: SC start mysql3310
M2: SC start mysql3320
M3: SC start mysql3330

Once the Servers are started, as good practice, change the password for the SUPER user.

The root password is EMPTY (no password) when MySQL Server is initialized with '--initialize-insecure’ option

Note : The root user can only access MySQL locally to the machine. The root@localhost is created when it is initialized. Remote access is not allowed.

M1:mysql -uroot -h127.0.0.1 -P3310
–> set sql_log_bin=0; set password=‘<your password>’  ; set sql_log_bin=1;


M2:mysql -uroot -h127.0.0.1 -P3320
–> set sql_log_bin=0; set password=‘<your password>’  ; set sql_log_bin=1;

M3:mysql -uroot -h127.0.0.1 -P3330
–> set sql_log_bin=0; set password=‘<your password>’  ; set sql_log_bin=1;

sql_log_bin has to used in order to avoid changing the GTID.  This makes all servers the same.  Otherwise, when building the InnoDB Cluster, it will report error because those servers have different content (different GTIDs).



Configuring Remote Admin User (gradmin)

Start MySQL Shell on M1
–mysqlsh

mysqlsh>dba.configureInstance(‘root:<your password>@127.0.0.1:3310’, { clusterAdmin:’gradmin’, clusterAdminPassword:’grpass’} )

Start MySQL Shell on M2
–mysqlsh

mysqlsh>dba.configureInstance(‘root:<your password>@127.0.0.1:3320’, { clusterAdmin:’gradmin’, clusterAdminPassword:’grpass’} )


Start MySQL Shell on M3
–mysqlsh

mysqlsh>dba.configureInstance(‘root:<your password>@127.0.0.1:3330’, { clusterAdmin:’gradmin’, clusterAdminPassword:’grpass’} )




Once we have the remote Admin User (e.g. gradmin), this user can login from anywhere.  Administration can login remotely.


Creating MySQL InnoDB Cluster

Consider the InnoDB Cluster has data exchange between the servers.   It is a good practice to have separate subnet for the Cluster Data Exchange instead of 127.0.0.1.     In the following Command, the subnet as "133.122.33.0/24" is purely an example for the cluster Subnet.    The machine must have the interface.  The localAddress has to be defined for each server.
/* update : the localAddress should attach to the interface that as different subnet instead of the localhost or127.0.0.1*/
You may want to change this subnet to a.b.c.d and the localAddress as a.b.c.d.

Note:  Connecting to the Node1(3310) MUST be done via IP Address no matter it is on the same machine.  DO NOT use 127.0.0.1.    Please change your ip1 (as ip address for the M1) for the following commands.

Start MySQL Shell on M1
 –mysqlsh
mysqlsh> \connect gradmin:grpass@ip1:3310
mysqlsh> dba.createCluster(‘mycluster’, {
   exitStateAction:‘ABORT_SERVER’,
   ipWhitelist:’133.122.33.10/24’,
   localAddress:’133.122.33.10:13310’,
   consistency:’BEFORE_ON_PRIMARY_FAILOVER’
   } )
mysqlsh>var xx = dba.getCluster()
mysqlsh> xx.addInstance(‘gradmin:grpass@ip2:3320’,{localAddress:’133.122.33.10:13320’})
mysqlsh> xx.addInstance(‘gradmin:grpass@ip3:3330’, {localAddress:’133.122.33.10:13330’})
mysqlsh> xx.status()


Bootstrapping MySQL Router Configuration

On application machine (not necessary to be the MySQL Server)
For testing, one of the (M1/M2/M3) is chosen.

INSTALL MySQL ROUTER if needed for any application machine.

Assuming MySQL Router in PATH, and the ROUTER configuration is to be created on E:\tempdata\config\myrouter



mysqlrouter --bootstrap gradmin:grpass@ip1:3310 --directory e:/tempdata/config/myrouter --name mycluster

cd e:\tempdata\config\myrouter\

Starting the PowerShell  (for testing)
PS > e:/tempdata/config/myrouter/start.ps1
Check the log file if necessary
e:\tempdata\config\myrouter\log\*.log




Installing MySQL Router as Window Service

CMD – Run with Administrator
Service Name is always – MySQL Router
Firstly, to remove the MySQL Router serive
mysqlrouter --remove-service


Windows Service Installation
–mysqlrouter --install-service -c “e:/tempdata/config/myrouter/mysqlrouter.conf”

Start the Windows Server 'MySQL Router'
or
in command (CMD)
SC start MySQLRouter



Testing MySQL Router Connection to RW/RO nodes

Connecting to R/W node (e.g. M1:3310)
–On M1 : mysql –uroot –h127.0.0.1 –P3310
mysql> create user demo@’%’ identified by ‘demo’;
mysql> grant all on *.* to demo@’%’;


Connecting to ROUTER port on the Machine with Router configured and started
–mysql -udemo -pdemo -h127.0.0.1 -P6446 -e “select @@hostname, @@port;”
–mysql -udemo -pdemo -h127.0.0.1 -P6447 -e “select @@hostname, @@port;”
–mysql -udemo -pdemo -h127.0.0.1 -P6447 -e “select @@hostname, @@port;”


Enjoy the testing.