Not your windows upgrade
Online upgrade of MySQL NDB Cluster (1 MGMD node)
A detailed demonstration
MySQL NDB Cluster software can be upgraded without downtime, continuing to process queries and transactions throughout an upgrade.
This blog demonstrates the upgrade of the cluster from 7.6.18 version to the 8.0.25 version with all the nodes running on the same physical host(A 5.7 based NDB cluster is being upgraded to an 8.0 based NDB cluster).
Please checkout clustertesting.blogspot.com for a step by step demonstration of bringing up an NDB cluster along with mysqld servers.
The following simple cluster of 7.6.18 version is brought up to begin the demonstration.
- 1 Management server
- 4 Data node servers
- 2 Mysqld servers
Operating system: Oracle Enterprise Linux 7
Note:
- Two instances of the cluster management client are used in the demonstration. One to perform operations on the cluster for the upgrade process and another to check the status of the cluster continuously throughout the process. For more information about the management client, please checkout ndb_mgm.
- Since, the cluster is started on a single host, the — ndb-connectstring option is skipped for all the operations.
- Before starting the upgrade process, it is always recommended to have a backup of the data from the version which is upgraded from.
NDB provides it’s own tool apart from mysqldump to take backup of the data. For more information about taking a backup, please refer Online backup and Restore. - This demonstration uses the same configuration files for both lower and higher version of the cluster with options specific to the version passed through command line. However, different configuration files can be used for both management server and mysqld servers.
- This demonstration follows the order of rolling restart to upgrade the cluster i.e., Upgrade mgmd nodes → Upgrade data nodes → Upgrade API nodes. However, it is not absolutely necessary to follow the same order.
- The data nodes in the upgrade process are restarted with “ — initial” option in the demonstration. But, it is necessary only for some version combinations. For more information, checkout Upgrading and Downgrading NDB Cluster.
The configuration files used for the cluster and server are as follows:
mgmt_config.ini
[ndbd default]
NoOfReplicas = 2
[mysqld default]
[ndb_mgmd default]
[tcp default]
[ndb_mgmd]
NodeId = 1
HostName = localhost
[ndbd]
NodeId = 2
HostName = localhost
DataDir = /export/home/tmp/vavanaga/data2
[ndbd]
NodeId = 3
HostName = localhost
DataDir = /export/home/tmp/vavanaga/data3
[ndbd]
NodeId = 4
HostName = localhost
DataDir = /export/home/tmp/vavanaga/data4
[ndbd]
NodeId = 5
HostName = localhost
DataDir = /export/home/tmp/vavanaga/data5
[mysqld]
NodeId = 6
[mysqld]
NodeId = 7
my_4.cnf
# Options for mysqld process:
[MYSQLD]
ndbcluster
ndb-connectstring=localhost
# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=localhost
[client]
port=3306
socket=/tmp/mysql_4.sock
[mysqld]
user=root
port=3306
socket=/tmp/mysql_4.sock
datadir=/export/home/tmp/vavanaga/mysqld_data_4
default_authentication_plugin=mysql_native_password
ndb-extra-logging=99
log-error-verbosity=3
my_5.cnf
# Options for mysqld process:
[MYSQLD]
ndbcluster
ndb-connectstring=localhost
# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=localhost
[client]
port=3306
socket=/tmp/mysql_5.sock
[mysqld]
user=root
port=3307
socket=/tmp/mysql_5.sock
datadir=/export/home/tmp/vavanaga/mysqld_data_5
default_authentication_plugin=mysql_native_password
ndb-extra-logging=99
log-error-verbosity=3
STEP 0: Bring up a cluster of 7.6.18 version with 1 mgmd node, 4 data nodes and 2 Mysqlds
Install the binaries of 7.6.18 and 8.0.25 in their respective directories and set paths.
For more information about download and installation, please refer MySQL Community Cluster Downloads and NDB Cluster installation.
- Download/create the configuration files for management node and mysqld servers.
- According to the configuration files provided, create data directories for ndbmtd(data) nodes and mysqld servers.
Start the management node:
$BASE_DIR_7_6_18/bin/ndb_mgmd — config-file=/export/home/tmp/vavanaga/mgmt_config.ini — configdir=$BASE_DIR_7_6_18 — initial
Start the data nodes:
$BASE_DIR_7_6_18/bin/ndbmtd — initial
$BASE_DIR_7_6_18/bin/ndbmtd — initial
$BASE_DIR_7_6_18/bin/ndbmtd — initial
$BASE_DIR_7_6_18/bin/ndbmtd — initial
Initialize data directory for MySQL server #1:
$BASE_DIR_7_6_18/bin/mysqld — basedir=$BASE_DIR_7_6_18 — datadir=/export/home/tmp/vavanaga/mysqld_data_4 — initialize-insecure
Start the mysql server #1:
$BASE_DIR_7_6_18/bin/mysqld — defaults-file=/export/home/tmp/vavanaga/my_4.cnf — basedir=$BASE_DIR_7_6_18/ &
Initialize data directory for MySQL server #2:
$BASE_DIR_7_6_18/bin/mysqld — basedir=$BASE_DIR_7_6_18 — datadir=/export/home/tmp/vavanaga/mysqld_data_5 — initialize-insecure
Start the mysql server #2:
$BASE_DIR_7_6_18/bin/mysqld — defaults-file=/export/home/tmp/vavanaga/my_5.cnf — basedir=$BASE_DIR_7_6_18/ &
To show that the cluster stays online during the upgrade process, we will continuously run inserts to two NDB tables throughout, which are created now. Two NDB tables, t1 and t2 are created.
Log in to mysqld and create two NDB tables:
- t1 — A simple table with 2 integer columns and a timestamp.
- t2 — A table which contains the information of running nodes in the cluster and a timestamp.
$BASE_DIR_7_6_18/bin/mysql -u root — protocol=tcp — port=3306
The cluster is configured with 2 mysqlds to show that even if one of the mysqld servers is down during the upgrade process, there is no database service loss through SQL.
To demonstrate that, an event is created on each mysqld which inserts data into the created NDB tables every 5 seconds throughout the upgrade process uninterrupted.
- Create an event in mysqld #1(running on port 3306) on table t1 to insert two int values continuously along with current timestamp.
- Create an event in mysqld #2(running on port 3307) on table t2 to insert the versions of the cluster nodes from processes table of ndbinfo database and a timestamp.
After triggering the events, the tables will contain the following data.
After the initial setup of the cluster, with tables and events, it is recommended to take the backup of the data for safety purposes.
Initial cluster
To check the state of the cluster, the management client(ndb_mgm) is used with the show command. NDB cluster at a glance provides a detailed description about how output of the show command represents the cluster.
Step 1: Upgrade the management node
The first step in upgrading a cluster is stop the management node. Another instance of ndb_mgm could be used to stop the nodes gracefully as shown below.
Once the management node is shutdown, the former management client which was used to check the status of the cluster is disconnected until it finds it’s management server.
After stopping the management node in the lower version, start the management node with the higher version binary and configuration file.
$BASE_DIR_7_6_18/bin/ndb_mgm -e “1 STOP”
$BASE_DIR_8_0_25/bin/ndb_mgmd — config-file=/export/home/tmp/vavanaga/mgmt_config.ini — configdir=$BASE_DIR_8_0_25 — initial
If there is any change in the configuration, the management server should be started with — initial option along with the respective configuration file to reflect any changes.
Once the management server is up, the management client used to check the status of the cluster connects to the latest version of the mgmd node started.
The tables can be checked at this point to check if the inserts are continuing to be processed.
The next steps describe upgrading the data nodes one by one.
Step 2: Upgrade the data node with id 2
Using another instance of ndb_mgm, one data node of the cluster is stopped using “<Node-Id> STOP” command.
Stop the data node:
$BASE_DIR_8_0_25/bin/ndb_mgm -e “2 STOP”
Start the data node of 8.0.25 version:
$BASE_DIR_8_0_25/bin/ndbmtd — initial
Once the data node is shutdown, ndb_mgm shows that data node status as not connected with the show command.
A data node of the higher version is started following the previous step. — ndb-connectstring option should be used if the cluster has it’s nodes configured to be running on different hosts.
Once the data node in the higher version is started, the state of the cluster shows the version of the data node connected to the cluster.
The starting process involves re-synchronising data, joining transaction protocols, taking over part of the work etc. For more information about this process, please checkout MySQL Cluster start phases.
The tables can be checked at this point to check if the inserts are continuing to be processed.
Step 3: Upgrade the data node with id 3
Similar to previous step, the next data node is restarted/upgraded.
Stop the data node:
$BASE_DIR_8_0_25/bin/ndb_mgm -e “3 STOP”
Start the data node of 8.0.25 version:
$BASE_DIR_8_0_25/bin/ndbmtd — initial
The tables can be checked at this point to check if the inserts are continuing to be processed.
Step 4: Upgrade the data node with id 4
Upgrade the data node with id 4:
$BASE_DIR_8_0_25/bin/ndb_mgm -e “4 STOP”
$BASE_DIR_8_0_25/bin/ndbmtd — initial
The tables can be checked at this point to check if the inserts are continuing to be processed.
Step 5: Upgrade the data node with id 5
Upgrade the data node with id 5:
$BASE_DIR_8_0_25/bin/ndb_mgm -e “5 STOP”
$BASE_DIR_8_0_25/bin/ndbmtd — initial
The tables can be checked at this point to check if the inserts are continuing to be processed.
Step 6: Upgrade the mysqld server #1(with id 6)
The mysqld server(API node) is also restarted in a similar manner where ‘datadir’ pointing to the same data directory of the server of lower version.
For more info, please checkout Upgrading MySQL manual.
Stop the mysqld server from mysql client:
$BASE_DIR_7_6_18/bin/mysql -u root — protocol=tcp — port=3306 -e “SHUTDOWN;”
At this point, the event insert_every_5_sec created in mysqld #1(3306) is stopped. But, there is no loss in the database service, as the queries and transactions can still be processed from mysqld #2. The event, check_cluster_status will still be running and will continue to insert data into the table t2.
Start the mysqld in 8.0.25 version:
$BASE_DIR_8_0_25/bin/mysqld — defaults-file=/export/home/tmp/vavanaga/my_4.cnf — basedir=$BASE_DIR_8_0_25/ &
Once the mysqld #1 is started in it’s higher version, the event insert_every_5_sec resumes and continues inserting data into table t1.
The tables can be checked at this point to check if the inserts are continuing to be processed.
Step 7: Upgrade the mysqld server #2(with id 7)
Stop the mysqld server from mysql client:
$BASE_DIR_7_6_18/bin/mysql -u root — protocol=tcp — port=3307 -e “SHUTDOWN;”
Start the mysqld in 8.0.25 version:
$BASE_DIR_8_0_25/bin/mysqld — defaults-file=/export/home/tmp/vavanaga/my_5.cnf — basedir=$BASE_DIR_8_0_25/ &
The tables can be checked at this point to check if the inserts are continuing to be processed.
The count of rows in both the tables indicate that both the events resume inserting data once the mysqld servers are up indicating the ZERO loss in database service.
Once all the nodes including mysqld are upgraded, the status of the cluster looks something like this:
MySQL Cluster Manager automates some of the online upgrade process of NDB Cluster. For more information, please refer Upgrading cluster.
For more info about MySQL NDB cluster and all it’s supported releases/version, please checkout MySQL Cluster.
About me: Varun Nagaraju