mysql cluster7.4.8安裝和性能測試
測試環境
5臺虛擬機centos6.5，4G內存
192.168.17.177 Management node (mgmd) 192.168.17.178 SQL node (mysqld) 192.168.17.179 SQL node (mysqld) 192.168.17.180 Data node(ndbd) 192.168.17.176 Data node(ndbd)
1臺管理節點，2臺sql節點，2臺存儲節點
Management node為管理節點，負責把數據保存到Data node節點；SQL node為mysql服務器節點，php、java可以mysql驅動連接該節點，訪問mysql cluster數據，與普通mysql服務器不同，該節點只保存表結構不保存具體數據，表數據保存在Data node。
這里為了方便關閉了防火墻
service iptables stop chkconfig iptables off
刪除centos6.5自帶mysql
rpm -qa | grep mysql rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64
在http://dev.mysql.com/downloads/cluster/ 下載安裝文件MySQL-Cluster-gpl-7.4.8-1.el6.x86_64.rpm-bundle.tar，上傳到5臺服務器各自解壓，包含文件
MySQL-Cluster-client-gpl-7.4.8-1.el6.x86_64.rpm MySQL-Cluster-devel-gpl-7.4.8-1.el6.x86_64.rpm MySQL-Cluster-embedded-gpl-7.4.8-1.el6.x86_64.rpm MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm MySQL-Cluster-shared-compat-gpl-7.4.8-1.el6.x86_64.rpm MySQL-Cluster-shared-gpl-7.4.8-1.el6.x86_64.rpm MySQL-Cluster-test-gpl-7.4.8-1.el6.x86_64.rpm
這里只用到MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm和MySQL-Cluster-client-gpl-7.4.8-1.el6.x86_64.rpm
1、安裝
1）Data node安裝
192.168.17.176、192.168.17.180兩臺服務器都需要執行下步安裝
rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm
該節點主要使用ndbd或ndbmtd(在/usr/sbin目錄)命令運行MySQL Cluster data node服務。
2）SQL nodes安裝
192.168.17.178、192.168.17.179兩臺服務器都需要執行下步安裝
rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm
安裝帶NDB引擎的mysqld服務（在/usr/sbin目錄），mysql的啟動命令mysql.server和mysqld_safe(在/usr/share/mysql或/usr/bin目錄)
這兩臺服務器需要提供mysql服務，需要安裝
rpm -ivh MySQL-Cluster-client-gpl-7.4.8-1.el6.x86_64.rpm
3）Management nodes
rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86_64.rpm
安裝ndb_mgmd服務管理（/usr/sbin目錄）和ndb_mgm（NDB管理客戶端）
2、配置
1）data nodes 和 SQL nodes配置
vim /etc/my.cnf
輸入內容：
[mysqld] ndbcluster #NBD引擎 [mysql_cluster] ndb-connectstring=192.168.17.177 #管理節點
2)management node配置
命令行輸入：
mkdir /var/lib/mysql-cluster cd /var/lib/mysql-cluster vim config.ini
config.ini內容為：
[ndbd default] # Options affecting ndbd processes on all data nodes: NoOfReplicas=2 # 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 default] # TCP/IP options: portnumber=2202 # This the default; however, you can use any # port that is free for all the hosts in the cluster # Note: It is recommended that you do not specify the port # number at all and simply allow the default value to be used # instead [ndb_mgmd] # Management process options: hostname=192.168.17.177 # Hostname or IP address of MGM node datadir=/var/lib/mysql-cluster # Directory for MGM node log files [ndbd] # Options for data node # (one [ndbd] section per data node) hostname=192.168.17.176 # Hostname or IP address datadir=/usr/local/mysql/data # Directory for this data node's data files [ndbd] # Options for data node hostname=192.168.17.180 # Hostname or IP address datadir=/usr/local/mysql/data # Directory for this data node's data files [mysqld] # SQL node options: hostname=192.168.17.178 # Hostname or IP address # (additional mysqld connections can be [mysqld] # SQL node options: hostname=192.168.17.179 # specified for this node for various # purposes such as running ndb_restore)
3、啟動
mysql cluster各節點啟動順序為:Management nodes -> data node -> SQL node;
1)啟動管理節點Management nodes（192.168.17.177）
ndb_mgmd -f /var/lib/mysql-cluster/config.ini #如果第一次啟動或配置文件修改過需要的尾后加上--initial
2）啟動存儲節點data node（192.168.17.180，192.168.17.176）
ndbd #如果第一次啟動尾后加上--initial
3）啟動SQL node (192.168.17.178, 192.168.17.179)，讓php，java等通過這邊兩臺服務器訪問mysql cluster
mysqld_safe &
php，java等需要連接這兩臺服務器訪問數據庫，執行下邊命令修改默認密碼和數據庫初始化。
more /root/.mysql_secret #獲取默認密碼 /usr/bin/mysql_secure_installation #修改root密碼和其它默認配置
4、關閉
mysql cluster關閉順序與啟動相反SQL node -> data node ->Management nodes
1）SQL node節點(192.168.17.178, 192.168.17.179)，在各自服務器上執行命令
service mysql stop
2）由于關閉Management nodes時會自動關閉data node，只需在Management nodes（192.168.17.177）執行命令
ndb_mgm -e shutdown
5、測試比較
在另一臺相同配置的虛擬機192.168.1.181上安裝獨立版本的mysql 5.6.28，下邊是測試比較結果。截圖上邊為mysql cluster測試結果，下圖為mysql結果，測試工具為jmeter2.13
jmeter JDBC連接配置
線程數從1000開始，線程組配置如下
線程數為1000時插入測試
線程數為5000時插入測試
線程數為10000時插入測試
線程數為15000時插入測試
線程數為1000時查詢測試
線程數為5000時查詢測試
線程數為10000時查詢測試
線程數為15000時查詢測試
參考文檔
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-install-linux-rpm.html