MySQL高可用方案之叢集(Cluster)

MySQL高可用方案之叢集(Cluster)

1.實驗環境

我用三台伺服器搭建mysql cluster環境,sql節點和資料節點在同一伺服器上,管理節點單獨一台.

cluster node1:192.168.1.102        data node1:192.168.1.102

cluster node2:192.168.1.104        data node2:192.168.1.104

manager node:192.168.1.19

OS:CentOS 6.4 64位

 

2.在sql節點和資料節點安裝mysql cluster(注:cluster節點1和節點2是一樣的)

cluster node1:

[root@node1 home]# wget http://mirrors.sohu.com/mysql/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar.gz

[root@node1 home]# tar xvf mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar.gz

[root@node1 home]# mv mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64 /usr/local/mysql-cluster-gpl-7.3.6/

[root@node1 home]# cd /usr/local/

[root@node1 local]# groupadd mysql
[root@node1 local]# useradd  -r -M -s /sbin/nologin mysql

[root@node1 local]# chown -R root:mysql mysql-cluster-gpl-7.3.6/
[root@node1 local]# chown -R mysql:mysql mysql-cluster-gpl-7.3.6/data/

[root@node1 scripts]# ./mysql_install_db  --user=mysql --datadir=/usr/local/mysql-cluster-gpl-7.3.6/data --basedir=/usr/local/mysql-cluster-gpl-7.3.6

[root@node1 scripts]# cd ../bin/

[root@node1 bin]# cp -a ../support-files/mysql.server  /etc/init.d/mysqld

[root@node1 bin]# chkconfig  --add mysqld

[root@node1 bin]# chkconfig  mysqld on

[root@node1 bin]# cp -a ../my.cnf  /etc/my.cnf

[root@node1 bin]# vim /etc/my.cnf

[mysqld]

basedir = /usr/local/mysql-cluster-gpl-7.3.6                      --資料庫的安裝路徑
datadir = /usr/local/mysql-cluster-gpl-7.3.6/data              --資料庫的資料目錄
port = 3306
ndbcluster            --叢集服務
ndb-connectstring=192.168.1.19          --管理節點的IP地址

 

[mysql_cluster]
ndb-connectstring=192.168.1.19          --管理節點的IP地址

[root@node1 bin]# ./mysqld_safe --user=mysql

[root@node1 bin]# /etc/init.d/mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL...................................... SUCCESS!
[root@node1 bin]# netstat -anp |grep mysql
tcp        0      0 :::3306                    :::*                        LISTEN      1982/mysqld       
unix  2      [ ACC ]    STREAM    LISTENING    48481  1982/mysqld        /tmp/mysql.sock[root@node1 bin]# /etc/init.d/iptables stop

iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                        [  OK  ]
iptables: Unloading modules:                              [  OK  ]
[root@node1 bin]#

 

cluster node2:

注:節點2上的mysql cluster安裝和節點1是一樣的,設定檔也是一樣

 

3.管理節點

[root@db2 home]# wget http://mirrors.sohu.com/mysql/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar.gz

[root@db2 home]# tar xvf mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar.gz

[root@db2 home]#  mv mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64 /usr/local/mysql-cluster-gpl-7.3.6/

[root@db2 home]# cd /usr/local/

[root@db2 local]# groupadd mysql
[root@db2 local]# useradd  -r -M -s /sbin/nologin mysql

[root@db2 local]# chown -R root:mysql mysql-cluster-gpl-7.3.6/
[root@db2 local]# chown -R mysql:mysql mysql-cluster-gpl-7.3.6/data/

[root@db2 scripts]# ./mysql_install_db  --user=mysql --datadir=/usr/local/mysql-cluster-gpl-7.3.6/data --basedir=/usr/local/mysql-cluster-gpl-7.3.6

[root@db2 scripts]# cd ../bin/

[root@db2 bin]# cp -a ../my.cnf  /etc/my.cnf

[root@db2 bin]# vim /etc/my.cnf

 [mysqld]

basedir = /usr/local/mysql-cluster-gpl-7.3.6
datadir = /usr/local/mysql-cluster-gpl-7.3.6/data
port = 3306

