一 MySQL集群簡介
上一篇文章我們提到MySQL 讀寫分離，這篇文章我們講解MySQL集群。我們提到的集群，是指多台機器完成一個工作，最主要的場景是數據庫服務器和Web服務器，但是集群環境不適合大規模計算。前面我們有提到MySQL AB復制，因為MySQL AB復制不適合大規模運用，要解決這個問題，我們可以使用MySQL集群。
MySQL集群分為三類節點：管理節點、SQL節點、存儲節點。管理節點的功能是管理其他節點，負責調度不同的SQL節點和存儲節點。SQL節點作用是用戶和該節點進行交互，用戶發送SQL語句到該節點，進行讀寫請求。存儲節點負責到磁盤中讀數據和寫數據。MySQL集群中采用一種特殊存儲引擎，名叫NDB。NDB負責對數據進行讀寫，並保證節點之間的數據一致性，存儲節點沒有必要使用共享存儲，因為第一存儲節點本身的數據互為鏡像，本身已經對數據做了備份。其中，管理節點只需要一個，SQL節點根據業務需要可以有多個，存儲節點同理。
二 MySQL集群示意圖
MySQL集群示意圖
圖片來源：MySQL
三 使用MySQL集群的優劣
3.1 優勢
•處理業務能力大幅提高；
•用戶關注的點更集中於業務；
•數據不易丟失，因為存儲節點對數據做備份。當然不要完全依靠MySQL集群，制定合理的備份和恢復策略還是很有必要的；
•在SQL節點有多台的情況下，一台SQL節點宕機不影響，只需要開發人員手動判斷該節點是否在線，不在線切換到另一台SQL節點上，保證了高可用性。
3.2 劣勢
•成本提高，因為MySQL集群至少需要三台服務器；
•運維難度增強，因為服務器數量增加。
四 搭建MySQL環境
4.1 實驗環境簡介
屬性
IP地址
主機名
管理節點 192.168.1.11 mgmd
存儲節點 192.168.1.14 ndb01
存儲節點 192.168.1.15 ndb02
SQL節點 192.168.1.12 sql01
SQL節點 192.168.1.13 sql02
MySQL集群網絡拓撲圖
MySQL集群網絡拓撲圖
4.2 操作系統版本
RHEL Server6.1 64位系統
4.3 使用到的軟件包版本
mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz
4.4 准備工作
第一步，拷貝文件。
scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.11:/opt/
scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.12:/opt/
scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.13:/opt/
scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.14:/opt/
scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.15:/opt/
第二步，修改主機名。
# serv01
hostname mgmd.host.com
vim /etc/sysconfig/network
cat !$
cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=mgmd.host.com
hostname
mgmd.host.com
# serv02
hostname sql01.host.com
vim /etc/sysconfig/network
cat !$
cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=sql01.host.com
hostname
sql01.host.com
# serv03
hostname sql02.host.com
vim /etc/sysconfig/network
cat !$
cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=sql02.host.com
hostname
sql02.host.com
# serv04
hostname ndb01.host.com
vim /etc/sysconfig/network
cat !$
cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=ndb01.host.com
hostname
ndb01.host.com
# serv05
hostname ndb02.host.com
vim /etc/sysconfig/network
cat !$
cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=ndb02.host.com
hostname
ndb02.host.com
第三步，確定IP地址。
# mgmd
ifconfig | grep eth -A1
eth0 Link encap:Ethernet HWaddr 00:0C:29:07:DD:3B
inet addr:192.168.1.11 Bcast:192.168.1.255 Mask:255.255.255.0
# sql01
ifconfig | grep eth -A1
eth0 Link encap:Ethernet HWaddr 00:0C:29:6A:EC:97
inet addr:192.168.1.12 Bcast:192.168.1.255 Mask:255.255.255.0
# sql02
ifconfig | grep eth -A1
eth0 Link encap:Ethernet HWaddr 00:0C:29:BD:08:05
inet addr:192.168.1.13 Bcast:192.168.1.255 Mask:255.255.255.0
# ndb01
ifconfig | grep eth -A1
eth0 Link encap:Ethernet HWaddr 00:0C:29:0F:1A:09
inet addr:192.168.1.14 Bcast:192.168.1.255 Mask:255.255.255.0
# ndb02
ifconfig | grep eth -A1
eth0 Link encap:Ethernet HWaddr 00:0C:29:77:CB:2F
inet addr:192.168.1.15 Bcast:192.168.1.255 Mask:255.255.255.0
4.5 管理節點搭建
第一步，添加mysql組和用戶。
groupadd -g 27 mysql
useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql
id mysql
第二步，解壓二進制包。
tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/
cd /usr/local/
第三步，重命名安裝目錄，修改所有者和所屬組。
mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql
chown mysql. mysql/ -R
ll mysql/ -d
ll mysql/
第四步，拷貝配置文件，編輯該文件。
cp /usr/local/mysql/support-files/ndb-config-2-node.ini /etc/ndb-config.ini
vim /etc/ndb-config.ini
cat /etc/ndb-config.ini
腳本內容如下。
# Example Ndbcluster storage engine config file.
#
[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentOperations= 10000
DataMemory= 80M
IndexMemory= 24M
TimeBetweenWatchDogCheck= 30000
DataDir= /var/lib/mysql-cluster
MaxNoOfOrderedIndexes= 512
[ndb_mgmd default]
# 確定該目錄存在
DataDir= /var/lib/mysql-cluster
[ndb_mgmd]
Id=1
HostName= 192.168.1.11
[ndbd]
Id= 2
HostName= 192.168.1.14
[ndbd]
Id= 3
HostName= 192.168.1.15
[mysqld]
Id= 4
HostName= 192.168.1.12
[mysqld]
Id= 5
HostName= 192.168.1.13
# choose an unused port number
# in this configuration 63132, 63133, and 63134
# will be used
[tcp default]
PortNumber= 63132
第五步，創建數據目錄，修改所有者和所屬組。
mkdir /var/lib/mysql-cluster
chown mysql. /var/lib/mysql-cluster/ -R
ll -d /var/lib/mysql-cluster/
第六步，啟動ndb_mgmd。
/usr/local/mysql/bin/ndb_mgmd -f /etc/ndb-config.ini
2013-11-04 23:46:12 [MgmtSrvr] INFO --
NDB Cluster Management Server. mysql-5.1.44 ndb-7.1.4b
2013-11-04 23:46:12 [MgmtSrvr] INFO --
The default config directory '/usr/local/mysql/mysql-cluster' does not exist.
Trying to create it...
2013-11-04 23:46:12 [MgmtSrvr] INFO --
Sucessfully created config directory
2013-11-04 23:46:12 [MgmtSrvr] INFO --
Reading cluster configuration from '/etc/ndb-config.ini'
2013-11-04 23:46:12 [MgmtSrvr] WARNING --
at line 39: [tcp] PortNumber is depricated, use Port used for this transporter instead
2013-11-04 23:46:13 [MgmtSrvr] INFO --
Reading cluster configuration from '/etc/ndb-config.ini'
2013-11-04 23:46:13 [MgmtSrvr] WARNING --
at line 39: [tcp] PortNumber is depricated, use Port used for this transporter instead
確定進程和端口號。
ps -ef | grep mgm | grep -v grep
netstat -langput | grep mgm
查看狀態。
/usr/local/mysql/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.1.14)
id=3 (not connected, accepting connect from 192.168.1.15)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.11 (mysql-5.1.44 ndb-7.1.4)
[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from 192.168.1.12)
id=5 (not connected, accepting connect from 192.168.1.13)
4.6 存儲節點搭建
第一步，ndb01添加mysql組和用戶。
groupadd -g 27 mysql
useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql
id mysql
cd /opt/
第二步，ndb01解壓二進制包。
tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/
cd /usr/local/
第三步，ndb01重命名安裝目錄，修改所有者和所屬組。
mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql
chown mysql. mysql/ -R
ll -d mysql/
ll mysql/
第四步，ndb01進入mysql目錄，拷貝模板文件，修改該文件，
cd mysql/
cp support-files/my-medium.cnf /etc/my.cnf
cp: overwrite `/etc/my.cnf'? y
vim /etc/my.cnf
配置文件內容如下。
grep "^#\|^$" /etc/my.cnf -v
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysql_cluster]
ndb_connectstring=192.168.1.11
# 下劃線或者橫線都可以
# ndb-connectstring=192.168.1.11
第五步，ndb01創建數據目錄，修改所有者和所屬組。
mkdir /var/lib/mysql-cluster
chown mysql.mysql !$ -R
ll -d /var/lib/mysql-cluster/
第六步，ndb01初始化ndbd。
/usr/local/mysql/bin/ndbd --initial
2013-11-04 23:57:06 [ndbd] INFO -- Configuration fetched from
'192.168.1.11:1186', generation: 1
查看狀態。
/usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.1.11:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.1.14 (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0)
id=3 (not connected, accepting connect from 192.168.1.15)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.11 (mysql-5.1.44 ndb-7.1.4)
[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from 192.168.1.12)
id=5 (not connected, accepting connect from 192.168.1.13)
第七步，ndb02和ndb01執行相同的操作，如下：
groupadd -g 27 mysql
useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql
id mysql
tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/
cd /usr/local/
mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql
chown mysql. mysql/ -R
ll -d mysql/
ll mysql/
mkdir /var/lib/mysql-cluster
chown mysql. /var/lib/mysql-cluster/ -R
ll -d /var/lib/mysql-cluster/
cd mysql/
cp support-files/my-medium.cnf /etc/my.cnf
cp: overwrite `/etc/my.cnf'? y
vim /etc/my.cnf
grep "^#\|^$" !$ -v
grep "^#\|^$" /etc/my.cnf -v
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysql_cluster]
ndb_connectstring=192.168.1.11
/usr/local/mysql/bin/ndbd --initial
2013-11-05 00:02:18 [ndbd] INFO -- Configuration fetched from
'192.168.1.11:1186', generation: 1
[root@ndb02 mysql]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.1.11:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.1.14 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0, Master)
id=3 @192.168.1.15 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.11 (mysql-5.1.44 ndb-7.1.4)
[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from 192.168.1.12)
id=5 (not connected, accepting connect from 192.168.1.13)
4.7 SQL節點搭建
第一步，sql01添加mysql組和用戶。
groupadd -g 27 mysql
useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql
id mysql
cd /opt/
第二步，sql01解壓二進制包。
tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/
第三步，sql01重命名安裝目錄，修改所有者和所屬組。
cd /usr/local/
mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql
chown mysql. mysql/ -R
ll -d mysql/
ll mysql/
第四步，sql01拷貝配置文件，並修改。
cd mysql/
cp support-files/my-medium.cnf /etc/my.cnf
cp: overwrite `/etc/my.cnf'? y
vim /etc/my.cnf
修改腳本如下。
grep "^#\|^$" !$ -v
grep "^#\|^$" /etc/my.cnf -v
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
datadir=/var/lib/mysql-cluster
ndbcluster
default-storage-engine=ndbcluster
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysql_cluster]
ndb_connectstring=192.168.1.11
第五步，sql01拷貝運行腳本，添加可執行權限。
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
第六步，sql01創建數據目錄，修改所有者和所屬組。
mkdir /var/lib/mysql-cluster
chown mysql. !$ -R
ll -d /var/lib/mysql-cluster/
第七步，sql01初始化數據庫。
/usr/local/mysql/scripts/mysql_install_db --user=mysql
[root@sql01 mysql]#
第八步，sql01啟動mysql，並加入ndb
/etc/init.d/mysqld start
Starting MySQL. SUCCESS!
/usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.1.11:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.1.14 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0, Master)
id=3 @192.168.1.15 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.11 (mysql-5.1.44 ndb-7.1.4)
[mysqld(API)] 2 node(s)
id=4 @192.168.1.12 (mysql-5.1.44 ndb-7.1.4)
id=5 (not connected, accepting connect from 192.168.1.13)
第九步，sql02執行和sql01相同的操作，如下：
groupadd -g 27 mysql
useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql
id mysql
cd /opt/
tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/
cd !$
mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql
chown mysql. mysql/ -R
ll -d mysql/
ll mysql/
mkdir /var/lib/mysql-cluster
chown mysql. /var/lib/mysql-cluster -R
ll -d /var/lib/mysql-cluster/
cd mysql/
cp support-files/my-medium.cnf /etc/my.cnf
cp: overwrite `/etc/my.cnf'? y
vim /etc/my.cnf
rep "^#\|^$" /etc/my.cnf -v
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
datadir=/var/lib/mysql-cluster
ndbcluster
default-storage-engine=ndbcluster
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysql_cluster]
ndb_connectstring=192.168.1.11
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x !$
/usr/local/mysql/scripts/mysql_install_db --user=mysql
/etc/init.d/mysqld start
Starting MySQL. SUCCESS!
/usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.1.11:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.1.14 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0, Master)
id=3 @192.168.1.15 (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.11 (mysql-5.1.44 ndb-7.1.4)
[mysqld(API)] 2 node(s)
id=4 @192.168.1.12 (mysql-5.1.44 ndb-7.1.4)
id=5 @192.168.1.13 (mysql-5.1.44 ndb-7.1.4)
4.8 測試
第一步，sql02創建測試數據庫，sql01可以發現。
# sql02
/usr/local/mysql/bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.44-ndb-7.1.4b-cluster-gpl-log MySQL Cluster Server (GPL)
-- sql02
mysql> create database larrydb;
Query OK, 1 row affected (0.21 sec)
--sql01
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| larrydb |
| mysql |
| ndbinfo |
| test |
+--------------------+
5 rows in set (0.00 sec)
第二步，sql01創建測試表，插入測試數據，sql02可以發現變化。
-- sql01
mysql> use larrydb;
Database changed
mysql> create table user(id int,name varchar(30));
Query OK, 0 rows affected (0.62 sec)
mysql> insert into user values(1,'larry');
Query OK, 1 row affected (0.03 sec)
--sql02
mysql> select * from larrydb.user;
+------+-------+
| id | name |
+------+-------+
| 1 | larry |
+------+-------+
1 row in set (0.04 sec)
如果只能使用三台服務器，可以這樣配置：sql節點和ndb節點放在一起。管理節點做如下配置：
# 管理節點
[ndb_mgmd]
Id=1
HostName= 192.168.1.11
[ndbd]
Id= 2
HostName= 192.168.1.14
[ndbd]
Id= 3
HostName= 192.168.1.15
[mysqld]
Id= 4
[mysqld]
Id= 5
# 其他節點做sql節點的配置即可
