MySQL Server Load balancer cluster notes

MySQL cluster based on two servers. MySQL can continue to run when any server encounters a problem or goes down.
Note: In theory, you can create a MySQL cluster based on only two servers. However, in this architecture, once a server goes down, the cluster cannot continue to work normally, in this way, the meaning of the cluster is lost. For this reason, a third server is required to run as a management node. The following describes the three services:
Server1: mysql1.linux.com 10.0.0.97
Server2: mysql2.linux.com 10.0.0.96
Server3: mysql3.linux.com 10.0.0.95
Server1 and Server2 serve as servers for configuring the MySQL cluster. Server3, which is a management node, has low requirements. You can run other services on Server3 at the same time.

2. Install MySQL on Server 1 and Server 2
Download mysql-max-5.0.27-linux-i686.tar.gz
Note: It must be MySQL of max version. Standard version does not support cluster deployment!
Modified Server1 and Server2
# Ls
Export mysql-max-5.0.27-linux-i686.tar.gz
# Music mysql-max-5.0.27-linux-i686.tar.gz/usr/src/
# Cd/usr/src/
# Groupadd mysql
# Useradd-g mysql
# Tar-zxvf mysql-max-5.0.27-linux-i686.tar.gz
# Music mysql-max-5.0.27-linux-i686 mysql
# Cd mysql/scripts
# Mysql_install_db -- user = mysql
# Cd ..
# Chown-fR root. mysql.
# Chown-R mysql data
# Cp support-files/mysql. server/etc/rc. d/init. d/mysqld
# Chmod + x/etc/rc. d/init. d/mysqld
# Cp support-files/my-large.cnf/etc/my. cnf
# Chkconfig -- add mysqld
Note: please not reStart MySQL!

3. install and modified Server3
Server3 requires two files: ndb_mgm and ndb_mgmd:
# Ls
# Mysql-max-5.0.27-linux-i686.tar.gz
# Mkdir/usr/src/mysql-mgm
# Pwd
#/Usr/src/mysql-mgm
# Tar-zxvf mysql-max-5.0.27-linux-i686.tar.gz
# Cd mysql-max-5.0.27-linux-i686
# Mv bin/ndb_mgm.
# Mv bin/ndb_mgmd.
# Chmod + x ndb_mg *
# Mv ndb_mg */usr/bin/
# Cd
# Rm-fR/usr/src/mysql-mgm
# Mkdir/var/lib/mysql-cluster // create a configuration file for this management node Server
# Cd/var/lib/mysql-cluster
# Vi config. ini // Add the following content to config. ini:
[Ndbd default]
NoOfReplicas = 2
[Mysqld default]
[NDB_MGMD DEFAULT]
[Tcp default]
# Managment Server
[NDB_MGMD]
HostName = 10.0.0.95 // ip address of the server Server3 on the Management Node
# Storage Engines
[NDBD]
HostName = 10.0.0.97 // ip address of MySQL cluster Server1
DataDir =/var/lib/mysql-cluster
[NDBD]
HostName = 10.0.0.96 // ip address of MySQL cluster Server2
DataDir =/var/lib/mysql-cluster
[MYSQLD] // two [MYSQLD] host names can be entered for Server1 and server2. To replace the servers in the cluster more quickly, [MYSQLD] // we recommend that you leave it blank. Otherwise, you must change this configuration after changing the server.
: X
# Ndb_mgmd

4. Configure the Cluster Server and start MySQL
# Change both vi/etc/my. cnf // Server1 and Server2
[Mysqld]
Ndbcluster
Ndb-connectstring = 10.0.0.95 // ip address of Server3
[Mysql_cluster]
Ndb-connectstring = 10.0.0.95 // ip address of Server3
: X
# Mkdir/var/lib/mysql-cluster
# Cd/var/lib/mysql-cluster
#/Usr/local/mysql/bin/ndbd -- initial-f/var/lib/mysql-cluster/config. ini
#/Etc/rc. d/init. d/mysqld start
# Cp/usr/local/mysql/bin/ndbd/etc/rc. local // start upon startup.
Note: you must use the -- initial Parameter only when you start ndbd for the first time or modify config. ini of Server3!

5. Check the working status
Return to Server 3 of the Management node and start the Management Terminal:
#/Usr/bin/ndb_mgm
[Root @ mysql3 root] #/usr/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 @ 10.0.0.97 (Version: 4.1.9, Nodegroup: 0, Master)
Id = 3 @ 10.0.0.96 (Version: 4.1.9, Nodegroup: 0)

[Ndb_mgmd (MGM)] 1 node (s)
Id = 1 @ 10.0.0.95 (Version: 4.1.9)

[Mysqld (API)] 2 node (s)
Id = 4 (Version: 4.1.9)
Id = 5 (Version: 4.1.9)

Ndb_mgm>
Test mysql:
At Server1 and server2:
# PATH = $ PATH/usr/local/mysql/bin
# Mysqladmin-uroot password '123'
# Mysql-uroot-p123
> Use test;
> Create table ctest (I INT) ENGINE = NDBCLUSTER;
> Insert into ctest () VALUES (1 );
> SELECT * FROM ctest;
If the above information is displayed, the experiment is successful.

Vi. Destructive Testing
Unplug the network cable of Server 1 or server 2 and check whether the server in the other cluster is working normally or
# Ps-A | grep ndbd
All ndbd processes are displayed:
Root 5578 0.0 0.3 6220 1964? S ndbd
Root 5579 0.0 20.4 492072 102828? R ndbd
Root 23532 0.0 0.1 3680 684 pts/1 S grep ndbd
# Kill-9 5578 5579
Then use the SELECT query test on another cluster server. Run the show command on the management terminal of the Management node server at www.bkjia.com to view the State of the damaged server.
After the test is complete, you only need to restart the ndbd process of the damaged server:
# Ndbd
Now, the MySQL cluster configuration is complete!

Author: edwin)
Date: 2010.7.27
Note: Reprinted please keep this information
For more information, visit my blog: blog.csdn.net/windxfxx8