MySQL 集群

原创 2013年12月22日 09:55:23

一 MySQL集群简介


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

 

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

 

二 MySQL集群示意图


      

图片来源: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


 

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)


第七步,ndb02ndb01执行相同的操作,如下:

[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



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


katoon Sina CSDN
@Wentasy 博文仅供参考,欢迎大家来访。如有错误之处,希望批评指正。原创博文如需转载请注明出处,谢谢 :) [CSDN博客]
版权声明:本文为博主原创文章,未经博主允许不得转载。 举报

相关文章推荐

org.tigris.subversion.javahl.ClientException: Entry already exists解决

环境 MyEclipse 8.6 + SVN1.0.6 问题 提交项目出现如下错误: org.tigris.subversion.javahl.ClientException:Entry ...

MySQL 读写分离

MySQL Proxy最强大的一项功能是实现“读写分离(Read/Write Splitting)”。基本的原理是让主数据库处理事务性查询,而从数据库处理SELECT查询。数据库复制被用来把事务性查询...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

SQL SERVER 查自动本月,本年,本日的数据条件

select record_date, * from staff where datediff(month,record_date,getdate()) record_date是要查的日期 ...

sql处理百万级以上的数据提高查询速度的方法

处理百万级以上的数据提高查询速度的方法:  1.应尽量避免在 where 子句中使用!=或  2.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立...

吐血整理 Delphi系列书籍 118本(全)

Delphi 教程 系列书籍 网友(老帅)整理 001_《Delhpi6数据库设计思想与实践》 002_《Delphi6应用开发指南》 003_《Delphi6开发人员指南》 004_《Del...

[MSSQL]数据库全局变量

在开启服务器的时候,开始正常,越来越慢,最后只能用造成"僵尸"状态,有可能使数据库的配置不合理. 后来检查了数据连接数目: 发现为32672已经很大了 但是如果对于一个企业来讲,建立的连接数目有可能...

[MSSQL]SQL数据库关键字之group by 小结

1、解释:Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表 2、常见用...

SQL Server DBA调优日记(一)——大数据量查询记录数优化及原理探讨

SQL Server DBA调优日记第一篇,大数据量查询记录数优化及原理探讨。现象、解决、模拟、原理探讨。

[MSSQL]将查询结果赋值给变量时使用set与select的区别

/****************** 场景布置 ******************/ DROP TABLE # CREATE TABLE #(OrderId INT) INSERT #(Or...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)