What is a MySQL cluster, what is a MySQL cluster, if you want to know what is a MySQL cluster, I will show you right now.
MySQL is a popular lightweight database that many applications use as a data store. As a database for small applications, it is fully competent, but if it is a large application, high performance and high availability requirements, single server deployment of MySQL is not enough. MySQL NDB Cluster provides an official clustering solution for this requirement.
What is the MySQL NDB Cluster
MySQL NDB Cluster is a highly available, highly redundant version of MySQL suitable for distributed computing environments. Document links
Pre – cluster setup
Prepare at least three servers, one for administration and two for data and SQL servers, although more would be better.
Management server MGM: 192.168.0.105 Data server NDB1:192.168.0.106 Data server NDB2:192.168.0.104 SQL server: 192.168.0.106 SQL server: 192.168.0.104
This article uses Ubuntu 20.04 as an example. All operations are available on Ubuntu.
Start deploying a cluster.
First download the MySQL NDB Cluster binary and unzip it to begin the following steps.
Deploy management server #
Update the system
apt update -y && apt upgrade -y && apt install libncurses5 -y
Copy ndb_mGM and ndb_mGMD to the admin server
SCP. / mysql cluster – the GPL – 7.6.17 – Linux – glibc2.12 – x86_64 / bin/ndb_mgm * [email protected]: / home/MGM
Copy ndb_mgm and ndb_mgmd to the /usr/local/bin folder on the admin server
cp -rfv /home/mgm/ndb_mgm* /usr/local/bin
Grant ndb_mGM and ndb_mGMD executable permissions
chmod +x /usr/local/bin/ndb_mgm*
Adding a Configuration File
mkdir /var/lib/mysql-cluster vi /var/lib/mysql-cluster/config.ini
config.ini
[ndbd default]
Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of fragment replicas DataMemory=98M # How much memory to allocate for data storage
[ndb_mgmd]
Management process options:
HostName=192.168.0.105 # Hostname or IP address of management node NodeId=1 # Node ID for this Management node DataDir=/var/lib/mysql-cluster # Directory for management node log files
[ndbd]
Options for data node “A”:
# (one [ndbd] section per data node)
Copy the codeHostName=192.168.0.104 # HostName or IP address NodeId=2 # Node ID for this data Node DataDir=/data/mysql-cluster/data # Directory for this data node’s data files
[ndbd]
Options for data node “B “:
# (one [ndbd] section per data node)
Copy the codeHostName=192.168.0.106 # HostName or IP address NodeId=3 # Node ID for this data Node DataDir=/data/mysql-cluster/data # Directory for this data node’s data files
[mysqld]
SQL node options:
HostName=192.168.0.104 # Hostname or IP address # (additional mysqld connections can be # specified for this node for various # purposes such as running ndb_restore)
[mysqld]
SQL node options:
HostName=192.168.0.106 # Hostname or IP address # (additional mysqld connections can be # specified for this node for various # purposes such as running ndb_restore)
If the firewall is enabled, the cluster management service uses port 1186 by default
ufw allow 22 ufw allow 1186 ufw enable
Initialize and start the management server
cd /usr/local/bin/ ndb_mgmd –initial –configdir=/var/lib/mysql-cluster -f /var/lib/mysql-cluster/config.ini –ndb-nodeid=1
If the following result is displayed, the management server has been started successfully
root@mgm:/usr/local/bin# ndb_mgmd –initial –configdir=/var/lib/mysql-cluster -f /var/lib/mysql-cluster/config.ini –ndb-nodeid=1 MySQL Cluster Management Server mysql-5.7.33 ndb-7.6.17
Run the ndb_mgm command to check the current cluster status
root@mgm:/usr/local/bin# ndb_mgm — NDB Cluster — Management Client — ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration
[NDBD (NDB)] 2 node(s) id=2 (not connected, can connect from 192.168.0.104) ID =3 (not connected, Has the connect the from 192.168.0.106)
[ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.105 (mysql-5.7.33ndb-7.6.17)
[mysqld(API)] 2 node(s) id=4 (not connected, can connect from 192.168.0.104) ID =5 (not connected, can connect from 192.168.0.104) Has the connect the from 192.168.0.106)
Deploy the data server
Do the following on all data servers
Update the system
apt update -y && apt upgrade -y && apt install libncurses5 -y
Enabling the Firewall
ufw allow 22 ufw allow 2202 ufw enable
Copy NDBD and NDBMTD to the data server
/mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ NDBD [email protected]:/home/ndb1 SCP . / mysql cluster – the GPL – 7.6.17 – Linux – glibc2.12 x86_64 / bin/NDBMTD [email protected]: / home/ndb1
/mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ NDBD [email protected]:/home/ndb2 SCP . / mysql cluster – the GPL – 7.6.17 – Linux – glibc2.12 x86_64 / bin/NDBMTD [email protected]: / home/ndb2
Copy NDBD and NDBMTD to the /usr/local/bin folder on the admin server
#192.168.0.106 cp -rfv /home/ndb1/ndbd /usr/local/bin cp -rfv /home/ndb1/ndbmtd /usr/local/bin
#192.168.0.104 cp -rfv /home/db2/ndbd /usr/local/bin cp -rfv /home/db2/ndb2/ndbmtd /usr/local/bin
Grant execution permission to NDBD
chmod +x /usr/local/bin/ndbd chmod +x /usr/local/bin/ndbmtd
Add the my.cnf file to /etc
vi /etc/my.cnf
My CNF file
[mysqld]
Options for mysqld process:
ndbcluster # run NDB storage engine
[mysql_cluster]
Options for NDB Cluster processes:
Ndb-connectstring =192.168.0.105 # Location of management server
The directory for storing data must be the same as that configured for the management service
mkdir -p /data/mysql-cluster/data
Start the Data Service
root@ndb1:/usr/local/bin# ndbd 2021-06-20 08:10:23 [ndbd] INFO — Angel connected to ‘192.168.0.105:1186’ 2021-06-20 08:10:23 [ndbd] INFO — Angel allocated nodeid: 3
Go back to the cluster management server and check the cluster status. You can see that the data service is successfully connected
root@mgm:/usr/local/bin# ndb_mgm — NDB Cluster — Management Client — ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration
[ndbd(NDB)] 2 node(s) id=2 (not connected, Can set today as soon as possible (can set today as soon as possible). Can set today as soon as possible (can set today as soon as possible)
[ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.105 (mysql-5.7.33ndb-7.6.17)
[mysqld(API)] 2 node(s) id=4 (not connected, can connect from 192.168.0.104) ID =5 (not connected, can connect from 192.168.0.104) Has the connect the from 192.168.0.106)
Repeat steps 4, 5, 6, and 7 on the other server (192.168.0.104). The result is displayed
root@ndb2:/usr/local/bin# ndbd 2021-06-20 08:20:10 [ndbd] INFO — Angel connected to ‘192.168.0.105:1186’ 2021-06-20 08:20:10 [ndbd] INFO — Angel allocated nodeid: 2
Return to the cluster management server and check the cluster status. You can see that all data services are connected successfully
root@mgm:/usr/local/bin# ndb_mgm — NDB Cluster — Management Client — ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration
[NDBD (NDB)] 2 node(s) id= [email protected] (mysql-5.7.33ndb-7.6.17, Nodegroup: 0, *) id=3 @192.168.0.106 (mysql-5.7.33ndb-7.6.17, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.105 (mysql-5.7.33ndb-7.6.17)
[mysqld(API)] 2 node(s) id=4 (not connected, can connect from 192.168.0.104) ID =5 (not connected, can connect from 192.168.0.104) Has the connect the from 192.168.0.106)
Under the directory /data/mysql/data you can see that the data service has generated data
root@ndb1:~# ls /data/mysql/data/ ndb_3_fs ndb_3_out.log ndb_3.pid
Deploy SQL service #
Copy MySQL to SQL server
SCP. / mysql – cluster – the GPL – 7.6.17 – Linux – glibc2.12 – x86_64. Tar. Gz [email protected]: / home/ndb2 SCP . / mysql cluster – the GPL – 7.6.17 – Linux – glibc2.12 – x86_64. Tar. Gz [email protected]: / home/ndb1
Unzip MySQL and copy it to /usr/local
Tar -zxvf mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64.tar.gz cp -rfv mysql-cluster-gPL-7.6.17-linux-glibc2.12-x86_64 /usr/local/ln -snf /usr/local/mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64 /usr/local/mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql.server export PATH=$PATH:/usr/local/mysql/bin source /etc/profile
Enabling the Firewall
ufw allow 22 ufw allow 3306 ufw enable
Create a directory for storing MySQL data
mkdir -p /data/mysql/data mkdir -p /data/mysql/run mkdir -p /var/log/mysql
Create a mysql user and create a directory
groupadd mysql useradd -r -g mysql -s /bin/false mysql chown mysql:mysql /data/mysql/data chmod 750 /data/mysql/data
chown mysql:mysql /data/mysql/run chmod 750 /data/mysql/run
chown mysql:mysql /var/log/mysql chmod 750 /var/log/mysql
Create the MySQL configuration file
mkdir -p /etc/mysql vi /etc/mysql/my.cnf
my.cnf
[mysqld]
Options for mysqld process:
ndbcluster # run NDB storage engine
pid-file = /data/mysql/run/mysqld.pid socket = /data/mysql/run/mysqld.sock datadir = /data/mysql/data
log-error = /var/log/mysql/error.log
By default we only accept connections from localhost
The bind – address = 192.168.0.106
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0
[mysql_cluster]
Options for NDB Cluster processes:
Ndb-connectstring = 192.168.0.105 # Location of management server
[client] socket = /data/mysql/run/mysqld.sock
Initialized MySQL
/usr/local/mysql/bin/mysqld –defaults-file=/etc/mysql/my.cnf –initialize –user=mysql –basedir=/usr/local/mysql
MySQL > create user root password sF#Hy,IuT6d#
root@ndb1:~# /usr/local/mysql/bin/mysqld –defaults-file=/etc/mysql/my.cnf –initialize –user=mysql –basedir=/usr/local/mysql 2021-06-20t12:23:26.874302z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details). The 2021-06-20 T12: remove. 102146 z 0 [Warning] InnoDB: New log Files created, LSN=45790 2021-06-20t12:23:27.145317z 0 [Warning] InnoDB: Creating Foreign key Constraint System tables. 2021-06-20T12:23:27.154405z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 50a15854-d1C2-11EB-9792-000c29681E23. 2021-06-20T12:23:27.155927z 0 [Warning] Gtid table is not ready to be used. table ‘mysql.gtid_executed’ cannot be opened. 2021-06-20T12:23:28.339372z 0 [Warning] CA certificate ca.pem is self signed. 2021-06-20T12:23:28.624534z 1 [Note] A temporary password is generated for root@localhost: sF#Hy,IuT6d#
Start the MySQL
/usr/local/mysql/bin/mysqld_safe –user=mysql &
Change the password of user root
mysqladmin -uroot -p’sF#Hy,IuT6d#’ password ‘123456’
Go back to the cluster management server and check the cluster status. You can see that one OF the SQL services has been connected
root@mgm:/usr/local/bin# ndb_mgm — NDB Cluster — Management Client — ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration
[NDBD (NDB)] 2 node(s) id= [email protected] (mysql-5.7.33ndb-7.6.17, Nodegroup: 0, *) id=3 @192.168.0.106 (mysql-5.7.33ndb-7.6.17, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.105 (mysql-5.7.33ndb-7.6.17)
[mysqld(API)] 2 node(s) id=4 (not connected, Can set the world free as soon as possible (can set the world free as soon as possible)
Deploy the SQL service on another server (192.168.0.104) and return to the cluster management server to check the cluster status. All SQL services are connected successfully
root@mgm:/usr/local/bin# ndb_mgm — NDB Cluster — Management Client — ndb_mgm> show Cluster Configuration
[NDBD (NDB)] 2 node(s) id= [email protected] (mysql-5.7.33ndb-7.6.17, Nodegroup: 0, *) id=3 @192.168.0.106 (mysql-5.7.33ndb-7.6.17, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.105 (mysql-5.7.33ndb-7.6.17)
[mysqld(API)] 2 node(s) id=4 @192.168.0.104 (mysql-5.7.33ndb-7.6.17) ID =5 @192.168.0.106 (mysql-5.7.33ndb-7.6.17)
After all the cluster services are deployed, let’s test whether the cluster has been successfully deployed
Create database and table on MySQL at 192.168.0.106
CREATE DATABASE wechat; CREATE TABLE wechat.user ( Column1 varchar(100) NULL, Column2 varchar(100) NULL ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Insert the data and view it
mysql> show databases; +——————–+ | Database | +——————–+ | information_schema | | mysql | | ndbinfo | | Performance_schema | | sys | | wechat | + — — — — — — — — — — — — — — — — — — — — + 6 rows in the set (0.00 SEC)
mysql> select * from wechat.user; The Empty set (0.02 SEC)
mysql> insert wechat.user (Column1, column2) value (‘1’, ‘2’); Query OK, 1 row affected (0.01sec)
mysql> select * from wechat.user; + + — — — — — — — — — — — — — — — — — — + | Column1 | Column2 | + + — — — — — — — — — — — — — — — — — — + | 1 | 2 | + + — — — — — — — — — — — — — — — — — — + 1 row in the set (0.00) sec)
The query on the other SQL server was successful
mysql> show databases; +——————–+ | Database | +——————–+ | information_schema | | mysql | | ndbinfo | | Performance_schema | | sys | | wechat | + — — — — — — — — — — — — — — — — — — — — + 6 rows in the set (0.00 SEC)
mysql> select * from wechat.user; The Empty set (0.07 SEC)
mysql> select * from wechat.user; + + — — — — — — — — — — — — — — — — — — + | Column1 | Column2 | + + — — — — — — — — — — — — — — — — — — + | 1 | 2 | + + — — — — — — — — — — — — — — — — — — + 1 row in the set (0.00) sec)
Now we turn one of the data nodes off, and in the admin server we see that NDBD has already turned one off
root@mgm:/usr/local/bin# ndb_mgm — NDB Cluster — Management Client — ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration
[NDBD (NDB)] 2 node(s) id= [email protected] (mysql-5.7.33ndb-7.6.17, Nodegroup: 0, *) can wipe out poverty = 2 (can wipe out poverty)
[ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.105 (mysql-5.7.33ndb-7.6.17)
[mysqld(API)] 2 node(s) id=4 @192.168.0.104 (mysql-5.7.33ndb-7.6.17) ID =5 @192.168.0.106 (mysql-5.7.33ndb-7.6.17)
Write a batch of data
mysql> select * from wechat.user; + + — — — — — — — — — — — — — — — — — — + | Column1 | Column2 | + + — — — — — — — — — — — — — — — — — — + | 1 | 2 | + + — — — — — — — — — — — — — — — — — — + 1 row in the set (0.01) sec)
mysql> insert into wechat.user (Column1, column2) value (‘3’, ‘4’); Query OK, 1 row affected (0.00 sec)
mysql> select * from wechat.user; +———+———+ | Column1 | Column2 | +———+———+ | 3 | 4 | | 1 | 2 | +———+———+ 2 rows in set (0.00 sec)
On another SQL server, the results are the same
mysql> select * from wechat.user; +———+———+ | Column1 | Column2 | +———+———+ | 3 | 4 | | 1 | 2 | +———+———+ 2 rows in set (0.00 sec)
Let’s turn off the 192.168.0.106 SQL service
root@mgm:/usr/local/bin# ndb_mgm — NDB Cluster — Management Client — ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration
[NDBD (NDB)] 2 node(s) id= [email protected] (mysql-5.7.33ndb-7.6.17, Nodegroup: 0, *) can wipe out poverty = 2 (can wipe out poverty)
[ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.105 (mysql-5.7.33ndb-7.6.17)
[mysqld(API)] 2 node(s) id=4 (mysql-5.7.33ndb-7.6.17) ID =5 (not connected, Has the connect the from 192.168.0.106)
Write data to SQL server at 192.168.0.104
mysql> insert into wechat.user (Column1, column2) value (‘5’, ‘6’); Query OK, 1 row affected (0.00 sec)
mysql> select * from wechat.user; +———+———+ | Column1 | Column2 | +———+———+ | 5 | 6 | | 3 | 4 | | 1 | 2 | +———+———+ 3 rows in set (0.00 sec)
Start data services and SQL services at 192.168.0.106
root@mgm:/usr/local/bin# ndb_mgm — NDB Cluster — Management Client — ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration
[NDBD (NDB)] 2 node(s) id= [email protected] (mysql-5.7.33ndb-7.6.17, Nodegroup: 0, *) id=3 @192.168.0.106 (mysql-5.7.33ndb-7.6.17, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.105 (mysql-5.7.33ndb-7.6.17)
[mysqld(API)] 2 node(s) id=4 @192.168.0.104 (mysql-5.7.33ndb-7.6.17) ID =5 @192.168.0.106 (mysql-5.7.33ndb-7.6.17)
A database query at 192.168.0.106 shows that the data generated during the fault has been synchronized
root@ndb1:~# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.33-ndb-7.6.17-cluster-gpl MySQL Cluster Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type ‘help; ‘ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select * from wechat.user; +———+———+ | Column1 | Column2 | +———+———+ | 1 | 2 | | 5 | 6 | | 3 | 4 | +———+———+ 3 rows in set (0.08 sec)
Database cluster deployment is successful, summary of cluster considerations #
When creating a table, you need to set ENGINE= ndbCluster, as shown in the table building script above.
The same user password needs to be created for each SQL service
Management servers cannot all fail; otherwise, operations on the cluster database fail.
Data servers cannot all fail; otherwise, the cluster database operation fails.
A database created during an SQL server fault will not be automatically synchronized after the database is recovered. You need to manually create a database with the same name on the server after the fault is recovered.
As long as there are more management servers and data servers, data can be written safely and the database system will not be unavailable when a fault occurs.
The more SQL servers that distribute database access requests to each SQL server through load balancing services, the more concurrency can be supported.
The cluster must be started in the following sequence: Management service > Data Service >SQL service.