MySQL 是一款流行的轻量级数据库，很多应用都是使用它作为数据存储。作为小型应用的数据库，它完全可以胜任，但是如果是大型应用，高性能高可用的要求，单服务器部署的MySQL就不够了。MySQL NDB Cluster 为这个需求提供了一个官方的集群解决方案。

MySQL NDB Cluster 是什么

MySQL NDB Cluster 是 MySQL 的一个高可用、高冗余版本，适用于分布式计算环境。
文档链接

搭建集群的前置工作

至少准备 3 台服务器，一台作为管理服务器，两台作为数据服务器和 SQL 服务器，当然有更多的服务器会更好。 

1
2
3
4
5
管理服务器mgm：192.168.0.105
数据服务器ndb1：192.168.0.106
数据服务器ndb2：192.168.0.104
sql服务器：192.168.0.106
sql服务器：192.168.0.104

本文以 ubuntu20.04 为例，所有操作都可用于 ubuntu 系统。

开始部署集群

首先下载 MySQL NDB Cluster二进制文件，解压缩后开始下面的步骤。

部署管理服务器

  1. 更新系统
1
apt update -y && apt upgrade -y && apt install libncurses5 -y
  1. 开启防火墙，集群管理服务默认使用 1186 端口
1
2
3
ufw allow 22
ufw allow 1186
ufw enable
  1. 复制 ndb_mgm 和 ndb_mgmd 到管理服务器
1
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndb_mgm* mgm@192.168.0.105:/home/mgm
  1. 在管理服务器复制 ndb_mgm 和 ndb_mgmd 到/usr/local/bin 文件夹
1
cp -rfv /home/mgm/ndb_mgm* /usr/local/bin
  1. 赋予 ndb_mgm 和 ndb_mgmd 可执行权限
1
chmod +x /usr/local/bin/ndb_mgm*
  1. 添加配置文件
1
2
mkdir /var/lib/mysql-cluster
vi /var/lib/mysql-cluster/config.ini
  1. config.ini
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
[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)
  1. 初始化并启动管理服务器
1
2
cd /usr/local/bin/
ndb_mgmd --initial --configdir=/var/lib/mysql-cluster -f /var/lib/mysql-cluster/config.ini --ndb-nodeid=1

当出现以下结果的时候，表示管理服务器已经启动成功了 

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

我们再执行 ndb_mgm 命令，可以查看当前集群的状态 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)

部署数据服务器

在所有数据服务器上执行以下操作

  1. 更新系统
1
apt update -y && apt upgrade -y && apt install libncurses5 -y
  1. 开启防火墙
1
2
3
ufw allow 22
ufw allow 2202
ufw enable
  1. 复制 ndbd 和 ndbmtd 到数据服务器
1
2
3
4
5
6
7
#复制到192.168.0.106
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbd ndb1@192.168.0.106:/home/ndb1
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbmtd ndb1@192.168.0.106:/home/ndb1

#复制到192.168.0.104
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbd ndb2@192.168.0.104:/home/ndb2
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbmtd ndb2@192.168.0.104:/home/ndb2
  1. 在管理服务器复制 ndbd 和 ndbmtd 到/usr/local/bin 文件夹
1
2
3
4
5
6
7
#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
  1. 赋予 ndbd 可执行权限
1
2
chmod +x /usr/local/bin/ndbd
chmod +x /usr/local/bin/ndbmtd
  1. 在/etc下加入my.cnf文件
1
vi /etc/my.cnf

my.cnf文件 

1
2
3
4
5
6
7
[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
  1. 创建数据保存的目录，必须与管理服务配置的路径一致
1
mkdir -p /data/mysql-cluster/data
  1. 启动数据服务
1
2
3
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

  1. 回到集群管理服务器查看集群状态，此时可以看到数据服务已经连接成功

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    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)

  2. 在另一台服务器（192.168.0.104）重复 4、5、6、7 步骤的操作，结果可看到

    1
    2
    3
    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

  3. 回到集群管理服务器查看集群状态，此时可以看到所有数据服务已经连接成功

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    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)

  4. 在目录/data/mysql/data下面可以看到数据服务已经产生了数据

