Thursday, May 9, 2019

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 : 

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:\

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

--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
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>dba.configureInstance(‘root:<your password>@’, { clusterAdmin:’gradmin’, clusterAdminPassword:’grpass’} )

Start MySQL Shell on M2

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

Start MySQL Shell on M3

mysqlsh>dba.configureInstance(‘root:<your password>@’, { 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     In the following Command, the subnet as "" 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    Please change your ip1 (as ip address for the M1) for the following commands.

Start MySQL Shell on M1
mysqlsh> \connect gradmin:grpass@ip1:3310
mysqlsh> dba.createCluster(‘mycluster’, {
   } )
mysqlsh>var xx = dba.getCluster()
mysqlsh> xx.addInstance(‘gradmin:grpass@ip2:3320’,{localAddress:’’})
mysqlsh> xx.addInstance(‘gradmin:grpass@ip3:3330’, {localAddress:’’})
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

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'
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.