溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

MySQL Cluster搭建與測(cè)試

發(fā)布時(shí)間:2020-07-02 16:42:31 來(lái)源:網(wǎng)絡(luò) 閱讀:613 作者:知行知行 欄目:MySQL數(shù)據(jù)庫(kù)

1、服務(wù)器準(zhǔn)備
1) MySQL節(jié)點(diǎn)1 10.41.1.85
2) MySQL節(jié)點(diǎn)2 10.41.1.84
3) ndb節(jié)點(diǎn)1 10.41.1.83
4) ndb節(jié)點(diǎn)2 10.41.1.82
5) 管理節(jié)點(diǎn) 10.41.1.81
2、測(cè)試環(huán)境
5臺(tái)服務(wù)器均一樣,不是必須的,所以服務(wù)器均已關(guān)閉iptables和selinux,生產(chǎn)環(huán)境請(qǐng)根據(jù)實(shí)際情況自行決定
systemctl stop firewalld #關(guān)閉防火墻
systemctl disable firewalld #永久關(guān)閉防火墻
setenforce 0 #臨時(shí)關(guān)閉selinux
sed -i 's#SELINUX=enforcing#SELINUX=disable#g' /etc/selinux/config #永久關(guān)閉selinux
getenforce #查看當(dāng)前狀態(tài)
3、軟件準(zhǔn)備
mysql-cluster-gpl-7.2.4-linux2.6-x86_64.tar.gz
4、管理節(jié)點(diǎn)安裝配置
建立目錄:
mkdir /usr/local/mysql/bin -p
mkdir /data/mysql-cluster -p
準(zhǔn)備文件
cp ./mysql-cluster-gpl-7.2.4-linux2.6-x86_64/bin/ndb_mgm /usr/local/mysql/bin/
cp ./mysql-cluster-gpl-7.2.4-linux2.6-x86_64/bin/ndb_mgmd /usr/local/mysql/bin/
環(huán)境變量
echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile
source /etc/profile
配置
cd /data/mysql-cluster/
touch config.ini
cat /data/mysql-cluster/config.ini
[NDBD DEFAULT]
NoOfReplicas=1
DataMemory=64M
IndexMemory=16M
#管理節(jié)點(diǎn)
[NDB_MGMD]
nodeid=1
hostname=10.41.1.81
datadir=/data/mysql-cluster
#第一個(gè) ndbd 節(jié)點(diǎn):
[NDBD]
nodeid=2
hostname=10.41.1.83
datadir=/data/mysql-cluster/data
#第二個(gè) ndbd 節(jié)點(diǎn):
[NDBD]
nodeid=3
hostname=10.41.1.82
datadir=/data/mysql-cluster/data

SQL節(jié)點(diǎn):

[MySQLD]
nodeid=4
hostname=10.41.1.85
[MySQLD]
nodeid=5
hostname=10.41.1.84
[MySQLD]

6、NDB節(jié)點(diǎn)安裝(數(shù)據(jù)節(jié)點(diǎn))
數(shù)據(jù)節(jié)點(diǎn)1: 10.41.1.83
數(shù)據(jù)節(jié)點(diǎn)2: 10.41.1.82
建立用戶
useradd mysql
解壓mysql cluster
tar zxf mysql-cluster-gpl-7.2.4-linux2.6-x86_64.tar.gz
mv mysql-cluster-gpl-7.2.4-linux2.6-x86_64/ /usr/local/mysql/
授權(quán)
cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .
建立數(shù)據(jù)目錄,并授權(quán)(用來(lái)在沒(méi)使用NDB時(shí)存放的數(shù)據(jù))
mkdir /data/mysql -p
chown -R mysql.mysql /data/mysql/
初始化
/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql/ --basedir=/usr/local/mysql
授權(quán)root
chown -R root .
配置
\cp support-files/my-large.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
環(huán)境變量
echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile
source /etc/profile
建立目錄(存放NDB節(jié)點(diǎn)的數(shù)據(jù))
mkdir /data/mysql-cluster/data -p
修改/etc/my.cnf文件
cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
basedir= /usr/local/mysql
ndbcluster
ndb-connectstring=10.41.1.81
[MYSQL_CLUSTER]
ndb-connectstring=10.41.1.81
說(shuō)明:
如果希望盡可能的各環(huán)境保持一致,建議在NDB節(jié)點(diǎn)也和SQL節(jié)點(diǎn)一樣安裝整個(gè)帶有 NDB Cluster 存儲(chǔ)引擎的MySQL Server。(NDB節(jié)點(diǎn)可以不用初始化數(shù)據(jù),自己已經(jīng)測(cè)試,但是我依然會(huì)初始化)安裝細(xì)節(jié)和上面的SQL節(jié)點(diǎn)完全一樣。
7、安裝 MySQL 節(jié)點(diǎn)
sql節(jié)點(diǎn)1: 10.41.1.85
sql節(jié)點(diǎn)2: 10.41.1.84
建立用戶
useradd mysql
解壓mysql cluster
tar zxf mysql-cluster-gpl-7.2.4-linux2.6-x86_64.tar.gz
mv mysql-cluster-gpl-7.2.4-linux2.6-x86_64/ /usr/local/mysql/
授權(quán)
cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .
建立數(shù)據(jù)目錄,并授權(quán)(用來(lái)在沒(méi)使用NDB時(shí)存放的數(shù)據(jù))
mkdir /data/mysql -p
chown -R mysql.mysql /data/mysql/
初始化
/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql/ --basedir=/usr/local/mysql
授權(quán)root
chown -R root .
配置
\cp support-files/my-large.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
環(huán)境變量
echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile
source /etc/profile
建立目錄(存放NDB節(jié)點(diǎn)的數(shù)據(jù))
mkdir /data/mysql-cluster/data -p
修改/etc/my.cnf文件
cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
basedir= /usr/local/mysql
ndbcluster
ndb-connectstring=10.41.1.81
[MYSQL_CLUSTER]
ndb-connectstring=10.41.1.81

