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

不念不忘少年蓝@ 2022-08-05 08:58 235阅读 0赞

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][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][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 备份与恢复 [ http://www.linuxidc.com/Linux/2013-06/85295.htm][http_www.linuxidc.com_Linux_2013-06_85295.htm]

MySQL Cluster安装配置 [ http://www.linuxidc.com/Linux/2013-07/87705.htm][http_www.linuxidc.com_Linux_2013-07_87705.htm]

MySQL Cluster 3台机器搭建集群环境 DOC [ http://www.linuxidc.com/Linux/2013-01/78249.htm][http_www.linuxidc.com_Linux_2013-01_78249.htm]

MySQL Cluster7.2在线增加数据节点存在重大弊端 [ http://www.linuxidc.com/Linux/2012-08/67605.htm][http_www.linuxidc.com_Linux_2012-08_67605.htm]

MySQL Cluster 的详细介绍：请点这里  
MySQL Cluster 的下载地址：请点这里

本文永久更新链接地址：[http://www.linuxidc.com/Linux/2014-10/107887.htm][http_www.linuxidc.com_Linux_2014-10_107887.htm]

本篇文章来源于 Linux公社网站([www.linuxidc.com][])  原文链接：[http://www.linuxidc.com/Linux/2014-10/107887.htm][http_www.linuxidc.com_Linux_2014-10_107887.htm]

[http_mirrors.sohu.com_mysql_MySQL-Cluster-7.3_mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar.gz]: http://mirrors.sohu.com/mysql/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar.gz
[http_www.linuxidc.com_Linux_2013-06_85295.htm]: http://www.linuxidc.com/Linux/2013-06/85295.htm
[http_www.linuxidc.com_Linux_2013-07_87705.htm]: http://www.linuxidc.com/Linux/2013-07/87705.htm
[http_www.linuxidc.com_Linux_2013-01_78249.htm]: http://www.linuxidc.com/Linux/2013-01/78249.htm
[http_www.linuxidc.com_Linux_2012-08_67605.htm]: http://www.linuxidc.com/Linux/2012-08/67605.htm
[http_www.linuxidc.com_Linux_2014-10_107887.htm]: http://www.linuxidc.com/Linux/2014-10/107887.htm
[www.linuxidc.com]: http://www.linuxidc.com