MySQL 集群

今天药忘吃喽~ 2022-09-19 10:40 199阅读 0赞

## 一 MySQL集群简介 ##

上一篇文章我们提到[MySQL 读写分离][MySQL]，这篇文章我们讲解MySQL集群。我们提到的集群，是指多台机器完成一个工作，最主要的场景是数据库服务器和Web服务器，但是集群环境不适合大规模计算。前面我们有提到[MySQL AB复制][MySQL AB]，因为MySQL AB复制不适合大规模运用，要解决这个问题，我们可以使用MySQL集群。

MySQL集群分为三类节点：管理节点、SQL节点、存储节点。管理节点的功能是管理其他节点，负责调度不同的SQL节点和存储节点。SQL节点作用是用户和该节点进行交互，用户发送SQL语句到该节点，进行读写请求。存储节点负责到磁盘中读数据和写数据。MySQL集群中采用一种特殊存储引擎，名叫NDB。NDB负责对数据进行读写，并保证节点之间的数据一致性，存储节点没有必要使用共享存储，因为第一存储节点本身的数据互为镜像，本身已经对数据做了备份。其中，管理节点只需要一个，SQL节点根据业务需要可以有多个，存储节点同理。

## 二 MySQL集群示意图 ##

![20131222093521656][]

图片来源：[http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-overview.html][http_dev.mysql.com_doc_refman_5.1_en_mysql-cluster-overview.html]

## 三 使用MySQL集群的优劣 ##

### 3.1 优势 ###

1.处理业务能力大幅提高；

2.用户关注的点更集中于业务；

3.数据不易丢失，因为存储节点对数据做备份。当然不要完全依靠MySQL集群，制定合理的备份和恢复策略还是很有必要的；

4.在SQL节点有多台的情况下，一台SQL节点宕机不影响，只需要开发人员手动判断该节点是否在线，不在线切换到另一台SQL节点上，保证了高可用性。

### 3.2 劣势 ###

1.成本提高，因为MySQL集群至少需要三台服务器；

2.运维难度增强，因为服务器数量增加。

## 四 搭建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

![SouthEast][]

MySQL集群网络拓扑图

### **4.2 操作系统版本** ###

RHEL Server6.1 64位系统

### **4.3 使用到的软件包版本** ###

mysql-cluster-gpl-7.1.4b-linux-x86\_64-glibc23.tar.gz

### **4.4 准备工作** ###

第一步，拷贝文件

>     [root@larrywen opt]# scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.11:/opt/
>     [root@larrywen opt]# scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.12:/opt/
>     [root@larrywen opt]# scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.13:/opt/
>     [root@larrywen opt]# scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.14:/opt/
>     [root@larrywen opt]# scp mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz 192.168.1.15:/opt/

第二步，修改主机名

>     [root@serv01 ~]# hostname mgmd.host.com
>     [root@serv01 ~]# vim /etc/sysconfig/network
>     [root@serv01 ~]# cat !$
>     cat /etc/sysconfig/network
>     NETWORKING=yes
>     HOSTNAME=mgmd.host.com
>     [root@serv01 ~]# hostname 
>     mgmd.host.com
>     
>     [root@serv02 ~]# hostname sql01.host.com
>     [root@serv02 ~]# vim /etc/sysconfig/network
>     [root@serv02 ~]# cat !$
>     cat /etc/sysconfig/network
>     NETWORKING=yes
>     HOSTNAME=sql01.host.com
>     [root@serv02 ~]# hostname 
>     sql01.host.com
>     
>     [root@serv03 ~]# hostname sql02.host.com
>     [root@serv03 ~]# vim /etc/sysconfig/network
>     [root@serv03 ~]# cat !$
>     cat /etc/sysconfig/network
>     NETWORKING=yes
>     HOSTNAME=sql02.host.com
>     [root@serv03 ~]# hostname
>     sql02.host.com
>     
>     [root@serv04 ~]# hostname ndb01.host.com
>     [root@serv04 ~]# vim /etc/sysconfig/network
>     [root@serv04 ~]# cat !$
>     cat /etc/sysconfig/network
>     NETWORKING=yes
>     HOSTNAME=ndb01.host.com
>     [root@serv04 ~]# hostname 
>     ndb01.host.com
>     
>     [root@serv05 ~]# hostname ndb02.host.com
>     [root@serv05 ~]# vim /etc/sysconfig/network
>     [root@serv05 ~]# cat !$
>     cat /etc/sysconfig/network
>     NETWORKING=yes
>     HOSTNAME=ndb02.host.com
>     [root@serv05 ~]# hostname 
>     ndb02.host.com