開始使用Cluster:
8、啟動(dòng)
啟動(dòng)順序?yàn)椋汗芾砉?jié)點(diǎn)->數(shù)據(jù)節(jié)點(diǎn)->SQL節(jié)點(diǎn)(非常重要)
a)啟動(dòng)管理節(jié)點(diǎn):
ndb_mgmd -f /data/mysql-cluster/config.ini
檢查:
netstat -ntlp | grep 1186
ps -ef | grep ndb_mgmd | grep -v grep
b)啟動(dòng)NDB(數(shù)據(jù)節(jié)點(diǎn))
注意:
只是在第一次啟動(dòng)或在備份/恢復(fù)或配置變化后重啟ndbd時(shí),才加–initial參數(shù)!
ndbd --initial
檢查:
ps -ef | grep ndbd | grep -v grep
c)啟動(dòng)SQL節(jié)點(diǎn)(啟動(dòng)mysql服務(wù))
/etc/init.d/mysqld start
9、ndb_mgm工具
a)show命令查看集群狀態(tài)
ndb_mgm> show
Cluster Configuration

[ndbd(NDB)] 2 node(s)
id=2 @10.41.1.83 (mysql-5.5.19 ndb-7.2.4, Nodegroup: 0, Master)
id=3 @10.41.1.82 (mysql-5.5.19 ndb-7.2.4, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.41.1.81 (mysql-5.5.19 ndb-7.2.4)

[mysqld(API)] 3 node(s)
id=4 @10.41.1.85 (mysql-5.5.19 ndb-7.2.4)
id=5 @10.41.1.84 (mysql-5.5.19 ndb-7.2.4)
id=6 (not connected, accepting connect from any host)
10、測(cè)試1-NDB存儲(chǔ)引擎測(cè)試
(1)在任意一個(gè)SQL節(jié)點(diǎn)(我這里選擇10.41.1.85)的test_cluster庫(kù)中創(chuàng)建測(cè)試表test_table01(不指定引擎)和test_table02(NDB存儲(chǔ)引擎),設(shè)置存儲(chǔ)引擎為NDB,并插入兩條測(cè)試數(shù)據(jù):
mysql> create table test_table01( id int, name varchar(20) ) engine=ndb;
mysql> create table test_table02( id int, name varchar(20) ) ;
mysql> show tables;
+------------------------+
Tables_in_test_cluster
+------------------------+
test_table01
test_table02
+------------------------+
2 rows in set (0.01 sec)
另一sql節(jié)點(diǎn):
mysql> show tables;
+------------------------+
Tables_in_test_cluster
+------------------------+
test_table02
+------------------------+
1 row in set (0.02 sec)
說(shuō)明只有ndb引擎才同步。
(2)在test_table02中插入兩條測(cè)試數(shù)據(jù)
mysql> insert into t1 select 1,'yayun';
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into t1 select 1,'atlas';
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
兩個(gè)sql節(jié)點(diǎn)都能查到數(shù)據(jù)。
mysql> select * from test_table02;
+------+-----------+
id | name
+------+-----------+
1 | cluster01
2 | cluster02
+------+-----------+
2 rows in set (0.00 sec)
顯然,兩個(gè)SQL節(jié)點(diǎn)查詢的數(shù)據(jù)是一致。
(3)在SQL節(jié)點(diǎn)10.41.1.85上把測(cè)試表test_table02引擎改為MyISAM,再次插入測(cè)試數(shù)據(jù):
mysql> alter table test_table02 engine=myisam;
Query OK, 2 rows affected (0.90 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into test_table02 value(3,'cluster03');#10.41.1.84
ERROR 1412 (HY000): Table definition has changed, please retry transaction
插入數(shù)據(jù)直接報(bào)錯(cuò)。

11、單點(diǎn)故障測(cè)試
管理節(jié)點(diǎn),不需要特殊的配置,只需要將管理工具和配置文件放置多臺(tái)服務(wù)器上即可。
SQL節(jié)點(diǎn)
10.41.1.84上的MySQL服務(wù)停止
管理節(jié)點(diǎn):
[mysqld(API)] 3 node(s)
id=4 @10.41.1.85 (mysql-5.5.19 ndb-7.2.4)
id=5 (not connected, accepting connect from 10.41.1.84)
id=6 (not connected, accepting connect from any host)
10.41.1.85:
mysql> insert into test_table02 value(4,'cluster04');
Query OK, 1 row affected (0.00 sec)
mysql> select from test_table02;
+------+-----------+
id | name
+------+-----------+
2 | cluster02
3 | cluster03
4 | cluster04
1 | cluster01
+------+-----------+
4 rows in set (0.00 sec)
10.41.1.85服務(wù)開啟:
mysql> select
from test_table02;
+------+-----------+
id | name
+------+-----------+
3 | cluster03
4 | cluster04
1 | cluster01
2 | cluster02
+------+-----------+
4 rows in set (0.01 sec)
數(shù)據(jù)又同步過(guò)來(lái)了

NDB(數(shù)據(jù)節(jié)點(diǎn))
數(shù)據(jù)節(jié)點(diǎn)10.41.1.82上的NDB進(jìn)程停止
[root@node3 mysql]# ps -ef | grep ndbd
root 15969 1 0 14:37 ? 00:00:04 ndbd --initial
root 15970 15969 1 14:37 ? 00:00:47 ndbd --initial
root 16029 15801 0 15:36 pts/3 00:00:00 grep --color=auto ndbd
killall ndbd
任意sql節(jié)點(diǎn)查詢:
mysql> select from test_table02;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
無(wú)法查詢
10.41.1.81:
grep 'NoOfReplicas' /data/mysql-cluster/config.ini
NoOfReplicas=1 #每個(gè)數(shù)據(jù)節(jié)點(diǎn)的鏡像數(shù)量
將配置文件中的NoOfReplicas改為2,按照前面的步驟重新啟動(dòng)集群,無(wú)法啟動(dòng),NoOfReplicas參數(shù)無(wú)法臨時(shí)更改,我們開始就需要設(shè)置好,不要到后面才想到更改,那時(shí)就悲劇了。如果重新ndbd --initial,將會(huì)丟失所有數(shù)據(jù)。
NoOfReplicas=2:
管理節(jié)點(diǎn)需要--initial參數(shù)啟動(dòng)
ndb_mgmd --initial -f /data/mysql-cluster/config.ini
數(shù)據(jù)節(jié)點(diǎn)(NDB)啟動(dòng)還要用ndbd --initial,數(shù)據(jù)丟失
mysql> select
from test_table02;
ERROR 1146 (42S02): Table 'test_cluster.test_table02' doesn't exist
mysql> create table test(
-> id int,
-> name varchar(20)
-> ) engine=ndb;
Query OK, 0 rows affected (0.85 sec)

mysql> show tables;
+------------------------+
Tables_in_test_cluster
+------------------------+
test
+------------------------+
1 row in set (0.01 sec)

mysql> insert into test value(1,'one');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test value(2,'two');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+
id | name
+------+------+
1 | one
2 | two
+------+------+
2 rows in set (0.00 sec)
再次停掉數(shù)據(jù)節(jié)點(diǎn)10.41.1.82:
兩節(jié)點(diǎn)都可以正常數(shù)據(jù)查詢
12、MySQL Cluster集群的關(guān)閉
關(guān)閉順序:SQL節(jié)點(diǎn)->數(shù)據(jù)節(jié)點(diǎn)->管理節(jié)點(diǎn)(在MySQL Cluster環(huán)境中,NDB節(jié)點(diǎn)和管理節(jié)點(diǎn)的關(guān)閉都可以在管理節(jié)點(diǎn)的管理程序中完成,也可以分節(jié)點(diǎn)關(guān)閉,但是SQL節(jié)點(diǎn)卻沒(méi)辦法。所以,在關(guān)閉整個(gè)MySQL Cluster環(huán)境或者關(guān)閉某個(gè)SQL節(jié)點(diǎn)的時(shí)候,首先必須到SQL節(jié)點(diǎn)主機(jī)上來(lái)關(guān)閉SQL節(jié)點(diǎn)程序。關(guān)閉方法和MySQL Server的關(guān)閉一樣。)
(1)SQL節(jié)點(diǎn)關(guān)閉
/etc/init.d/mysqld stop
(2)(NDB)數(shù)據(jù)節(jié)點(diǎn)關(guān)閉
ndbd stop
(3)管理節(jié)點(diǎn)關(guān)閉
ndb_mgm> shutdown或者命令行:ndb_mgm -e shutdown
總結(jié):
參數(shù)NoOfReplicas無(wú)法臨時(shí)更改。
selinux,iptables等相關(guān)問(wèn)題。
注:參考大量網(wǎng)絡(luò)數(shù)據(jù)。

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI