目錄MySQL NDB Cluster 是什么搭建集群的前置工作開始部署集群部署管理服務器部署數據服務器部署 SQL 服務所有集群服務部署完畢，我們來測試一下集群是否真的部署成功數據庫集群部署成功了，總結一下集群的注意事項MySQL NDB Cluster 是什么

MySQL NDB Cluster 是 MySQL 的一個高可用、高冗余版本，適用于分布式計算環境。文檔鏈接

搭建集群的前置工作

至少準備 3 臺服務器，一臺作為管理服務器，兩臺作為數據服務器和 SQL 服務器，當然有更多的服務器會更好。

管理服務器mgm：192.168.0.105數據服務器ndb1：192.168.0.106數據服務器ndb2：192.168.0.104sql服務器：192.168.0.106sql服務器：192.168.0.104

開始部署集群

首先下載 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* [email protected]:/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-clustervi /var/lib/mysql-cluster/config.ini

config.ini

[ndbd default]# Options affecting ndbd processes on all data nodes:NoOfReplicas=2 # Number of fragment replicasDataMemory=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 nodeNodeId=1 # Node ID for this Management nodeDataDir=/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 addressNodeId=2 # Node ID for this data nodeDataDir=/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 addressNodeId=3# Node ID for this data nodeDataDir=/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 22ufw allow 1186ufw 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=1MySQL 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> showConnected to Management Server at: localhost:1186Cluster 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)[email protected] (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 22ufw allow 2202ufw enable

復制 ndbd 和 ndbmtd 到數據服務器

#復制到192.168.0.106scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbd [email protected]:/home/ndb1scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbmtd [email protected]:/home/ndb1#復制到192.168.0.104scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbd [email protected]:/home/ndb2scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbmtd [email protected]:/home/ndb2

在管理服務器復制 ndbd 和 ndbmtd 到/usr/local/bin 文件夾

#192.168.0.106cp -rfv /home/ndb1/ndbd /usr/local/bincp -rfv /home/ndb1/ndbmtd /usr/local/bin#192.168.0.104cp -rfv /home/ndb2/ndbd /usr/local/bincp -rfv /home/ndb2/ndbmtd /usr/local/bin

賦予 ndbd 可執行權限

chmod +x /usr/local/bin/ndbdchmod +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# ndbd2021-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> showConnected to Management Server at: localhost:1186Cluster 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# ndbd2021-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> showConnected to Management Server at: localhost:1186Cluster 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 [email protected]:/home/ndb2scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64.tar.gz [email protected]:/home/ndb1

解壓縮 MySQL, 然后復制到/usr/local目錄

tar -zxvf mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64.tar.gzcp -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/mysqlcp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql.serverexport PATH=$PATH:/usr/local/mysql/binsource /etc/profile

開啟防火墻

ufw allow 22ufw allow 3306ufw enable

創建 MySQL 數據存放的目錄

mkdir -p /data/mysql/datamkdir -p /data/mysql/runmkdir -p /var/log/mysql

創建 mysql 用戶，創建相關目錄

groupadd mysqluseradd -r -g mysql -s /bin/false mysqlchown mysql:mysql /data/mysql/datachmod 750 /data/mysql/datachown mysql:mysql /data/mysql/runchmod 750 /data/mysql/runchown mysql:mysql /var/log/mysqlchmod 750 /var/log/mysql

創建 MySQL 配置文件

mkdir -p /etc/mysqlvi /etc/mysql/my.cnfmy.cnf[mysqld]# Options for mysqld process:ndbcluster # run NDB storage enginepid-file = /data/mysql/run/mysqld.pidsocket = /data/mysql/run/mysqld.sockdatadir = /data/mysql/data# log-error = /var/log/mysql/error.log# By default we only accept connections from localhostbind-address = 192.168.0.106# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-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/mysql2021-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=457902021-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> showConnected to Management Server at: localhost:1186Cluster 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> showCluster 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=ndbclusterDEFAULT CHARSET=utf8mb4COLLATE=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> showConnected to Management Server at: localhost:1186Cluster 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> showConnected to Management Server at: localhost:1186Cluster 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> showConnected to Management Server at: localhost:1186Cluster 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 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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服務。

以上就是如何搭建 MySQL 高可用高性能集群的詳細內容，更多關于搭建 MySQL 高可用高性能集群的資料請關注好吧啦網其它相關文章！