MySQL high availability solution Cluster)

MySQL high availability solution Cluster)

1. experiment environment

I used three servers to build the mysql cluster environment. The SQL node and the data node are on the same server, and the Management node is a separate one.

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-bit

 

2. Install mysql cluster on the SQL node and data node (Note: cluster node 1 and node 2 are the same)

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-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 -- database installation path
Datadir =/usr/local/mysql-cluster-gpl-7.3.6/data -- database data directory
Port = 3306
Ndbcluster -- cluster service
Ndb-connectstring = 192.168.1.19 -- the IP address of the Management Node

 

[Mysql_cluster]
Ndb-connectstring = 192.168.1.19 -- the IP address of the Management Node

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

[Root @ node1 bin] #/etc/init. d/mysqld restart
ERROR! MySQL server PID file cocould 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:

Note: The mysql cluster installation on node 2 is the same as that on node 1, and the configuration file is the same.

 

3. manage nodes

[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 -- pay attention to Case sensitivity in the configuration file

[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 -- start the management node service
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. Start cluster nodes and database services

Cluster node1:

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

09:59:20 [ndbd] INFO -- Angel connected to '192. 168.1.19: 100'
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

10:01:24 [ndbd] INFO -- Angel connected to '192. 168.1.19: 100'
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] #

 

Management node viewing information:

[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, Nodegroup: 0 ,*)
Id = 3 @ 192.168.1.104 (mysql-5.6.17, 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 backup and recovery

MySQL Cluster installation Configuration

Create a Cluster environment using three MySQL clusters. DOC

MySQL Cluster7.2 online addition of data nodes has major drawbacks

MySQL Cluster details: click here
MySQL Cluster: click here

This article permanently updates the link address: