How to build a MySQL high-availability and high-performance cluster

What is MySQL NDB Cluster
Preliminary work for building a cluster
Start deploying the cluster
Deployment Management Server
Deploy the data server
Deploy SQL Server
All cluster services are deployed. Let's test whether the cluster is successfully deployed.
The database cluster has been deployed successfully. Let’s summarize the precautions for the cluster.

What is MySQL NDB Cluster

MySQL NDB Cluster is a highly available, highly redundant version of MySQL, suitable for distributed computing environments.
Document Links

Preliminary work for building a cluster

Prepare at least three servers, one as a management server, and two as data servers and SQL servers. Of course, more servers will 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

Start deploying the cluster

First download the MySQL NDB Cluster binary file, unzip it and start the following steps.

Deployment Management Server

Update the system

apt update -y && apt upgrade -y && apt install libncurses5 -y

Copy ndb_mgm and ndb_mgmd to the management server

scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndb_mgm* [email protected]:/home/mgm

On the management server, copy ndb_mgm and ndb_mgmd to the /usr/local/bin folder.

cp -rfv /home/mgm/ndb_mgm* /usr/local/bin

Grant executable permissions to ndb_mgm and ndb_mgmd

chmod +x /usr/local/bin/ndb_mgm*

Add a profile

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)
HostName=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)
HostName=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)

Open the firewall, 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

When the following result appears, it means that 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

We can run the ndb_mgm command again to view the status of the current cluster.

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, accepting connect from 192.168.0.104)
id=3 (not connected, accepting connect from 192.168.0.106)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)

[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from 192.168.0.104)
id=5 (not connected, accepting connect 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

Enable firewall

ufw allow 22
ufw allow 2202
ufw enable

Copy ndbd and ndbmtd to the data server

#Copy to 192.168.0.106
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbd [email protected]:/home/ndb1
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbmtd [email protected]:/home/ndb1

#Copy to 192.168.0.104
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbd [email protected]:/home/ndb2
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbmtd [email protected]:/home/ndb2

On the management server, copy ndbd and ndbmtd to the /usr/local/bin folder.

#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/ndb2/ndbd /usr/local/bin
cp -rfv /home/ndb2/ndbmtd /usr/local/bin

Grant ndbd executable permissions

chmod +x /usr/local/bin/ndbd
chmod +x /usr/local/bin/ndbmtd

Add the my.cnf file under /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

Create a directory for data storage, which must be consistent with the path configured in the management service

mkdir -p /data/mysql-cluster/data

Start 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 to check the cluster status. You can see that the data service has been 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, accepting connect from 192.168.0.104)
id=3 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)

[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from 192.168.0.104)
id=5 (not connected, accepting connect from 192.168.0.106)

Repeat steps 4, 5, 6, and 7 on another server (192.168.0.104). You can see the result.

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

Go back to the cluster management server to check the cluster status. You can see that all data services have been 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=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)
id=3 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)

[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from 192.168.0.104)
id=5 (not connected, accepting connect from 192.168.0.106)
In 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 Server

Copy MySQL to SQL Server

scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64.tar.gz [email protected]:/home/ndb2
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64.tar.gz [email protected]:/home/ndb1

Unzip MySQL and copy it to the /usr/local directory

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

Enable firewall

ufw allow 22
ufw allow 3306
ufw enable

Create a directory for MySQL data storage

mkdir -p /data/mysql/data
mkdir -p /data/mysql/run
mkdir -p /var/log/mysql

Create a mysql user and related directories

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

Initialize MySQL

/usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql

Record the root user password sF#Hy,IuT6d# generated by MySQL initialization

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).
2021-06-20T12:23:27.102146Z 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 MySQL

/usr/local/mysql/bin/mysqld_safe --user=mysql &

Change the root user password

mysqladmin -uroot -p'sF#Hy,IuT6d#' password '123456'

Go back to the cluster management server to check the cluster status. You can see that a SQL service 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=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)
id=3 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)

[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from 192.168.0.104)
id=5 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17)

Deploy SQL service on another server (192.168.0.104), return to the cluster management server to check the cluster status, and you can see that all SQL services have been successfully connected.

root@mgm:/usr/local/bin#ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)
id=3 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)

[mysqld(API)] 2 node(s)
id=4 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17)
id=5 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17)

All cluster services are deployed. Let's test whether the cluster is successfully deployed.

Create a 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 data and view mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
|ndbinfo|
| performance_schema |
|sys|
| wechat |
+--------------------+
6 rows in set (0.00 sec)

mysql> select * from wechat.user;
Empty set (0.02 sec)

mysql> insert wechat.user (Column1, column2) value ('1', '2');
Query OK, 1 row affected (0.01 sec)