[root@db2 home]# /etc/init.d/iptables stop
iptables: Flushing firewall rules:                        [  OK  ]
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Unloading modules:                              [  OK  ]

[root@db2 home]# mkdir  /var/lib/mysql-cluster/

[root@db2 home]# vim  /var/lib/mysql-cluster/config.ini    --設定檔中注意大小寫

[ndbd default]
NoOfReplicas=1

[tcp default]
portnumber=2202

[ndb_mgmd]
Id=1
Hostname=192.168.1.19
datadir=/usr/local/mysql-cluster-gpl-7.3.6/data

[ndbd]
Id=2
Hostname=192.168.1.102
datadir=/usr/local/mysql-cluster-gpl-7.3.6/data/

[ndbd]
Id=3
Hostname=192.168.1.104
datadir=/usr/local/mysql-cluster-gpl-7.3.6/data/

[MYSQLD]
Id=4
Hostname=192.168.1.102

[MYSQLD]
Id=5
Hostname=192.168.1.104

[root@db2 bin]# ./ndb_mgmd --configdir=/var/lib/mysql-cluster/ -f /var/lib/mysql-cluster/config.ini  --initial  --啟動管理節點服務
MySQL Cluster Management Server mysql-5.6.17 ndb-7.3.6

[root@db2 bin]# netstat -anp | grep ndb
tcp        0      0 0.0.0.0:1186                0.0.0.0:*                  LISTEN      3569/ndb_mgmd     
tcp        0      0 192.168.1.105:1186        192.168.1.102:45799        ESTABLISHED 3569/ndb_mgmd     
tcp        0      0 192.168.1.105:1186        192.168.1.104:46933        ESTABLISHED 3569/ndb_mgmd     
tcp        0      0 127.0.0.1:1186              127.0.0.1:55347            ESTABLISHED 3569/ndb_mgmd     
tcp        0      0 192.168.1.105:1186        192.168.1.102:45800        ESTABLISHED 3569/ndb_mgmd     
tcp        0      0 192.168.1.105:1186        192.168.1.102:45801        ESTABLISHED 3569/ndb_mgmd     
tcp        0      0 127.0.0.1:55347            127.0.0.1:1186              ESTABLISHED 3569/ndb_mgmd     
unix  3      [ ]        STREAM    CONNECTED    20977  3569/ndb_mgmd     
unix  3      [ ]        STREAM    CONNECTED    20976  3569/ndb_mgmd     
[root@db2 bin]#

 

4.啟動cluster節點和資料庫服務

cluster node1:

[root@node1 bin]# ./ndbd --initial

2014-10-10 09:59:20 [ndbd] INFO    -- Angel connected to '192.168.1.19:1186'
2014-10-10 09:59:20 [ndbd] INFO    -- Angel allocated nodeid: 2

[root@node1 bin]# ../support-files/mysql.server start
Starting MySQL SUCCESS!
[root@node1 bin]#

 

cluster node2:

[root@node2 bin]# ./ndbd --initial

2014-10-10 10:01:24 [ndbd] INFO    -- Angel connected to '192.168.1.19:1186'
2014-10-10 10:01:24 [ndbd] INFO    -- Angel allocated nodeid: 3

[root@node2 bin]# ../support-files/mysql.server start
Starting MySQL SUCCESS!
[root@node2 bin]#

 

管理節點查看資訊:

[root@db2 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 @192.168.1.102  (mysql-5.6.17 ndb-7.3.6, Nodegroup: 0, *)
id=3 @192.168.1.104  (mysql-5.6.17 ndb-7.3.6, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.19  (mysql-5.6.17 ndb-7.3.6)

[mysqld(API)] 2 node(s)
id=4 @192.168.1.102  (mysql-5.6.17 ndb-7.3.6)
id=5 @192.168.1.102  (mysql-5.6.17 ndb-7.3.6)

ndb_mgm>

MySQL Cluster 備份與恢複

MySQL Cluster安裝配置

MySQL Cluster 3台機器搭建叢集環境 DOC

MySQL Cluster7.2線上增加資料節點存在重大弊端

MySQL Cluster 的詳細介紹：請點這裡
MySQL Cluster 的：請點這裡

本文永久更新連結地址：