如何搭建 MySQL 高可用高性能集群
發(fā)布日期：2022-02-10 15:18 | 文章來源：gibhub
MySQL NDB Cluster 是什么
MySQL NDB Cluster 是 MySQL 的一個高可用、高冗余版本，適用于分布式計算環(huán)境。
文檔鏈接
搭建集群的前置工作
至少準備 3 臺服務(wù)器，一臺作為管理服務(wù)器，兩臺作為數(shù)據(jù)服務(wù)器和 SQL 服務(wù)器，當然有更多的服務(wù)器會更好。
管理服務(wù)器mgm：192.168.0.105
數(shù)據(jù)服務(wù)器ndb1：192.168.0.106
數(shù)據(jù)服務(wù)器ndb2：192.168.0.104
sql服務(wù)器：192.168.0.106
sql服務(wù)器：192.168.0.104
開始部署集群
首先下載 MySQL NDB Cluster二進制文件，解壓縮后開始下面的步驟。
部署管理服務(wù)器
更新系統(tǒng)
apt update -y && apt upgrade -y && apt install libncurses5 -y
復(fù)制 ndb_mgm 和 ndb_mgmd 到管理服務(wù)器
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndb_mgm* mgm@192.168.0.105:/home/mgm
在管理服務(wù)器復(fù)制 ndb_mgm 和 ndb_mgmd 到/usr/local/bin 文件夾
cp -rfv /home/mgm/ndb_mgm* /usr/local/bin
賦予 ndb_mgm 和 ndb_mgmd 可執(zhí)行權(quán)限
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)
開啟防火墻，集群管理服務(wù)默認使用 1186 端口
ufw allow 22 ufw allow 1186 ufw enable
初始化并啟動管理服務(wù)器
cd /usr/local/bin/ ndb_mgmd --initial --configdir=/var/lib/mysql-cluster -f /var/lib/mysql-cluster/config.ini --ndb-nodeid=1
當出現(xiàn)以下結(jié)果的時候，表示管理服務(wù)器已經(jīng)啟動成功了
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
我們再執(zhí)行 ndb_mgm 命令，可以查看當前集群的狀態(tài)
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)
部署數(shù)據(jù)服務(wù)器
在所有數(shù)據(jù)服務(wù)器上執(zhí)行以下操作
更新系統(tǒng)
apt update -y && apt upgrade -y && apt install libncurses5 -y
開啟防火墻
ufw allow 22 ufw allow 2202 ufw enable
復(fù)制 ndbd 和 ndbmtd 到數(shù)據(jù)服務(wù)器
#復(fù)制到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 #復(fù)制到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
在管理服務(wù)器復(fù)制 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 可執(zhí)行權(quán)限
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
創(chuàng)建數(shù)據(jù)保存的目錄，必須與管理服務(wù)配置的路徑一致
mkdir -p /data/mysql-cluster/data
啟動數(shù)據(jù)服務(wù)
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
回到集群管理服務(wù)器查看集群狀態(tài)，此時可以看到數(shù)據(jù)服務(wù)已經(jīng)連接成功
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)
在另一臺服務(wù)器（192.168.0.104）重復(fù) 4、5、6、7 步驟的操作，結(jié)果可看到
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
回到集群管理服務(wù)器查看集群狀態(tài)，此時可以看到所有數(shù)據(jù)服務(wù)已經(jīng)連接成功
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下面可以看到數(shù)據(jù)服務(wù)已經(jīng)產(chǎn)生了數(shù)據(jù) root@ndb1:~# ls /data/mysql/data/ ndb_3_fs ndb_3_out.log ndb_3.pid
部署 SQL 服務(wù)
復(fù)制 MySQL 到SQL服務(wù)器
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, 然后復(fù)制到/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
創(chuàng)建 MySQL 數(shù)據(jù)存放的目錄
mkdir -p /data/mysql/data mkdir -p /data/mysql/run mkdir -p /var/log/mysql
創(chuàng)建 mysql 用戶，創(chuàng)建相關(guān)目錄
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
創(chuàng)建 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'
回到集群管理服務(wù)器查看集群狀態(tài)，此時可以看到有一個 SQL 服務(wù)已經(jīng)連接上了
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)
在另一臺服務(wù)器（192.168.0.104）部署 SQL 服務(wù)，回到集群管理服務(wù)器查看集群狀態(tài)，此時可以看到所有 SQL 服務(wù)已經(jīng)連接成功
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)
所有集群服務(wù)部署完畢，我們來測試一下集群是否真的部署成功
在 192.168.0.106 的 MySQL 上創(chuàng)建數(shù)據(jù)庫和表
CREATE DATABASE `wechat`;
CREATE TABLE wechat.user (
Column1 varchar(100) NULL,
Column2 varchar(100) NULL
)
ENGINE=ndbcluster
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
插入數(shù)據(jù)并查看
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 服務(wù)器查詢，結(jié)果是成功的
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)
現(xiàn)在我們把其中一個數(shù)據(jù)節(jié)點關(guān)掉，在管理服務(wù)器我們看到 ndbd已經(jīng)關(guān)閉一個了
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)
寫入一筆數(shù)據(jù)
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 服務(wù)器查詢，結(jié)果還是一致的
mysql> select * from wechat.user; +---------+---------+ | Column1 | Column2 | +---------+---------+ | 3 | 4 | | 1 | 2 | +---------+---------+ 2 rows in set (0.00 sec)
我們再關(guān)閉 192.168.0.106 SQL服務(wù)
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 服務(wù)寫入一筆數(shù)據(jù)
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 的數(shù)據(jù)服務(wù)和SQL服務(wù)
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 查詢數(shù)據(jù)庫發(fā)現(xiàn)，發(fā)生故障期間產(chǎn)生的數(shù)據(jù)已經(jīng)同步了過來
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)
數(shù)據(jù)庫集群部署成功了，總結(jié)一下集群的注意事項
- 創(chuàng)建表的時候，需要設(shè)置ENGINE=ndbcluster，具體請看上面的建表腳本。
- 每個 SQL 服務(wù)需要創(chuàng)建一樣的用戶密碼
- 管理服務(wù)器不能全部發(fā)生故障，否則集群數(shù)據(jù)庫操作失敗。
- 數(shù)據(jù)服務(wù)器不能全部發(fā)生故障，否則集群數(shù)據(jù)庫操作失敗。
- SQL 服務(wù)器發(fā)生故障期間建立的數(shù)據(jù)庫，在恢復(fù)后不會自動同步新建數(shù)據(jù)庫過來，需要手動在故障恢復(fù)后的服務(wù)器上創(chuàng)建同名數(shù)據(jù)庫，之后數(shù)據(jù)才會自動同步過來。
- 只要管理服務(wù)器和數(shù)據(jù)服務(wù)器越多，故障發(fā)生時，才能保證數(shù)據(jù)安全的寫入，才不會導(dǎo)致數(shù)據(jù)庫系統(tǒng)不可用。
- SQL 服務(wù)器越多，把數(shù)據(jù)庫訪問的請求通過負載均衡服務(wù)分攤到各個 SQL 服務(wù)器，才能承受更多的并發(fā)量。
- 集群啟動必須按照以下順序依次啟動，管理服務(wù)->數(shù)據(jù)服務(wù)->SQL服務(wù)。
以上就是如何搭建 MySQL 高可用高性能集群的詳細內(nèi)容，更多關(guān)于搭建 MySQL 高可用高性能集群的資料請關(guān)注本站其它相關(guān)文章！
版權(quán)聲明：本站文章來源標注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信