什麼是MySQL叢集，什麼是MySQL叢集，如果你想知道什麼是MySQL叢集，我現在就帶你研究。
MySQL 是一款流行的輕量級資料庫，很多應用都是使用它作為資料儲存。作為小型應用的資料庫，它完全可以勝任，但是如果是大型應用，高效能高可用的要求，單伺服器部署的MySQL就不夠了。MySQL NDB Cluster 為這個需求提供了一個官方的叢集解決方案。
MySQL NDB Cluster 是什麼
MySQL NDB Cluster 是 MySQL 的一個高可用、高冗餘版本，適用於分散式計算環境。
文件連結
搭建叢集的前置工作
至少準備 3 臺伺服器，一臺作為管理伺服器，兩臺作為資料伺服器和 SQL 伺服器，當然有更多的伺服器會更好。
管理伺服器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二進位制檔案，解壓縮後開始下面的步驟。
部署管理伺服器
- 更新系統
apt update -y && apt upgrade -y && apt install libncurses5 -y
- 複製 ndb_mgm 和 ndb_mgmd 到管理伺服器
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndb_mgm* mgm@192.168.0.105:/home/mgm
- 在管理伺服器複製 ndb_mgm 和 ndb_mgmd 到/usr/local/bin 資料夾
cp -rfv /home/mgm/ndb_mgm* /usr/local/bin
- 賦予 ndb_mgm 和 ndb_mgmd 可執行許可權
chmod +x /usr/local/bin/ndb_mgm*
- 新增配置檔案
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)
- 開啟防火牆，叢集管理服務預設使用 1186 埠
ufw allow 22
ufw allow 1186
ufw enable
- 初始化並啟動管理伺服器
cd /usr/local/bin/
ndb_mgmd --initial --configdir=/var/lib/mysql-cluster -f /var/lib/mysql-cluster/config.ini --ndb-nodeid=1
當出現以下結果的時候，表示管理伺服器已經啟動成功了
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 命令，可以檢視當前叢集的狀態
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)
部署資料伺服器
在所有資料伺服器上執行以下操作
- 更新系統
apt update -y && apt upgrade -y && apt install libncurses5 -y
- 開啟防火牆
ufw allow 22
ufw allow 2202
ufw enable
- 複製 ndbd 和 ndbmtd 到資料伺服器
#複製到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
- 在管理伺服器複製 ndbd 和 ndbmtd 到/usr/local/bin 資料夾
#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
- 賦予 ndbd 可執行許可權
chmod +x /usr/local/bin/ndbd
chmod +x /usr/local/bin/ndbmtd
- 在/etc下加入my.cnf檔案
vi /etc/my.cnf
my.cnf檔案
[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
- 建立資料儲存的目錄，必須與管理服務配置的路徑一致
mkdir -p /data/mysql-cluster/data
- 啟動資料服務
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
- 回到叢集管理伺服器檢視叢集狀態，此時可以看到資料服務已經連線成功
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)
- 在另一臺伺服器（192.168.0.104）重複 4、5、6、7 步驟的操作，結果可看到
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
- 回到叢集管理伺服器檢視叢集狀態，此時可以看到所有資料服務已經連線成功
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)
- 在目錄/data/mysql/data下面可以看到資料服務已經產生了資料
root@ndb1:~# ls /data/mysql/data/
ndb_3_fs ndb_3_out.log ndb_3.pid
部署 SQL 服務
- 複製 MySQL 到SQL伺服器
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
- 解壓縮 MySQL, 然後複製到/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
- 開啟防火牆
ufw allow 22
ufw allow 3306
ufw enable
- 建立 MySQL 資料存放的目錄
mkdir -p /data/mysql/data
mkdir -p /data/mysql/run
mkdir -p /var/log/mysql
- 建立 mysql 使用者，建立相關目錄
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
- 建立 MySQL 配置檔案
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
- 初始化MySQL
/usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql
- 記錄下 MySQL 初始化生成的 root 使用者密碼 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).
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#
- 啟動MySQL
/usr/local/mysql/bin/mysqld_safe --user=mysql &
- 修改 root 使用者密碼
mysqladmin -uroot -p'sF#Hy,IuT6d#' password '123456'
- 回到叢集管理伺服器檢視叢集狀態，此時可以看到有一個 SQL 服務已經連線上了
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)
- 在另一臺伺服器（192.168.0.104）部署 SQL 服務，回到叢集管理伺服器檢視叢集狀態，此時可以看到所有 SQL 服務已經連線成功
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)
所有叢集服務部署完畢，我們來測試一下叢集是否真的部署成功
- 在 192.168.0.106 的 MySQL 上建立資料庫和表
CREATE DATABASE `wechat`;
CREATE TABLE wechat.user (
Column1 varchar(100) NULL,
Column2 varchar(100) NULL
)
ENGINE=ndbcluster
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
- 插入資料並檢視
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)
- 在另一個 SQL 伺服器查詢，結果是成功的
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)
- 現在我們把其中一個資料節點關掉，在管理伺服器我們看到 ndbd已經關閉一個了
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)
- 寫入一筆資料
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)
- 在另一臺 SQL 伺服器查詢，結果還是一致的
mysql> select * from wechat.user;
+---------+---------+
| Column1 | Column2 |
+---------+---------+
| 3 | 4 |
| 1 | 2 |
+---------+---------+
2 rows in set (0.00 sec)
- 我們再關閉 192.168.0.106 SQL服務
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)
- 在 192.168.0.104 的 SQL 服務寫入一筆資料
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)
- 啟動 192.168.0.106 的資料服務和SQL服務
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)
- 在 192.168.0.106 查詢資料庫發現，發生故障期間產生的資料已經同步了過來
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)
資料庫叢集部署成功了，總結一下叢集的注意事項
- 建立表的時候，需要設定ENGINE=ndbcluster，具體請看上面的建表指令碼。
- 每個 SQL 服務需要建立一樣的使用者密碼
- 管理伺服器不能全部發生故障，否則叢集資料庫操作失敗。
- 資料伺服器不能全部發生故障，否則叢集資料庫操作失敗。
- SQL 伺服器發生故障期間建立的資料庫，在恢復後不會自動同步新建資料庫過來，需要手動在故障恢復後的伺服器上建立同名資料庫，之後資料才會自動同步過來。
- 只要管理伺服器和資料伺服器越多，故障發生時，才能保證資料安全的寫入，才不會導致資料庫系統不可用。
- SQL 伺服器越多，把資料庫訪問的請求通過負載均衡服務分攤到各個 SQL 伺服器，才能承受更多的併發量。
- 叢集啟動必須按照以下順序依次啟動，管理服務->資料服務->SQL服務。