MySQL的集群配置的基本命令使用及一次操作過程實(shí)錄
1. 先了解一下你是否應(yīng)該用MySQL集群。
減少數(shù)據(jù)中心結(jié)點(diǎn)壓力和大數(shù)據(jù)量處理,采用把MySQL分布,一個(gè)或多個(gè)application對(duì)應(yīng)一個(gè)MySQL數(shù)據(jù)庫。把幾個(gè)MySQL數(shù)據(jù)庫公用的數(shù)據(jù)做出共享數(shù)據(jù),例如購物車,用戶對(duì)象等等,存在數(shù)據(jù)結(jié)點(diǎn)里面。其他不共享的數(shù)據(jù)還維持在各自分布的MySQL數(shù)據(jù)庫本身中。
2. 集群MySQL中名稱概念.(如上圖)
1)Sql結(jié)點(diǎn)(SQL node--上圖對(duì)應(yīng)為MySQLd):分布式數(shù)據(jù)庫。包括自身數(shù)據(jù)和查詢中心結(jié)點(diǎn)數(shù)據(jù).
2)數(shù)據(jù)結(jié)點(diǎn)(Data node -- ndbd):集群共享數(shù)據(jù)(內(nèi)存中).
3)管理服務(wù)器(Management Server – ndb_mgmd):集群管理SQL node,Data node.
3.配置
MySQL-max版本,當(dāng)然現(xiàn)在MySQL集群系統(tǒng)windonws平臺(tái)上面不被支持.
安裝MySQL就不多說了,網(wǎng)上一大堆,簡(jiǎn)明扼要。
- A:192.168.1.251 – Data node和Management Server.
- B:192.168.1.254 – SQL node.
當(dāng)然,你也可以讓一個(gè)機(jī)器同時(shí)為3者。
A,B my.inf加上:
[MySQLD] ndbcluster # run NDB engine ndb-connectstring=192.168.1.251 # location of MGM node # Options for ndbd process: [MySQL_CLUSTER] ndb-connectstring=192.168.1.251 # location of MGM node A: /var/lib/MySQL-cluster/config.ini [NDBD DEFAULT] NoOfReplicas=1 # Number of replicas DataMemory=80M # How much memory to allocate for data storage IndexMemory=18M # How much memory to allocate for index storage # For DataMemory and IndexMemory, we have used the # default values. Since the "world" database takes up # only about 500KB, this should be more than enough for # this example Cluster setup. # TCP/IP options: [TCP DEFAULT] portnumber=2202 # This the default; however, you can use any # port that is free for all the hosts in cluster # Note: It is recommended beginning with MySQL 5.0 that # you do not specify the portnumber at all and simply allow # the default value to be used instead # Management process options: [NDB_MGMD] hostname=192.168.1.251 # Hostname or IP address of MGM node datadir=/var/lib/MySQL-cluster # Directory for MGM node logfiles # Options for data node "A": [NDBD] # (one [NDBD] section per data node) hostname=192.168.1.251 # Hostname or IP address datadir=/usr/local/MySQL/data # Directory for this data node's datafiles # SQL node options: [MySQLD] hostname=192.168.1.254 #[MySQLD] #這個(gè)相當(dāng)于192.168.1.251
4. 啟動(dòng)測(cè)試
在管理服務(wù)器上面(這里是192.168.1.251):
shell>ndb_mgmd -f /var/lib/MySQL-cluster/config.ini
在數(shù)據(jù)結(jié)點(diǎn)服務(wù)器上面(依然是192.168.1.251and more):
shell>ndbd--initial
(第一次時(shí)加--initial參數(shù))
SQL結(jié)點(diǎn)服務(wù)器上面(192.168.1.254):
shell>MySQLd &
在251上面察看
./ndb_mgm
-- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: 192.168.1.251:1186 Cluster Configuration --------------------- [ndbd(NDB)] 1 node(s) id=2 @192.168.1.251 (Version:5.0.22, Nodegroup: 0, Master) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.1.251 (Version:5.0.22) [MySQLd(API)] 1 node(s) id=3 @192.168.1.254 (Version:5.0.22) ok
關(guān)閉集群:
shell>ndb_mgm -e shutdown
5.基本的集群說明
1)在MySQL集群中.當(dāng)table引擎為NDBCLUSTER時(shí)才做集群,其他非NDBCLUSTER表和一般MySQL數(shù)據(jù)庫表一樣,不會(huì)共享數(shù)據(jù). NDBCLUSTER表數(shù)據(jù)存儲(chǔ)在Data node服務(wù)器內(nèi)存中,Data Node可以為1臺(tái)或多臺(tái)服務(wù)器,它們之間存放共享數(shù)據(jù)。Data Node服務(wù)器可以分組數(shù)據(jù)copy。
例如:2,3,4,5為四臺(tái)Data Node服務(wù)器ID. 2,3為組0。 4,5為組1。 2,3維持?jǐn)?shù)據(jù)相同,4,5維持?jǐn)?shù)據(jù)相同。 組0和組1維持?jǐn)?shù)據(jù)不同。
2)sql node服務(wù)器中,非NDBCLUSTER數(shù)據(jù)存在本身數(shù)據(jù)庫中,table引擎為NDBCLUSTER時(shí),數(shù)據(jù)存儲(chǔ)在Data Node中。當(dāng)查詢NDBCLUSTER表時(shí),它會(huì)從Data node集群中提起數(shù)據(jù).
3)Manager server
管理SQl node和Data node狀態(tài)。
附:MySQL集群配置詳細(xì)過程錄制
1、準(zhǔn)備三臺(tái)linux服務(wù)器(三臺(tái)機(jī)器進(jìn)行如下配置)
--hostname配置
192.168.9.241 sqltest01 (mysqld及存儲(chǔ)節(jié)點(diǎn))
192.168.9.242 sqltest02 (mysqld及存儲(chǔ)節(jié)點(diǎn))
192.168.9.243 sqltest03
其中,sqltest01、sqltest02分別是mysql節(jié)點(diǎn)及存儲(chǔ)節(jié)點(diǎn),sqltest03為管理節(jié)點(diǎn)
--同時(shí),把防火墻進(jìn)行關(guān)閉或者把相關(guān)的端口打開,如3306,管理節(jié)點(diǎn)的1186等
[root@sqltest01 u01]# service iptables status
iptables: Firewall is not running.
如果開啟的,請(qǐng)使用service iptables stop
--創(chuàng)建相應(yīng)的用戶及目錄
[root@sqltest01 u01]# groupadd mysql [root@sqltest01 u01]# useradd -r -g mysql mysql [root@sqltest01 u01]# mkdir -p /usr/local/mysql [root@sqltest01 u01]# chown -R mysql.mysql
2、mysql cluster
下載網(wǎng)址:dev.mysql.com,然后選擇cluster,然后在網(wǎng)頁中出現(xiàn)的選擇平臺(tái)中,選擇linux generic!在這里選擇所需要tar包,我這里用的是mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz
下載完成后,使用ftp傳送到服務(wù)器上面,然后分別在三臺(tái)機(jī)器上解壓
[root@sqltest01 u01]# tar -zxvf mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz
mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/have_plugin_auth.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/kill_query.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/unsafe_binlog.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/have_multi_ndb.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/ipv6_clients.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/setup_fake_relay_log.inc mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/mysql-test/include/wait_for_slave_sql_error_and_skip.inc ......................................................................................................
解壓后,里面包括了數(shù)據(jù)庫文件以及集群軟件
3、配置管理節(jié)點(diǎn)(sqltest03)
--將剛才解壓的軟件拷貝到指定位置/usr/local/mysql
[root@sqltest03 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# pwd /u01/mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64 [root@sqltest03 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# mv * /usr/local/mysql/
--創(chuàng)建集群目錄
[root@sqltest03 u01]# cd /usr/local/mysql [root@sqltest03 mysql]# mkdir mysql-cluster [root@sqltest03 mysql]# pwd /usr/local/mysql [root@sqltest03 mysql]# cp bin/ndb_mgm* /usr/local/bin/ [root@sqltest03 mysql]# cd /var/lib [root@sqltest03 mysql]# mkdir mysql-cluster [root@sqltest03 mysql]# cd mysql-cluster [root@sqltest03 mysql]# vi config.ini
配置內(nèi)容如下:
[root@sqltest03 mysql-cluster]# cat config.ini [ndbd default] NoOfReplicas=1 DataMemory=2048M IndexMemory=512M [tcp default] [ndb_mgmd] hostname=192.168.9.243 datadir=/var/lib/mysql-cluster NodeId=1 [ndbd] hostname=192.168.9.241 datadir=/u01/mysql/data NodeId=2 [ndbd] hostname=192.168.9.242 datadir=/u01/mysql/data NodeId=3 [mysqld] hostname=192.168.9.241 NodeId=4 [mysqld] hostname=192.168.9.242 NodeId=5
配置說明:
[ndbd default]
這部分是公共部分,對(duì)于每一個(gè)數(shù)據(jù)節(jié)點(diǎn)都有效,只需要配置一份
NoOfReplicas=1
數(shù)據(jù)鏡像幾份(各數(shù)據(jù)節(jié)點(diǎn)之間相互備份)
[tcp default]
針對(duì)每個(gè)數(shù)據(jù)節(jié)點(diǎn)及管理節(jié)點(diǎn)之間使用哪個(gè)端口進(jìn)行通訊,在舊版本的NDB集群軟件配置時(shí),這個(gè)地方通常配置portnumber=2202但新版的NDB軟件這里不需要配置,并且MySQL官方也強(qiáng)烈建議不要配置
[ndb_mgmd]
管理節(jié)點(diǎn)的配置部分(通常只有一個(gè))。注意NodeId=1指明管理節(jié)點(diǎn)的節(jié)點(diǎn)ID為1,如果不指定,在啟動(dòng)集群時(shí),會(huì)報(bào)錯(cuò)
hostname=192.168.9.243
指明管理節(jié)點(diǎn)的IP地址
datadir=/var/lib/mysql-cluster
指明集群管理日志存放的位置
[ndbd]
數(shù)據(jù)節(jié)點(diǎn)配置部分,有幾個(gè)數(shù)據(jù)節(jié)點(diǎn)就配置幾個(gè)[ndbd]
hostname=192.168.1.111
指明數(shù)據(jù)節(jié)點(diǎn)的IP地址
datadir=/u01/app/mysql/data
指明數(shù)據(jù)節(jié)點(diǎn)上的數(shù)據(jù)庫文件存放的位置
NodeId=2
指明該數(shù)據(jù)節(jié)點(diǎn)在整個(gè)集群中的nodeid號(hào)(很重要)
[mysqld]
SQL節(jié)點(diǎn)配置部分,有幾個(gè)SQL節(jié)點(diǎn),就配置幾個(gè)[mysqld]
到這里,就可以啟動(dòng)集群了
[root@sqltest03 bin]# pwd /usr/local/bin [root@sqltest03 bin]# ./ndb_mgmd -f /var/lib/mysql-cluster/config.ini MySQL Cluster Management Server mysql-5.6.21 ndb-7.3.7
進(jìn)入執(zhí)行查看
[root@sqltest03 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.9.241) id=3 (not connected, accepting connect from 192.168.9.242) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.9.243 (mysql-5.6.21 ndb-7.3.7) [mysqld(API)] 2 node(s) id=4 (not connected, accepting connect from 192.168.9.241) id=5 (not connected, accepting connect from 192.168.9.242)
可以看到有兩個(gè)節(jié)點(diǎn),節(jié)點(diǎn)沒有連接上
4、配置mysqld節(jié)點(diǎn)及存儲(chǔ)節(jié)點(diǎn)(sqltest01,sqltest02)
--建立相應(yīng)目錄
[root@sqltest01 mysql]# mkdir -p /usr/local/mysql --用于存放剛才解壓的文件,如mysql的bin目錄等 [root@sqltest01 mysql]# mkdir -p /u01/mysql/data --用于存儲(chǔ)數(shù)據(jù)文件(innodb) [root@sqltest01 mysql]# chown -R mysql.mysql /u01
--將先前解壓的文件拷貝
[root@sqltest01 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# pwd /u01/mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64 [root@sqltest01 mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64]# mv * /usr/local/mysql/ [root@sqltest01 mysql]# chown -R mysql.mysql /usr/local/mysql/
--拷貝mysql.server
[root@sqltest01 support-files]# pwd /usr/local/mysql/support-files [root@sqltest01 support-files]# ls -lrt total 32 -rw-r--r--. 1 mysql mysql 773 Oct 9 21:46 magic -rwxr-xr-x. 1 mysql mysql 10880 Oct 9 22:42 mysql.server -rwxr-xr-x. 1 mysql mysql 894 Oct 9 22:42 mysql-log-rotate -rwxr-xr-x. 1 mysql mysql 1061 Oct 9 22:42 mysqld_multi.server -rw-r--r--. 1 mysql mysql 1126 Oct 9 22:42 my-default.cnf -rwxr-xr-x. 1 mysql mysql 1153 Oct 9 22:42 binary-configure [root@sqltest01 support-files]# cp mysql.server /etc/rc.d/init.d/mysqld
--編輯環(huán)境變量
[root@sqltest01 tmp]# vi /etc/profile
添加如下:
PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
export PATH
[root@sqltest01 tmp]# source /etc/profile
--使修改生效
--配置my.cnf
[root@sqltest01 support-files]# cp my-default.cnf /etc/my.cnf
并對(duì)my.cnf進(jìn)行配置,具體配置如下
[mysqld] ndbcluster basedir=/usr/local/mysql datadir=/u01/mysql/data port=3306 [mysql_cluster] ndb-connectstring=192.168.9.243
--初始化節(jié)點(diǎn)數(shù)據(jù)庫
[root@sqltest01 mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/u01/mysql/data
執(zhí)行完這條命令以后,數(shù)據(jù)庫的數(shù)據(jù)文件(包括mysql,test , performance_schema等數(shù)據(jù)庫),就安裝到相應(yīng)的位置了,可以使用了
在兩個(gè)節(jié)點(diǎn)都執(zhí)行上面的步驟即可
5、在兩個(gè)點(diǎn)啟動(dòng)
[root@sqltest01 mysql]# ndbd --initial
2014-12-24 17:55:57 [ndbd] INFO -- Angel connected to '192.168.9.243:1186' 2014-12-24 17:55:57 [ndbd] INFO -- Angel allocated nodeid: 2
第一次啟動(dòng)時(shí),需要加--initial來初始化數(shù)據(jù)節(jié)點(diǎn),第二次啟動(dòng)時(shí),就不需要這個(gè)參數(shù)了
在管理節(jié)點(diǎn)查看,可以看到第一個(gè)節(jié)點(diǎn)已經(jīng)連接
ndb_mgm> show
Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @192.168.9.241 (mysql-5.6.21 ndb-7.3.7, starting, Nodegroup: 0) --表明已經(jīng)連接上了 id=3 (not connected, accepting connect from 192.168.9.242) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.9.243 (mysql-5.6.21 ndb-7.3.7) [mysqld(API)] 2 node(s) id=4 (not connected, accepting connect from 192.168.9.241) id=5 (not connected, accepting connect from 192.168.9.242)
啟動(dòng)mysqld
[root@sqltest01 mysql]# cd /usr/local/mysql/bin [root@sqltest01 bin]# ./mysqld_safe --user=mysql
141224 17:59:50 mysqld_safe Logging to '/u01/mysql/data/sqltest01.err'. 141224 17:59:51 mysqld_safe Starting mysqld daemon with databases from /u01/mysql/data
啟動(dòng)數(shù)據(jù)庫時(shí),第一次初始化使用的root,而這次使用mysql,需要對(duì)/u01/mysql/data權(quán)限進(jìn)行配置,否則報(bào)不可讀寫
再次在管理節(jié)點(diǎn)查看
ndb_mgm> show
Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @192.168.9.241 (mysql-5.6.21 ndb-7.3.7, Nodegroup: 0, *) id=3 (not connected, accepting connect from 192.168.9.242) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.9.243 (mysql-5.6.21 ndb-7.3.7) [mysqld(API)] 2 node(s) id=4 @192.168.9.241 (mysql-5.6.21 ndb-7.3.7)
--表明已經(jīng)連接上了
最后把第二節(jié)點(diǎn)也啟動(dòng),再次從管理節(jié)點(diǎn)檢查
ndb_mgm> show
Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @192.168.9.241 (mysql-5.6.21 ndb-7.3.7, Nodegroup: 0, *) id=3 @192.168.9.242 (mysql-5.6.21 ndb-7.3.7, Nodegroup: 1) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.9.243 (mysql-5.6.21 ndb-7.3.7) [mysqld(API)] 2 node(s) id=4 @192.168.9.241 (mysql-5.6.21 ndb-7.3.7) id=5 @192.168.9.242 (mysql-5.6.21 ndb-7.3.7)
6、在兩個(gè)節(jié)點(diǎn)測(cè)試
[root@sqltest01 ~]# mysql -uroot -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.21-ndb-7.3.7-cluster-gpl MySQL Cluster Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. 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> create database mydb1; Query OK, 1 row affected (0.07 sec) mysql> use mydb1; Database changed mysql> create table mytb1(id int,birthdate datetime,pername char(10)) engine=ndbcluster; Query OK, 0 rows affected (0.19 sec)
mysql> insert into mytb1(id,birthdate,pername) values(1,'2013-01-23 09:45:10','pengzitong');
Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> insert into mytb1(id,birthdate,pername) values(2,'2007-07-09 09:45:10','pengzixin'); Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在第二節(jié)點(diǎn)檢查
[root@sqltest02 ~]# mysql -uroot -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.21-ndb-7.3.7-cluster-gpl MySQL Cluster Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. 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> use mydb1 mysql> select * from mytb1; +------+---------------------+------------+ | id | birthdate | pername | +------+---------------------+------------+ | 1 | 2013-01-23 09:45:10 | pengzitong | | 2 | 2007-07-09 09:45:10 | pengzixin | +------+---------------------+------------+
7、集群停止
要想關(guān)閉 Cluster,可在MGM節(jié)點(diǎn)所在的機(jī)器上,在Shell中簡(jiǎn)單地輸入下述命令:
[root@sqltest03 bin]# /usr/local/mysql/ndb_mgm -e shutdown
運(yùn)行以下命令關(guān)閉SQL節(jié)點(diǎn)的mysqld服務(wù):
[root@sqltest01 bin]# /usr/local/mysql/bin/mysqladmin -uroot shutdown
相關(guān)文章
MySQL8.0.21安裝步驟及出現(xiàn)問題解決方案
這篇文章主要介紹了MySQL8.0.21安裝步驟及出現(xiàn)問題解決方案,本文通過圖文并茂的形式給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-12-12同時(shí)運(yùn)行多個(gè)MySQL服務(wù)器的方法
在同一臺(tái)機(jī)器上運(yùn)行多個(gè)有些情況下你可能想要在同一臺(tái)機(jī)器上運(yùn)行多個(gè)服務(wù)器。例如,你可能想要測(cè)試一個(gè)新的MySQL版本而讓你現(xiàn)有生產(chǎn)系統(tǒng)的設(shè)置不受到干擾, 或你可能是想要為不同的客戶提供獨(dú)立的MySQL安裝一個(gè)因特網(wǎng)服務(wù)供應(yīng)商。2008-05-05MySQL之權(quán)限以及設(shè)計(jì)數(shù)據(jù)庫案例講解
這篇文章主要介紹了MySQL之權(quán)限以及設(shè)計(jì)數(shù)據(jù)庫案例講解,本篇文章通過簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08MySQL性能優(yōu)化之table_cache配置參數(shù)淺析
這篇文章主要介紹了MySQL性能優(yōu)化之table_cache配置參數(shù)淺析,本文介紹了它的緩存機(jī)制、參數(shù)優(yōu)化及清空緩存的命令等,需要的朋友可以參考下2014-07-07MySQL安裝與配置:手工配置MySQL(windows環(huán)境)過程
這篇文章主要介紹了MySQL安裝與配置:手工配置MySQL(windows環(huán)境)過程,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12