MySQL database server cluster configuration detailed introduction

First, introduce
This document is intended to describe how to install a MySQL database cluster that is configured on 2 servers. and the implementation of any one server problems or downtime MySQL can still continue to run.
Attention!
Although this is a MySQL cluster based on 2 servers, there must also be an additional third server as a management node, but this server can be shut down after the cluster startup completes. Also note that it is not recommended to shut down the server as a management node after the cluster startup completes. Although it is theoretically possible to build a MySQL cluster based on only 2 servers, such a framework would have lost the sense of clustering once a server was down and the cluster could not continue to work properly. For this reason, a third server is required to run as a management node.
In addition, many friends may not have the actual environment of 3 servers, you can consider in VMware or other virtual machines to experiment.
The following assumptions about these 3 services:

The code is as follows	Copy Code
Server1:mysql1.vmtest.net 192.168.0.1 Server2:mysql2.vmtest.net 192.168.0.2 Server3:mysql3.vmtest.net 192.168.0.3

Servers1 and Server2 as the actual server with MySQL database cluster. Server3, as a management node, requires a low level of adjustment to the SERVER3 system and does not require installation Mysql,server3 can use a lower-configured computer and can run other services simultaneously at Server3.

Second, install MySQL on Server1 and Server2
Note: The max version of Mysql,standard must not support cluster deployment!
The following steps need to be done on Server1 and Server2 each time

The code is as follows

Copy Code

# MV mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz/usr/local/ # cd/usr/local/ # Groupadd MySQL # useradd-g MySQL MySQL # TAR-ZXVF Mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz # rm-f Mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz # mv mysql-max-4.1.9-pc-linux-gnu-i686 MySQL # CD MySQL # scripts/mysql_install_db--user=mysql # Chown-r Root. # chown-r MySQL Data # chgrp-r MySQL. # CP Support-files/mysql.server/etc/rc.d/init.d/mysqld # chmod +x/etc/rc.d/init.d/mysqld # chkconfig--add mysqld

Do not start mysql! at this time

Three, install and configure the Management node server (SERVER3)
as a Management node server, SERVER3 requires NDB_MGM and NDB_MGMD two files:
from http:// www.mysql.com Download mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz

The code is as follows	Copy Code
# MKDIR/USR/SRC/MYSQL-MGM # CD/USR/SRC/MYSQL-MGM # TAR-ZXVF Mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz # RM mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz # CD mysql-max-4.1.9-pc-linux-gnu-i686 # MV BIN/NDB_MGM. # MV BIN/NDB_MGMD. # chmod +x ndb_mg* # MV Ndb_mg*/usr/bin/ # CD # RM-RF/USR/SRC/MYSQL-MGM

At the beginning of the configuration file for this Management node server:

code is as follows

copy code

# Mkdir/var/lib/mysql-cluster # Cd/var/lib/mysql-cluster # VI Config.ini Add the following in Config.ini: [NDBD DEFAULT] noofreplicas=2 [MYSQLD DEFAULT] [NDB_MGMD DEFAULT] [TCP DEFAULT] # managment Server [NDB_MGMD] hostname=192.168.0.3 #管理节点服务器Server3的IP地址 # Storage Engines [NDBD] hostname=192.168.0.1 #MySQL数据库集群Server1的IP地址 Datadir=/var/lib/mysql-cluster [NDBD] hostname=192.168.0.2 #MySQL数据库集群Server2的IP地址 Datadir=/var/lib/mysql-cluster

The following 2 [MYSQLD] can fill in the Server1 and Server2 hostname.
However, for faster replacement of servers in the cluster, it is recommended that you leave it blank, or you must change this configuration after you replace the server.

[MYSQLD]

After the [MYSQLD] save exits, start the Management node server Server3:

# NDB_MGMD
After starting the management node, it should be noted that this is only the Management node service, not the management terminal. So you don't see any information about the output after startup.

Iv. Configure the cluster server and start MySQL
The following changes are required in both Server1 and Server2:

The code is as follows	Copy Code
# VI/ETC/MY.CNF [Mysqld] Ndbcluster ndb-connectstring=192.168.0.3 #Server3的IP地址 [Mysql_cluster] ndb-connectstring=192.168.0.3 #Server3

IP address can add/USR/LOCAL/MYSQL/BIN/NDBD to the/etc/rc.local to boot up.
Note: You need to use--initial parameters only when you first start ndbd or when you make changes to Server3 Config.ini!

V. Check the status of work
Go back to the Management node server Server3 and start the management terminal:

code is as follows

copy code

#/USR/BIN/NDB_MGM Type show command to view current working status: (Below is a status output example) [Root@mysql3 root]#/USR/BIN/NDB_MGM--NDB Cluster--Management Client--ndb_mgm> show Connected to Managem ENT Server at:localhost:1186 Cluster Configuration [NDBD (NDB)] 2 node (s) id=2 @192.168.0.1 (version:4.1.9, node group:0, Master) id=3 @192.168.0.2 (version:4.1.9, nodegroup:0) [NDB_MGMD (MGM)] 1 node (s) id=1 @192.168.0.3 (V ersion:4.1.9) [Mysqld (API)] 2 node (s) id=4 (version:4.1.9) id=5 (version:4.1.9) ndb_mgm>

If there is no problem, start testing MySQL now:
Note that this document does not have the root password set for MySQL and recommends that you set the MySQL root password for Server1 and Server2.
In the Server1:

The code is as follows	Copy Code
#/usr/local/mysql/bin/mysql-u root-p > Use test;    > CREATE TABLE ctest (i INT) engine=ndbcluster;    > INSERT into CTest () VALUES (1); > SELECT * from CTest;

　　
You should see 1 row returned information (return value 1).
If the above normal, then change to Server2 on the above test, observe the effect. If successful, insert in Server2 and return to Server1 to see if it works properly.
If there are no problems, then congratulations on success!

VI. Destructive Testing
Unplug the Server1 or Server2 cable to see if another MySQL database Cluster server is working properly (you can use the Select query test). Once the test is complete, reinsert the network cable.
If you don't have access to a physical server, which means you can't unplug the network cable, you can also test this:

On the Server1 or Server2:

The code is as follows	Copy Code
# PS aux | grep ndbd You will see all NDBD process information: Root 5578 0.0 0.3 6220 1964? S 03:14 0:00 NDBD Root 5579 0.0 20.4 492072 102828? R 03:14 0:04 NDBD Root 23532 0.0 0.1 3680 684 pts/1 S 07:59 0:00 grep ndbd

  
Then kill a ndbd process to achieve the purpose of destroying the MySQL clustered server:
# kill-9 5578 5579  
and then use the Select Check on another clustered server Consult the test. and executing the show command in the management terminal of the Management node server will see the state of the compromised server.
After the test is complete, you only need to restart the NDBD process of the compromised server:
# ndbd   Note! Said earlier, at this time is not to add--inital parameters!
At this point, the MySQL database cluster is configured to complete!