第三步，确定IP地址

>     [root@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
>     
>     [root@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
>     
>     [root@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
>     
>     [root@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
>     
>     [root@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组和用户

>     [root@mgmd opt]# groupadd -g 27 mysql
>     [root@mgmd opt]# useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql
>     [root@mgmd opt]# id mysql
>     uid=27(mysql) gid=27(mysql) groups=27(mysql)

第二步，解压二进制包

>     [root@mgmd opt]# tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/
>     [root@mgmd opt]# cd /usr/local/

第三步，重命名安装目录，修改所有者和所属组

>     [root@mgmd local]# mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql
>     [root@mgmd local]# chown mysql. mysql/ -R
>     [root@mgmd local]# ll mysql/ -d
>     drwxr-xr-x 13 mysql mysql 4096 Jun 11  2010 mysql/
>     [root@mgmd local]# ll mysql/
>     total 244
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 bin
>     -rw-r--r--  1 mysql mysql  19071 Jun 11  2010 COPYING
>     drwxr-x---  4 mysql mysql   4096 Jun 11  2010 data
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 docs
>     -rw-r--r--  1 mysql mysql   5139 Jun 11  2010 EXCEPTIONS-CLIENT
>     drwxr-xr-x  3 mysql mysql   4096 Jun 11  2010 include
>     -rw-r--r--  1 mysql mysql   9748 Jun 11  2010 INSTALL-BINARY
>     drwxr-xr-x  3 mysql mysql   4096 Jun 11  2010 lib
>     drwxr-xr-x  4 mysql mysql   4096 Jun 11  2010 man
>     drwxr-xr-x 10 mysql mysql   4096 Jun 11  2010 mysql-test
>     -rw-r--r--  1 mysql mysql 161295 Jun 11  2010 README
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 scripts
>     drwxr-xr-x 28 mysql mysql   4096 Jun 11  2010 share
>     drwxr-xr-x  5 mysql mysql   4096 Jun 11  2010 sql-bench
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 support-files

第四步，拷贝配置文件，编辑该文件，内容如下

>     [root@mgmd local]# cp /usr/local/mysql/support-files/ndb-config-2-node.ini /etc/ndb-config.ini
>     [root@mgmd local]# vim /etc/ndb-config.ini 
>     [root@mgmd local]# 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

第五步，创建数据目录，修改所有者和所属组

>     [root@mgmd local]# mkdir /var/lib/mysql-cluster
>     [root@mgmd local]# chown mysql. /var/lib/mysql-cluster/ -R
>     [root@mgmd local]# ll -d /var/lib/mysql-cluster/
>     drwxr-xr-x 2 mysql mysql 4096 Nov  4 23:39 /var/lib/mysql-cluster/

第六步，启动ndb\_mgmd

>     [root@mgmd local]# /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
>     
>     --确定进程和端口号
>     [root@mgmd local]# ps -ef | grep mgm | grep -v grep
>     root      1070     1  0 23:46 ?        00:00:00 /usr/local/mysql/bin/ndb_mgmd -f /etc/ndb-config.ini
>     [root@mgmd local]# netstat -langput | grep mgm
>     tcp        0      0 0.0.0.0:1186                0.0.0.0:*                   LISTEN      1070/ndb_mgmd       
>     tcp        0      0 127.0.0.1:1186              127.0.0.1:55252             ESTABLISHED 1070/ndb_mgmd       
>     tcp        0      0 127.0.0.1:55252             127.0.0.1:1186              ESTABLISHED 1070/ndb_mgmd    
>     
>     [root@mgmd local]# /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组和用户

>     [root@ndb01 ~]# groupadd -g 27 mysql
>     [root@ndb01 ~]# useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql
>     [root@ndb01 ~]# id mysql
>     uid=27(mysql) gid=27(mysql) groups=27(mysql)
>     [root@ndb01 ~]# cd /opt/

第二步，ndb01解压二进制包

>     [root@ndb01 opt]# tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/
>     [root@ndb01 opt]# cd /usr/local/

第三步，ndb01重命名安装目录，修改所有者和所属组

>     [root@ndb01 local]# mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql
>     [root@ndb01 local]# chown mysql. mysql/ -R
>     [root@ndb01 local]# ll -d mysql/
>     drwxr-xr-x 13 mysql mysql 4096 Jun 11  2010 mysql/
>     [root@ndb01 local]# ll mysql/
>     total 244
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 bin
>     -rw-r--r--  1 mysql mysql  19071 Jun 11  2010 COPYING
>     drwxr-x---  4 mysql mysql   4096 Jun 11  2010 data
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 docs
>     -rw-r--r--  1 mysql mysql   5139 Jun 11  2010 EXCEPTIONS-CLIENT
>     drwxr-xr-x  3 mysql mysql   4096 Jun 11  2010 include
>     -rw-r--r--  1 mysql mysql   9748 Jun 11  2010 INSTALL-BINARY
>     drwxr-xr-x  3 mysql mysql   4096 Jun 11  2010 lib
>     drwxr-xr-x  4 mysql mysql   4096 Jun 11  2010 man
>     drwxr-xr-x 10 mysql mysql   4096 Jun 11  2010 mysql-test
>     -rw-r--r--  1 mysql mysql 161295 Jun 11  2010 README
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 scripts
>     drwxr-xr-x 28 mysql mysql   4096 Jun 11  2010 share
>     drwxr-xr-x  5 mysql mysql   4096 Jun 11  2010 sql-bench
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 support-files

第四步，ndb01进入mysql目录，拷贝模板文件，修改该文件，内容如下

>     [root@ndb01 local]# cd mysql/
>     [root@ndb01 mysql]# cp support-files/my-medium.cnf /etc/my.cnf 
>     cp: overwrite `/etc/my.cnf'? y
>     [root@ndb01 mysql]# vim /etc/my.cnf 
>     [root@ndb01 mysql]# 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创建数据目录，修改所有者和所属组

>     [root@ndb01 mysql]# mkdir /var/lib/mysql-cluster
>     [root@ndb01 mysql]# chown mysql.mysql !$ -R
>     chown mysql.mysql /var/lib/mysql-cluster -R
>     [root@ndb01 mysql]# ll -d /var/lib/mysql-cluster/
>     drwxr-xr-x 2 mysql mysql 4096 Nov  4 23:54 /var/lib/mysql-cluster/

第六步，ndb01初始化ndbd

>     [root@ndb01 mysql]# /usr/local/mysql/bin/ndbd --initial
>     2013-11-04 23:57:06 [ndbd] INFO     -- Configuration fetched from '192.168.1.11:1186', generation: 1
>     
>     #查看状态
>     [root@ndb01 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, 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执行相同的操作，如下：

>     [root@ndb02 ~]# groupadd -g 27 mysql
>     [root@ndb02 ~]# useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql
>     [root@ndb02 ~]# id mysql
>     uid=27(mysql) gid=27(mysql) groups=27(mysql)
>     [root@ndb02 opt]# tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/ 
>     
>     [root@ndb02 opt]# cd /usr/local/
>     [root@ndb02 local]# mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql
>     [root@ndb02 local]# chown mysql. mysql/ -R
>     [root@ndb02 local]# ll -d mysql/
>     drwxr-xr-x 13 mysql mysql 4096 Jun 11  2010 mysql/
>     [root@ndb02 local]# ll mysql/
>     total 244
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 bin
>     -rw-r--r--  1 mysql mysql  19071 Jun 11  2010 COPYING
>     drwxr-x---  4 mysql mysql   4096 Jun 11  2010 data
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 docs
>     -rw-r--r--  1 mysql mysql   5139 Jun 11  2010 EXCEPTIONS-CLIENT
>     drwxr-xr-x  3 mysql mysql   4096 Jun 11  2010 include
>     -rw-r--r--  1 mysql mysql   9748 Jun 11  2010 INSTALL-BINARY
>     drwxr-xr-x  3 mysql mysql   4096 Jun 11  2010 lib
>     drwxr-xr-x  4 mysql mysql   4096 Jun 11  2010 man
>     drwxr-xr-x 10 mysql mysql   4096 Jun 11  2010 mysql-test
>     -rw-r--r--  1 mysql mysql 161295 Jun 11  2010 README
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 scripts
>     drwxr-xr-x 28 mysql mysql   4096 Jun 11  2010 share
>     drwxr-xr-x  5 mysql mysql   4096 Jun 11  2010 sql-bench
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 support-files
>     
>     [root@ndb02 local]# mkdir /var/lib/mysql-cluster
>     [root@ndb02 local]# chown mysql. /var/lib/mysql-cluster/ -R
>     [root@ndb02 local]# ll -d /var/lib/mysql-cluster/
>     drwxr-xr-x 2 mysql mysql 4096 Nov  5 00:00 /var/lib/mysql-cluster/
>     
>     [root@ndb02 local]# cd mysql/
>     [root@ndb02 mysql]# cp support-files/my-medium.cnf /etc/my.cnf 
>     cp: overwrite `/etc/my.cnf'? y
>     [root@ndb02 mysql]# vim /etc/my.cnf 
>     [root@ndb02 mysql]# 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
>     
>     [root@ndb02 mysql]# /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组和用户

>     [root@sql01 ~]# groupadd -g 27 mysql
>     [root@sql01 ~]# useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql
>     [root@sql01 ~]# id mysql
>     uid=27(mysql) gid=27(mysql) groups=27(mysql)
>     [root@sql01 ~]# cd /opt/

第二步，sql01解压二进制包

>     [root@sql01 opt]# tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/

第三步，sql01重命名安装目录，修改所有者和所属组

>     [root@sql01 opt]# cd /usr/local/
>     [root@sql01 local]# mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql
>     [root@sql01 local]# chown mysql. mysql/ -R
>     [root@sql01 local]# ll -d mysql/
>     drwxr-xr-x 13 mysql mysql 4096 Jun 11  2010 mysql/
>     [root@sql01 local]# ll mysql/
>     total 244
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 bin
>     -rw-r--r--  1 mysql mysql  19071 Jun 11  2010 COPYING
>     drwxr-x---  4 mysql mysql   4096 Jun 11  2010 data
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 docs
>     -rw-r--r--  1 mysql mysql   5139 Jun 11  2010 EXCEPTIONS-CLIENT
>     drwxr-xr-x  3 mysql mysql   4096 Jun 11  2010 include
>     -rw-r--r--  1 mysql mysql   9748 Jun 11  2010 INSTALL-BINARY
>     drwxr-xr-x  3 mysql mysql   4096 Jun 11  2010 lib
>     drwxr-xr-x  4 mysql mysql   4096 Jun 11  2010 man
>     drwxr-xr-x 10 mysql mysql   4096 Jun 11  2010 mysql-test
>     -rw-r--r--  1 mysql mysql 161295 Jun 11  2010 README
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 scripts
>     drwxr-xr-x 28 mysql mysql   4096 Jun 11  2010 share
>     drwxr-xr-x  5 mysql mysql   4096 Jun 11  2010 sql-bench
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 support-files

第四步，sql01拷贝配置文件，并修改，如下

>     [root@sql01 local]# cd mysql/
>     [root@sql01 mysql]# cp support-files/my-medium.cnf /etc/my.cnf 
>     cp: overwrite `/etc/my.cnf'? y
>     [root@sql01 mysql]# vim /etc/my.cnf 
>     [root@sql01 mysql]# 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拷贝运行脚本，添加可执行权限

>     [root@sql01 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
>     [root@sql01 mysql]# chmod +x /etc/init.d/mysqld

第六步，sql01创建数据目录，修改所有者和所属组

>     [root@sql01 mysql]# mkdir /var/lib/mysql-cluster
>     [root@sql01 mysql]# chown mysql. !$ -R
>     chown mysql. /var/lib/mysql-cluster -R
>     [root@sql01 mysql]# ll -d /var/lib/mysql-cluster/
>     drwxr-xr-x 2 mysql mysql 4096 Nov  5 00:06 /var/lib/mysql-cluster/

第七步，sql01初始化数据库

>     [root@sql01 mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysql
>     WARNING: The host 'sql01.host.com' could not be looked up with resolveip.
>     This probably means that your libc libraries are not 100 % compatible
>     with this binary MySQL version. The MySQL daemon, mysqld, should work
>     normally with the exception that host name resolving will not work.
>     This means that you should use IP addresses instead of hostnames
>     when specifying MySQL privileges !
>     Installing MySQL system tables...
>     131105  0:07:32 [Warning] The syntax '--skip-locking' is deprecated and will be removed in a future release. Please use --skip-external-locking instead.
>     OK
>     Filling help tables...
>     131105  0:07:32 [Warning] The syntax '--skip-locking' is deprecated and will be removed in a future release. Please use --skip-external-locking instead.
>     OK
>     
>     To start mysqld at boot time you have to copy
>     support-files/mysql.server to the right place for your system
>     
>     PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
>     To do so, start the server, then issue the following commands:
>     
>     ./bin/mysqladmin -u root password 'new-password'
>     ./bin/mysqladmin -u root -h sql01.host.com password 'new-password'
>     
>     Alternatively you can run:
>     ./bin/mysql_secure_installation
>     
>     which will also give you the option of removing the test
>     databases and anonymous user created by default.  This is
>     strongly recommended for production servers.
>     
>     See the manual for more instructions.
>     
>     You can start the MySQL daemon with:
>     cd . ; ./bin/mysqld_safe &
>     
>     You can test the MySQL daemon with mysql-test-run.pl
>     cd ./mysql-test ; perl mysql-test-run.pl
>     
>     Please report any problems with the ./bin/mysqlbug script!

第八步，sql01启动mysql，并加入ndb

>     [root@sql01 mysql]# /etc/init.d/mysqld start
>     Starting MySQL. SUCCESS! 
>     [root@sql01 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  @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相同的操作，如下：

>     [root@sql02 ~]# groupadd -g 27 mysql
>     [root@sql02 ~]# useradd -u 27 -g 27 -r -M -s /sbin/nologin mysql
>     [root@sql02 ~]# id mysql
>     uid=27(mysql) gid=27(mysql) groups=27(mysql)
>     [root@sql02 ~]# cd /opt/
>     [root@sql02 opt]# tar -xvf mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23.tar.gz -C /usr/local/
>     
>     [root@sql02 opt]# cd !$
>     cd /usr/local/
>     [root@sql02 local]# mv mysql-cluster-gpl-7.1.4b-linux-x86_64-glibc23/ mysql
>     [root@sql02 local]# chown mysql. mysql/ -R
>     [root@sql02 local]# ll -d mysql/
>     drwxr-xr-x 13 mysql mysql 4096 Jun 11  2010 mysql/
>     [root@sql02 local]# ll mysql/
>     total 244
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 bin
>     -rw-r--r--  1 mysql mysql  19071 Jun 11  2010 COPYING
>     drwxr-x---  4 mysql mysql   4096 Jun 11  2010 data
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 docs
>     -rw-r--r--  1 mysql mysql   5139 Jun 11  2010 EXCEPTIONS-CLIENT
>     drwxr-xr-x  3 mysql mysql   4096 Jun 11  2010 include
>     -rw-r--r--  1 mysql mysql   9748 Jun 11  2010 INSTALL-BINARY
>     drwxr-xr-x  3 mysql mysql   4096 Jun 11  2010 lib
>     drwxr-xr-x  4 mysql mysql   4096 Jun 11  2010 man
>     drwxr-xr-x 10 mysql mysql   4096 Jun 11  2010 mysql-test
>     -rw-r--r--  1 mysql mysql 161295 Jun 11  2010 README
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 scripts
>     drwxr-xr-x 28 mysql mysql   4096 Jun 11  2010 share
>     drwxr-xr-x  5 mysql mysql   4096 Jun 11  2010 sql-bench
>     drwxr-xr-x  2 mysql mysql   4096 Jun 11  2010 support-files
>     
>     [root@sql02 local]# mkdir /var/lib/mysql-cluster
>     [root@sql02 local]# chown mysql. /var/lib/mysql-cluster -R
>     [root@sql02 local]# ll -d /var/lib/mysql-cluster/
>     drwxr-xr-x 2 mysql mysql 4096 Nov  5 00:10 /var/lib/mysql-cluster/
>     
>     [root@sql02 local]# cd mysql/
>     [root@sql02 mysql]# cp support-files/my-medium.cnf /etc/my.cnf 
>     cp: overwrite `/etc/my.cnf'? y 
>     [root@sql02 mysql]# vim /etc/my.cnf 
>     [root@sql02 mysql]# 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
>     
>     [root@sql02 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
>     [root@sql02 mysql]# chmod +x !$
>     chmod +x /etc/init.d/mysqld
>     [root@sql02 mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysql
>     WARNING: The host 'sql02.host.com' could not be looked up with resolveip.
>     This probably means that your libc libraries are not 100 % compatible
>     with this binary MySQL version. The MySQL daemon, mysqld, should work
>     normally with the exception that host name resolving will not work.
>     This means that you should use IP addresses instead of hostnames
>     when specifying MySQL privileges !
>     Installing MySQL system tables...
>     131105  0:14:20 [Warning] The syntax '--skip-locking' is deprecated and will be removed in a future release. Please use --skip-external-locking instead.
>     OK
>     Filling help tables...
>     131105  0:14:20 [Warning] The syntax '--skip-locking' is deprecated and will be removed in a future release. Please use --skip-external-locking instead.
>     OK
>     
>     To start mysqld at boot time you have to copy
>     support-files/mysql.server to the right place for your system
>     
>     PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
>     To do so, start the server, then issue the following commands:
>     
>     ./bin/mysqladmin -u root password 'new-password'
>     ./bin/mysqladmin -u root -h sql02.host.com password 'new-password'
>     
>     Alternatively you can run:
>     ./bin/mysql_secure_installation
>     
>     which will also give you the option of removing the test
>     databases and anonymous user created by default.  This is
>     strongly recommended for production servers.
>     
>     See the manual for more instructions.
>     
>     You can start the MySQL daemon with:
>     cd . ; ./bin/mysqld_safe &
>     
>     You can test the MySQL daemon with mysql-test-run.pl
>     cd ./mysql-test ; perl mysql-test-run.pl
>     
>     Please report any problems with the ./bin/mysqlbug script!
>     [root@sql02 mysql]# /etc/init.d/mysqld start
>     Starting MySQL. SUCCESS! 
>     
>     [root@sql02 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  @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可以发现

>     [root@sql02 mysql]# /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)
>     
>     Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
>     
>     mysql> create database larrydb;
>     Query OK, 1 row affected (0.21 sec)
>     
>     mysql> 
>     
>     [root@sql01 mysql]# /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)
>     
>     Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
>     
>     mysql> show databases;
>     +--------------------+
>     | Database           |
>     +--------------------+
>     | information_schema |
>     | larrydb            |
>     | mysql              |
>     | ndbinfo            |
>     | test               |
>     +--------------------+
>     5 rows in set (0.00 sec)

第二步，sql01创建测试表，插入测试数据，sql02可以发现变化

>     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节点的配置即可

## 五 参考资料 ##

[http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html][http_dev.mysql.com_doc_refman_5.1_en_mysql-cluster.html]

>     我的邮箱：wgbno27@163.com   新浪微博：@jutdb         
>       微信公众平台：JustOracle（微信号：justoracle）
>       数据库技术交流群：336882565（加群时验证 From CSDN XXX）
>       All is well
>       2013年12月22日
>       By Larry Wen

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a>&nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[MySQL]: http://blog.csdn.net/justdb/article/details/17331569
[MySQL AB]: http://blog.csdn.net/justdb/article/details/13168569
[20131222093521656]: /images/20220828/cacb09ddb1354a0b88f2663c7fcde7cc.png
[http_dev.mysql.com_doc_refman_5.1_en_mysql-cluster-overview.html]: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-overview.html
[SouthEast]: /images/20220828/84d3391334b8442eb1ec5caaf515ee7a.png
[http_dev.mysql.com_doc_refman_5.1_en_mysql-cluster.html]: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html