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