<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
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
首先下載 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
複製 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)
以上就是如何搭建 MySQL 高可用高效能叢集的詳細內容,更多關於搭建 MySQL 高可用高效能叢集的資料請關注it145.com其它相關文章!
相關文章
<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
综合看Anker超能充系列的性价比很高,并且与不仅和iPhone12/苹果<em>Mac</em>Book很配,而且适合多设备充电需求的日常使用或差旅场景,不管是安卓还是Switch同样也能用得上它,希望这次分享能给准备购入充电器的小伙伴们有所
2021-06-01 09:31:42
除了L4WUDU与吴亦凡已经多次共事,成为了明面上的厂牌成员,吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节,这也是20XXCLUB首次全员合照,王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让
2021-06-01 09:31:34
目前应用IPFS的机构:1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆,历史资料永久保存在 IPFS 6 加
2021-06-01 09:31:24
开拓者的车机是兼容苹果和<em>安卓</em>,虽然我不怎么用,但确实兼顾了我家人的很多需求:副驾的门板还配有解锁开关,有的时候老婆开车,下车的时候偶尔会忘记解锁,我在副驾驶可以自己开门:第二排设计很好,不仅配置了一个很大的
2021-06-01 09:30:48
不仅是<em>安卓</em>手机,苹果手机的降价力度也是前所未有了,iPhone12也“跳水价”了,发布价是6799元,如今已经跌至5308元,降价幅度超过1400元,最新定价确认了。iPhone12是苹果首款5G手机,同时也是全球首款5nm芯片的智能机,它
2021-06-01 09:30:45