Wednesday, December 4, 2019

Storing keyring_encrypted_file_password settings in extra option file

MySQL configuration file (my.ini or my.cnf) is to define the variables to be used for the server start up.

In MySQL 8.0, we have Persistent Variables which is stored within the data directory as 'mysqld-auto.cnf'

In many cases, using "--defaults-file" for mysqld (or mysqld_safe on linunx) is commonly used. 


# mysqld_safe --defaults-file=/data/my.ini &

The my.ini may contain all the settings which is required.   For security reason, the password if it is stored within the same file 'my.ini' may contribute extra risk where accidentally we may send my.ini for troubleshooting. 

Scenario :  MySQL Transparent Encryption with encrypted file keyring (in MySQL Enterprise Edition).  

For Windows :  Define a New File (e.g. my-tde.ini) which includes only the TDE settings


For the my.ini file, Append a line to include the my-tde.ini.

<... all other settings...>
!include C:/ProgramData/MySQL/my-tde.ini

On Windows, if !include /path/to/extra.ini is the last line in the file, make sure that a newline is appended at the end or the line will be ignored.

Having extra option file with !include directive, it can reduce the risk by separating the settings between option files.

Friday, October 11, 2019

MySQL InnoDB Cluster Network Reliability Parameters

MySQL InnoDB Cluster is easy to configure and deploy.  This article is written to provide 3 group replication parameter settings which may contribute to the stability and reliability of the Network in MySQL InnoDB Cluster.

With MySQL 8.0, there are at least 3 most important parameters in Network Reliability Settings.

1. group_replication_member_expel_timeout
3. group_replication_unreachable_majority_timeout

"group_replication_member_expel_timeout"- default = 0
When a node is considered to be disconnected, the majority group will wait for a time period in order to reform this majority group.   By default, there is a 5 seconds + "group_replication_member_expel_timeout" as time period to wait before kicking out the node member and reform the majority group.

Outcome : When one node is expelled, the reform takes place.  This is also the time period considered to promote a PRIMARY if (the expelled node is PRIMARY node).  

Setting considerations: Setting this value too large affect the duration for new Primary promotion so that applications cannot connect to New Primary if the expelled node is Primary.

Setting this value too low (0) as 5 seconds : Considering the following situations 
a.  Network reliability 
b.  VM Backup and the VM is frozen
c.  VM migration and the VM is forzen
d.  Backup data traffic
e.  Long Message / Transaction
d.  Node is too busy 

All those situations may delay the response.  If occurrence is high, the service  interruption will be greatly impacted.  If one node is failed, another node may also be suffered for the same reason.   So this value is important to be tuned correctly according to the environment.

"group_replication_autorejoin_tries" - default = 0
This setting allows the expelled / disconnected node to rejoin the Cluster.  The default is 0.  It means it does not rejoin.   

If a node is disconnected (network interruption or VM being too busy or being forzen - and the expel timeout is passsed), it does "autorejoin" at interval of 5 minutes.  For continuously 1 hour retry, the value can be 12.  For 3 days retry, it is 12 x 24 x 3.  

The setting of 0 requires manual intervention to bring the expelled node to the cluster.     A setting of > 0 allows the cluster to be more resilience to failure.  Otherwise, a node is failed at one time without rejoin recovery, sooner or later another node failure will be disaster.

"group_replication_unreachable_majority_timeout" - default = 0
This setting is in particularly visible to Primary node operation.    Considering a Primary node is committing a transaction, it tries to get consensus ("certification process") from other node members.   If at this time the "cluster network" with primary node is failed / broken, the "COMMIT" process is not able to get consensus.   The "group_replication_unreachable_majority_timeout as 0" defines the timeout to be forever.  It hangs and waits forever.   

Setting consideration : The value is too large where the application may hang for longer period before the database returns the error status.
This value is too small where the application may also receive "error" status and the transaction is rollback.

Sunday, July 28, 2019

MySQL 8.0.17 - New Features - Clone Plugin - some reference links

The usage of this clone plugin is so much helpful to bring new MySQL instance to live.

Usage can go with New Replica (as in MySQL Replication), or we can easily create New Node in MySQL InnoDB Cluster.  or we can provision a New MySQL Instance for Testing or Troubleshooting.  or we need the New Instance with data for snapshot query.

Many useful links have been posted in the recent week(s).   

Please check the URL with the details.   This CLONE feature is the **NEW** super star add-on to the MySQL today.

Usage :

Documentation :

Thursday, July 4, 2019

Troubleshooting MySQL InnoDB Cluster GTID Inconsistency

This tutorial is showing how manual fix of GTID inconsistency but it can be VERY RISKY to mess up the InnoDB Cluster.   It purely serves as a tutorial how to troubleshoot and identify potential issues.

MySQL InnoDB Cluster being setup on Single Machine with 3 Nodes
Node 1 : Port 3306
Node 2 : Port 3316
Node 3 : Port 3326

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.