Dual-machine Redundancy backup and load balancing policy (MySQL cluster getting started installation and Configuration Guide)

MySQL cluster is a highly practical and redundant MySQL version suitable for distributed computing environments. It uses the NDB Cluster Storage engine and allows multiple MySQL servers to run in one cluster. MySQL cluster is a technology that allows you to deploy the "in memory" Database Cluster in a non-shared system. Without a shared architecture, the system can use cheap hardware without special requirements for hardware and software. In addition, since each component has its own memory and disk, there is no single point of failure.

First, download MySQL cluster. The download link is MySQL cluster 7.1.17. We use centos server and Ubuntu server, so we download the Linux generic version.

Now I have three servers, two of which are centos servers and one is Ubuntu server. The first two servers serve as data node and SQL node respectively (data node and SQL node are on the same machine ),

The latter serves as the management node. If there is no real machine, you can simulate it using a virtual machine. below is my IP deployment:

1 192.168.56.10     Data Node + Sql Node
2 192.168.56.20     Data Node +Sql Node
3 192.168.56.30     Management Node

The following figure shows the general topology of my machine:

Next, we need to configure these nodes in detail:

1. configure data node and SQL Node

Currently, we adopt three machine policies, so we can place these two nodes on the same machine, so we can configure them at once.

First, we perform the configuration on the host 192.168.56.10.

We need to execute subordinate operations under the root account. If the command cannot be found in centos, refer to my blogcentos Bash: groupadd: Command not found problem.

Next, create a new user group and user. The command is as follows:

1 groupadd mysql
2 useradd –g mysql mysql

Put the MySQL cluster under/usr/local/and run the following command:

1 tar -C /usr/local -xzvf mysql-cluster-gpl-7.1.17-linux-i686-glibc23.tar.gz
2 ln -s /usr/local/mysql-cluster-gpl-7.1.17-linux-i686-glibc23 /usr/local/mysql

Go to the MySQL directory and run the database initialization command:

1 scripts/mysql_install_db --user=mysql

To change the permission, run the following command:

1 chown -R root .
2 chown -R mysql data
3 chgrp -R mysql .

2. Configure the management Node

Put the ndb_mgmd and ndb_mgm script files in the/usr/local/bin directory and run the following command:

1 tar -zxvf mysql-5.1.56-ndb-7.1.17-linux-i686-glibc23.tar.gz
2 cd mysql-5.1.56-ndb-7.1.17-linux-i686-glibc23
3 cp bin/ndb_mgm* /usr/local/bin

To change the corresponding permissions, run the following command:

1 cd /usr/local/bin
2 chmod +x ndb_mgm*

Create the directory/var/lib/MySQL-cluster/and run the following command:

mkdir -p /var/lib/mysql-cluster/

3. Configure the configuration files for the three machines

Create the configuration file my. CNF under the/etc directory for the two data nodes. The content is as follows:

[mysqld]
ndbcluster
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
port=3306
[mysql_cluster]
ndb-connectstring=192.168.56.10

If the MySQL database "communications link failure due to underlying exception" problem occurs during the running process, refer to this blog post for configuration.

Replace NDB-connectstring with 192.168.56.20.

Create the config. ini file under the/etc directory of the Management node. The content is as follows:

 1 [ndbd default]
 2 NoOfReplicas=2
 3 DataMemory=80M
 4 IndexMemory=18M
 5 
 6 [ndb_mgmd]
 7 NodeId=1
 8 hostname=192.168.56.30
 9 datadir=/var/lib/mysql-cluster
10 
11 [ndbd]
12 NodeId=2
13 hostname=192.168.56.10
14 datadir=/usr/local/mysql/data
15 
16 [ndbd]
17 NodeId=3
18 hostname=192.168.56.20
19 datadir=/usr/local/mysql/data
20 
21 [mysqld]
22 [mysqld]

4. Start the Cluster Environment

First, start the management node 192.168.56.30 and run the following command:

1 ndb_mgmd -f /etc/config.ini --initial

If the default config directory '/usr/local/MySQL-cluster' does not exist error message is displayed, create the folder manually.

Next we need to start the data node at 192.168.56.10 and 192.168.56.20. The command is:

1 bin/ndbd --initial

The-Initial Parameter needs to be added for the first start, and the second startCannot addThis parameter. Both machines run this command.

Run the following command to start the SQL nodes of the two machines:

1 bin/mysqld_safe --user=mysql &

Both machines need to execute this command.
Finally, go to the console and check whether the cluster configuration is complete. Enter the following command:

1 ndb_mgm –e show

If the following information is displayed, the configuration is successful:

If you cannot see the above information, it may be a firewall problem. You can turn off the centos firewall with the command:

1 /etc/init.d/iptables stop

Of course, you can choose to configure the firewall and open some necessary ports. Refer to another article on how to enable port 3306 in centos.

Close the cluster and run the following command:

1 ndb_mgm –e shutdown