mysql> select * from wechat.user;
+---------+---------+
| Column1 | Column2 |
+---------+---------+
| 1 | 2 |
+---------+---------+
1 row in set (0.00 sec)

In another SQL server query, the result is successful

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
|ndbinfo|
| performance_schema |
|sys|
| wechat |
+--------------------+
6 rows in set (0.00 sec)

mysql> select * from wechat.user;
Empty set (0.07 sec)

mysql> select * from wechat.user;
+---------+---------+
| Column1 | Column2 |
+---------+---------+
| 1 | 2 |
+---------+---------+
1 row in set (0.00 sec)

Now we shut down one of the data nodes. On the management server, we see that ndbd has shut down one.

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 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)
id=3 (not connected, accepting connect from 192.168.0.106)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)

[mysqld(API)] 2 node(s)
id=4 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17)
id=5 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17)

Write a piece of data

mysql> select * from wechat.user;
+---------+---------+
| Column1 | Column2 |
+---------+---------+
| 1 | 2 |
+---------+---------+
1 row in 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)

Querying 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)

We then shut down 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=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)
id=3 (not connected, accepting connect from 192.168.0.106)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)

[mysqld(API)] 2 node(s)
id=4 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17)
id=5 (not connected, accepting connect from 192.168.0.106)

Write a data to the SQL service 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 the data service and SQL service of 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=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)
id=3 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)

[mysqld(API)] 2 node(s)
id=4 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17)
id=5 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17)

Querying the database at 192.168.0.106, I found that the data generated during the failure period 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)

The database cluster has been deployed successfully. Let’s summarize the precautions for the cluster.

When creating a table, you need to set ENGINE=ndbcluster. For details, please refer to the table creation script above.
Each SQL service needs to create the same user password
The management servers cannot all fail; otherwise, cluster database operations fail.
All data servers cannot fail, otherwise the cluster database operation will fail.
The database created during the SQL server failure will not be automatically synchronized to the newly created database after recovery. You need to manually create a database with the same name on the server after the failure recovery, and then the data will be automatically synchronized.
The more management servers and data servers there are, the safer data writing can be guaranteed when a failure occurs, and the database system will not become unavailable.
The more SQL servers there are, the more concurrency can be supported by distributing database access requests to each SQL server through load balancing services.
The cluster must be started in the following order: Management Service -> Data Service -> SQL Service.

The above is the details of how to build a MySQL high-availability and high-performance cluster. For more information about building a MySQL high-availability and high-performance cluster, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

How to build a MySQL PXC cluster
MySQL high availability cluster deployment and failover implementation
MySQL 5.7 cluster configuration steps
Implementation of Docker deployment of MySQL cluster
Detailed steps for installing MySQL using cluster rpm
Detailed explanation of MySQL cluster: one master and multiple slaves architecture implementation
How to deploy MySQL 5.7 & 8.0 master-slave cluster using Docker
Detailed explanation of galera-cluster deployment in cluster mode of MySQL
Example of how to build a Mysql cluster with docker
MySQL Cluster Basic Deployment Tutorial

<<: Detailed explanation of Vue's list rendering

>>: Bug of Chinese input garbled characters in flex program Firefox

Have you really learned MySQL connection query?

How to build a MySQL high-availability and high-performance cluster

Table of contents

What is MySQL NDB Cluster

Preliminary work for building a cluster

Start deploying the cluster

Deployment Management Server

Deploy the data server

Deploy SQL Server

All cluster services are deployed. Let's test whether the cluster is successfully deployed.

The database cluster has been deployed successfully. Let’s summarize the precautions for the cluster.

Have you really learned MySQL connection query?

How to preview pdf file using pdfjs in vue

Detailed explanation of the difference between tags and elements in HTML

MySQL database architecture details

Bootstrap FileInput implements image upload function

A brief discussion on the use of GROUP BY and HAVING in SQL statements

Docker deploys net5 program to achieve cross-platform functions

javascript countdown prompt box

Docker: Modifying the DOCKER_OPTS parameter in /etc/default/docker does not take effect

How to implement Docker volume mounting

Recommend

HTML multimedia application: inserting flash animation and music into web pages

Mobile web screen adaptation (rem)

Detailed explanation of basic concepts of HTML

JavaScript anti-shake case study

Windows keeps remote desktop from being automatically disconnected for a long time

Implementation code for using mongodb database in Docker

Solution to 1067 when Mysql starts in Windows

Vuex implements simple shopping cart function

MySQL InnoDB tablespace encryption example detailed explanation

Solution to the problem "/bin/sh: pip: command not found" during Dockerfile build

Solve the problem of wireless and audio not working after Windows Server installation

CentOS 7 installation and configuration tutorial under VMware10

How to filter out certain libraries during mysql full backup

avue-crud implementation example of multi-level complex dynamic header

Implementing file content deduplication and intersection and difference in Linux