Safe Shutdown and Startup of NDB Cluster
To shut down the cluster, enter the following command in a shell on one of the machine hosting the management node:
$ ndb_mgm -e shutdown
Connected to Management Server at: 192.168.100.11:1186
4 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.
The command causes the ndb_mgm, ndb_mgmd, and ndbd processes to terminate gracefully. Shutdown the MySQL server on each of the SQL nodes.
$ sudo systemctl stop mysql
To start the cluster, first start the ndb_mgmd on both the Management nodes.
$ sudo systemctl start ndb_mgmd
Next start the ndbd on all Data nodes
$ sudo systemctl start ndbd
Finaly start the MySQL nodes on SQL servers
$ sudo systemctl start mysql
Verify all nodes are up and running.
$ ndb_mgm -e show Connected to Management Server at: 192.168.100.11:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=3 @192.168.100.12 (mysql-5.7.27 ndb-7.6.11, Nodegroup: 0, *) id=4 @192.168.100.13 (mysql-5.7.27 ndb-7.6.11, Nodegroup: 0) [ndb_mgmd(MGM)] 2 node(s) id=1 @192.168.100.11 (mysql-5.7.27 ndb-7.6.11) id=2 @192.168.100.10 (mysql-5.7.27 ndb-7.6.11) [mysqld(API)] 2 node(s) id=5 @192.168.100.11 (mysql-5.7.27 ndb-7.6.11) id=6 @192.168.100.10 (mysql-5.7.27 ndb-7.6.11)
Rolling Restart of NDB Cluster
A Rolling Restart is required in following scenarios.
Configuration change: To make a change in the cluster’s configuration, such as adding an SQL node to the cluster, or setting a configuration parameter to a new value.
NDB Cluster software upgrade: To upgrade the cluster to a newer version of the NDB Cluster software
Change on node host: To make changes in the hardware or operating system on which one or more NDB Cluster node processes are running.
Follow the step wise process to do a Rolling Restart.
Stop all NDB Cluster ndb_mgmd processes on both Management nodes.
$ sudo systemctl stop ndb_mgmd
Update config.ini files on both Management nodes.
Start a single ndb_mgmd with –initial on one Management node.
$ sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial
Start the second ndb_mgmd with the –initial option.
$ sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial
Do the rolling restarts of the data nodes by using ndb_mgm client on any of the Management node.
$ ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: 192.168.100.11:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=3 @192.168.100.12 (mysql-5.7.27 ndb-7.6.11, Nodegroup: 0, *) id=4 @192.168.100.13 (mysql-5.7.27 ndb-7.6.11, Nodegroup: 0) [ndb_mgmd(MGM)] 2 node(s) id=1 @192.168.100.11 (mysql-5.7.27 ndb-7.6.11) id=2 @192.168.100.10 (mysql-5.7.27 ndb-7.6.11) [mysqld(API)] 2 node(s) id=5 @192.168.100.11 (mysql-5.7.27 ndb-7.6.11) id=6 @192.168.100.10 (mysql-5.7.27 ndb-7.6.11) ndb_mgm> 3 restart Node 3: Node shutdown initiated Node 3: Node shutdown completed, restarting, no start. Node 3 is being restarted ndb_mgm> Node 3: Start initiated (version 7.6.11) Node 3: Started (version 7.6.11) ndb_mgm> 4 restart Node 4: Node shutdown initiated Node 4: Node shutdown completed, restarting, no start. Node 4 is being restarted ndb_mgm> Node 4: Start initiated (version 7.6.11) Node 4: Started (version 7.6.11) ndb_mgm> quit
Restart the mysql daemon on the MySQL nodes.
$ sudo systemctl restart mysql
Measuring Performance of the Database Cluster
The performance of the Database cluster can be tested using mysqlslap tool which can generate traffic and provide benchmark results.
Create a create.sql file:
CREATE TABLE sub_name (sub_id INT NOT NULL PRIMARY KEY, name VARCHAR(30)) engine=ndb; CREATE TABLE sub_age (sub_id INT NOT NULL PRIMARY KEY, age INT) engine=ndb; INSERT INTO sub_name VALUES (1,'Bill'),(2,'Fred'),(3,'Bill'),(4,'Jane'),(5,'Andrew'),(6,'Anne'),(7,'Juliette'),(8,'Awen'),(9,'Leo'),(10,'Bill'); INSERT INTO sub_age VALUES (1,40),(2,23),(3,33),(4,19),(5,21),(6,50),(7,31),(8,65),(9,18),(10,101);
Create a query.sql file:
SELECT sub_age.age FROM sub_name, sub_age WHERE sub_name.name='Bill' AND sub_name.sub_id=sub_age.sub_id;
Run the benchmark test:
$ mysqlslap -u root -p --concurrency=5 --iterations=100 --query=query.sql --create=create.sql Enter password: Benchmark Average number of seconds to run all queries: 0.033 seconds Minimum number of seconds to run all queries: 0.012 seconds Maximum number of seconds to run all queries: 0.056 seconds Number of clients running queries: 5 Average number of queries per client: 1
MYSQL NDB Cluster Backup and Restore
The Backup of the NDB Cluster databases can be done using the management client ndb_mgm. The “START BACKUP” command creates backup of the data on each of the Data node. A backup ID can be specified to properly identify when the backup was created. A suggested ID format is YYmmDDHHMM. In following example the backup was created in year 2019, 9th month, 5th day at 10:52AM. The backup files are created on each of the Data nodes in the directory /usr/local/mysql/data/BACKUP/BACKUP-1909051052/
The Backup and Restore has been done for sample employee MYSQL database https://dev.mysql.com/doc/employee/en/
Create complete database cluster backup by using the ndb_mgm client:
$ ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> START BACKUP 1909051052 Connected to Management Server at: 192.168.100.11:1186 Waiting for completed, this may take several minutes Node 3: Backup 1909051052 started from node 1 Node 3: Backup 1909051052 started from node 1 completed StartGCP: 10018 StopGCP: 10021 #Records: 3921096 #LogRecords: 0 Data: 116150016 bytes Log: 0 bytes ndb_mgm> quit
To do the restore the database cluster must be empty. The restore is done in three steps:
1. Restore the schema.
2. Restore the data with indexes disabled.
3. Rebuild the indexes.Copy the backup files from the Data nodes to one of the MySQL nodes and place them in a directory /var/mysql/BACKUPS. Stop the MySQL server on the node.
$ sudo systemctl stop mysql
First restore the schema using node ID of one of the Data nodes.
$ ndb_restore -c 192.168.100.11:1186 -n 3 -b 1909051052 \ --backup_path=/var/mysql/BACKUPS --include-databases=employees \ --restore_meta --disable-indexes Nodeid = 3 Backup Id = 1909051052 backup path = /var/mysql/BACKUPS Including Databases: employees 2019-09-05 11:10:43 [restore_metadata] Read meta data file header Opening file '/var/mysql/BACKUPS/BACKUP-1909051052.3.ctl' File size 40984 bytes ...... ...... Successfully restored table event REPL$employees/employees Successfully restored table `employees/def/salaries` Successfully restored table event REPL$employees/salaries 2019-09-05 11:10:45 [restore_metadata] Save foreign key info Save FK 15/16/FK_11_31 Save FK 15/13/FK_11_18 Save FK 12/13/FK_13_19 Save FK 15/10/FK_11_28 Save FK 15/14/FK_11_23 Save FK 12/14/FK_13_24 2019-09-05 11:10:45 [restore_data] Start restoring table data
Second perform restore of data for both of the Data nodes with indexes disabled.
$ ndb_restore -c 192.168.100.11:1186 -n 3 -b 1909051052 \ --backup_path=/var/mysql/BACKUPS --include-databases=employees \ --restore_data --disable-indexes Nodeid = 3 Backup Id = 1909051052 backup path = /var/mysql/BACKUPS Including Databases: employees 2019-09-05 11:11:34 [restore_metadata] Read meta data file header Opening file '/var/mysql/BACKUPS/BACKUP-1909051052.3.ctl' File size 40984 bytes Backup version in files: ndb-6.3.11 ndb version: mysql-5.7.27 ndb-7.6.11 2019-09-05 11:11:34 [restore_metadata] Load content ....... ....... _____________________________________________________ Processing data in table: employees/def/dept_manager(13) fragment 0 _____________________________________________________ Processing data in table: employees/def/dept_emp(14) fragment 0 _____________________________________________________ Processing data in table: sys/def/SYSTAB_0(2) fragment 0 _____________________________________________________ Processing data in table: employees/def/employees(15) fragment 0 _____________________________________________________ Processing data in table: employees/def/salaries(16) fragment 0 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(7) fragment 0 2019-09-05 11:12:17 [restore_log] Read log file header Opening file '/var/mysql/BACKUPS/BACKUP-1909051052.3.log' File size 52 bytes 2019-09-05 11:12:17 [restore_log] Restore log entries Restored 1958776 tuples and 0 log entries $ ndb_restore -c 192.168.100.11:1186 -n 4 -b 1909051052 \ --backup_path=/var/mysql/BACKUPS --include-databases=employees \ --restore_data --disable-indexes Nodeid = 4 Backup Id = 1909051052 backup path = /var/mysql/BACKUPS Including Databases: employees 2019-09-05 11:13:01 [restore_metadata] Read meta data file header Opening file '/var/mysql/BACKUPS/BACKUP-1909051052.4.ctl' File size 40984 bytes Backup version in files: ndb-6.3.11 ndb version: mysql-5.7.27 ndb-7.6.11 ....... ....... Processing data in table: sys/def/SYSTAB_0(2) fragment 1 _____________________________________________________ Processing data in table: employees/def/employees(15) fragment 1 _____________________________________________________ Processing data in table: employees/def/salaries(16) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(7) fragment 1 2019-09-05 11:13:44 [restore_log] Read log file header Opening file '/var/mysql/BACKUPS/BACKUP-1909051052.4.log' File size 52 bytes 2019-09-05 11:13:44 [restore_log] Restore log entries Restored 1960239 tuples and 0 log entries
Third rebuild the indexes.
$ ndb_restore -c 192.168.100.11:1186 -n 4 -b 1909051052 \ --backup_path=/var/mysql/BACKUPS --include-databases=employees \ --rebuild-indexes Nodeid = 4 Backup Id = 1909051052 backup path = /var/mysql/BACKUPS Including Databases: employees 2019-09-05 11:16:25 [restore_metadata] Read meta data file header Opening file '/var/mysql/BACKUPS/BACKUP-1909051052.4.ctl' File size 40984 bytes ........ ........ Rebuilding index `PRIMARY` on table `dept_emp` ...OK (0s) Rebuilding index `PRIMARY` on table `employees` ...OK (0s) Rebuilding index `PRIMARY` on table `salaries` ...OK (2s) Create foreign keys Successfully created foreign key FK_11_31 parent employees.employees.PK child employees.salaries.PRIMARY Successfully created foreign key FK_11_18 parent employees.employees.PK child employees.dept_manager.PRIMARY Successfully created foreign key FK_13_19 parent employees.departments.PK child employees.dept_manager.dept_no Successfully created foreign key FK_11_28 parent employees.employees.PK child employees.titles.PRIMARY Successfully created foreign key FK_11_23 parent employees.employees.PK child employees.dept_emp.PRIMARY Successfully created foreign key FK_13_24 parent employees.departments.PK child employees.dept_emp.dept_no
References
https://mysql.wisborg.dk/2018/03/31/mysql-ndb-cluster-backups/
https://dev.mysql.com/doc/employee/en/