1
2
root@ndb1:~# ls /data/mysql/data/
ndb_3_fs  ndb_3_out.log  ndb_3.pid

部署 SQL 服务

  1. 复制 MySQL 到SQL服务器

    1
    2
    scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64.tar.gz ndb2@192.168.0.104:/home/ndb2
    scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64.tar.gz ndb1@192.168.0.106:/home/ndb1

  2. 解压缩 MySQL, 然后复制到/usr/local目录

1
2
3
4
5
6
7
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
  1. 开启防火墙
1
2
3
ufw allow 22
ufw allow 3306
ufw enable
  1. 创建 MySQL 数据存放的目录
1
2
3
mkdir -p /data/mysql/data
mkdir -p /data/mysql/run
mkdir -p /var/log/mysql

  1. 创建 mysql 用户，创建相关目录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    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

  2. 创建 MySQL 配置文件

    1
    2
    mkdir -p /etc/mysql
    vi /etc/mysql/my.cnf

  3. my.cnf

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    [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

  4. 初始化MySQL

1
/usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql
  1. 记录下 MySQL 初始化生成的 root 用户密码 sF#Hy,IuT6d#
1
2
3
4
5
6
7
8
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#
  1. 启动MySQL
1
/usr/local/mysql/bin/mysqld_safe --user=mysql &
  1. 修改 root 用户密码
1
mysqladmin -uroot -p'sF#Hy,IuT6d#' password '123456'
  1. 回到集群管理服务器查看集群状态，此时可以看到有一个 SQL 服务已经连接上了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)
  1. 在另一台服务器（192.168.0.104）部署 SQL 服务，回到集群管理服务器查看集群状态，此时可以看到所有 SQL 服务已经连接成功
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)

所有集群服务部署完毕，我们来测试一下集群是否真的部署成功

  1. 在 192.168.0.106 的 MySQL 上创建数据库和表

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE DATABASE `wechat`;
    CREATE TABLE wechat.user (
    	Column1 varchar(100) NULL,
    	Column2 varchar(100) NULL
    )
    ENGINE=ndbcluster
    DEFAULT CHARSET=utf8mb4
    COLLATE=utf8mb4_general_ci;

  2. 插入数据并查看

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    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)

  3. 在另一个 SQL 服务器查询，结果是成功的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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)
  1. 现在我们把其中一个数据节点关掉，在管理服务器我们看到 ndbd 已经关闭一个了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)
  1. 写入一笔数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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)
  1. 在另一台 SQL 服务器查询，结果还是一致的
1
2
3
4
5
6
7
8
mysql> select * from wechat.user;
+---------+---------+
| Column1 | Column2 |
+---------+---------+
| 3       | 4       |
| 1       | 2       |
+---------+---------+
2 rows in set (0.00 sec)
  1. 我们再关闭 192.168.0.106 SQL服务
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)
  1. 在 192.168.0.104 的 SQL 服务写入一笔数据
1
2
3
4
5
6
7
8
9
10
11
12
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)
  1. 启动 192.168.0.106 的数据服务和SQL服务
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)
  1. 在 192.168.0.106 查询数据库发现，发生故障期间产生的数据已经同步了过来
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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)

数据库集群部署成功了，总结一下集群的注意事项

  1. 创建表的时候，需要设置ENGINE=ndbcluster，具体请看上面的建表脚本。
  2. 每个 SQL 服务需要创建一样的用户密码
  3. 管理服务器不能全部发生故障，否则集群数据库操作失败。
  4. 数据服务器不能全部发生故障，否则集群数据库操作失败。
  5. SQL 服务器发生故障期间建立的数据库，在恢复后不会自动同步新建数据库过来，需要手动在故障恢复后的服务器上创建同名数据库，之后数据才会自动同步过来。
  6. 只要管理服务器和数据服务器越多，故障发生时，才能保证数据安全的写入，才不会导致数据库系统不可用。
  7. SQL 服务器越多，把数据库访问的请求通过负载均衡服务分摊到各个 SQL 服务器，就能承受更多的并发量。
  8. 集群启动必须按照以下顺序依次启动，管理服务->数据服务->SQL服务。

相关文档