Here we configure a MySQL 5 cluster with three nodes: two storage nodes and one management node. This cluster is load-balanced by a high-availability load balancer that in fact has two nodes that use the heartbeat (for checking if the other node is still alive) and ldirectord (to split up the requests to the nodes of the MySQL cluster).
servers required to configured this setup (All are in the same network 192.168.0.x ):
mysql1.pankaj.com: 192.168.0.101 MySQL cluster node 1
mysql2.pankaj.com: 192.168.0.102 MySQL cluster node 2
loadp1.pankaj.com: 192.168.0.103 Load Balancer 1 / MySQL cluster management server
loadp2.pankaj.com: 192.168.0.104 Load Balancer 2
In addition to that we need a virtual IP address : 192.168.0.105. It will be assigned to the MySQL cluster by the load balancer so that applications have a single IP address to access the cluster.
Although we want to have two MySQL cluster nodes in our MySQL cluster, we still need a third node, the MySQL cluster management server, for mainly one reason: if one of the two MySQL cluster nodes fails, and the management server is not running, then the data on the two cluster nodes will become inconsistent ("split brain"). We also need it for configuring the MySQL cluster.
So normally we would need five machines for our setup:
2 MySQL cluster nodes + 1 cluster management server + 2 Load Balancers = 5
(As the MySQL cluster management server does not use many resources, and the system would just sit there doing nothing, we can put our first load balancer on the same machine, which saves us one machine, so we end up with four machines.)
Set Up The MySQL Cluster Management Server
First we have to download MySQL 5.0.19 (the max version!) and install the cluster management server (ndb_mgmd) and the cluster management client (ndb_mgm - it can be used to monitor what's going on in the cluster). The following steps are carried out on loadp1.pankaj.com (192.168.0.103):
loadp1.pankaj.com:
mkdir /usr/src/mysql-mgm
cd /usr/src/mysql-mgm
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-max-5.0.19-linux-i686-\
glibc23.tar.gz/from/http://www.mirrorservice.org/sites/ftp.mysql.com/
tar xvfz mysql-max-5.0.19-linux-i686-glibc23.tar.gz
cd mysql-max-5.0.19-linux-i686-glibc23
mv bin/ndb_mgm /usr/bin
mv bin/ndb_mgmd /usr/bin
chmod 755 /usr/bin/ndb_mg*
cd /usr/src
rm -rf /usr/src/mysql-mgm
cd /usr/src/mysql-mgm
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-max-5.0.19-linux-i686-\
glibc23.tar.gz/from/http://www.mirrorservice.org/sites/ftp.mysql.com/
tar xvfz mysql-max-5.0.19-linux-i686-glibc23.tar.gz
cd mysql-max-5.0.19-linux-i686-glibc23
mv bin/ndb_mgm /usr/bin
mv bin/ndb_mgmd /usr/bin
chmod 755 /usr/bin/ndb_mg*
cd /usr/src
rm -rf /usr/src/mysql-mgm
Next, we must create the cluster configuration file, /var/lib/mysql-cluster/config.ini:
mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
vi config.ini
cd /var/lib/mysql-cluster
vi config.ini
[NDBD DEFAULT]
NoOfReplicas=2
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Section for the cluster management node
[NDB_MGMD]
# IP address of the management node (this system)
HostName=192.168.0.103
# Section for the storage nodes
[NDBD]
# IP address of the first storage node
HostName=192.168.0.101
DataDir= /var/lib/mysql-cluster
[NDBD]
# IP address of the second storage node
HostName=192.168.0.102
DataDir=/var/lib/mysql-cluster
# one [MYSQLD] per storage node
[MYSQLD]
[MYSQLD]
NoOfReplicas=2
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Section for the cluster management node
[NDB_MGMD]
# IP address of the management node (this system)
HostName=192.168.0.103
# Section for the storage nodes
[NDBD]
# IP address of the first storage node
HostName=192.168.0.101
DataDir= /var/lib/mysql-cluster
[NDBD]
# IP address of the second storage node
HostName=192.168.0.102
DataDir=/var/lib/mysql-cluster
# one [MYSQLD] per storage node
[MYSQLD]
[MYSQLD]
Please replace the IP addresses in the file appropriately.
Then we start the cluster management server:
Then we start the cluster management server:
loadp1.pankaj.com:
ndb_mgmd -f /var/lib/mysql-cluster/config.ini
It makes sense to automatically start the management server at system boot time, so we create a very simple init script and the appropriate startup links:
echo 'ndb_mgmd -f /var/lib/mysql-cluster/config.ini' > /etc/init.d/ndb_mgmd
chmod 755 /etc/init.d/ndb_mgmd
update-rc.d ndb_mgmd defaults
ndb_mgmd -f /var/lib/mysql-cluster/config.ini
It makes sense to automatically start the management server at system boot time, so we create a very simple init script and the appropriate startup links:
echo 'ndb_mgmd -f /var/lib/mysql-cluster/config.ini' > /etc/init.d/ndb_mgmd
chmod 755 /etc/init.d/ndb_mgmd
update-rc.d ndb_mgmd defaults
Set Up The MySQL Cluster Nodes (Storage Nodes)
Now we install mysql-max-5.0.19 on both mysql1.pankaj.com and mysql2.pankaj.com:
mysql1.pankaj.com / mysql2.pankaj.com:
groupadd mysql
useradd -g mysql mysql
cd /usr/local/
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-max-5.0.19-linux-i686-\
glibc23.tar.gz/from/http://www.mirrorservice.org/sites/ftp.mysql.com/
tar xvfz mysql-max-5.0.19-linux-i686-glibc23.tar.gz
ln -s mysql-max-5.0.19-linux-i686-glibc23 mysql
cd mysql
scripts/mysql_install_db --user=mysql
chown -R root:mysql .
chown -R mysql data
cp support-files/mysql.server /etc/init.d/
chmod 755 /etc/init.d/mysql.server
update-rc.d mysql.server defaults
cd /usr/local/mysql/bin
mv * /usr/bin
cd ../
rm -fr /usr/local/mysql/bin
ln -s /usr/bin /usr/local/mysql/bin
Then we create the MySQL configuration file /etc/my.cnf on both nodes:
mysql1.pankaj.com / mysql2.pankaj.com:
vi /etc/my.cnf
[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=192.168.0.103
[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=192.168.0.103
Make sure you fill in the correct IP address of the MySQL cluster management server.
Next we create the data directories and start the MySQL server on both cluster nodes:
mysql1.pankaj.com / mysql2.pankaj.com:
mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
ndbd --initial
/etc/init.d/mysql.server start
(Please note: we have to run ndbd --initial only when the start MySQL for the first time, and if /var/lib/mysql-cluster/config.ini on loadp1.pankaj.com changes.)
Now is a good time to set a password for the MySQL root user:
mysql1.pankaj.com / mysql2.pankaj.com:
mysqladmin -u root password yourrootsqlpassword
We want to start the cluster nodes at boot time, so we create an ndbd init script and the appropriate system startup links:
mysql1.pankaj.com / mysql2.pankaj.com:
echo 'ndbd' > /etc/init.d/ndbd
chmod 755 /etc/init.d/ndbd
update-rc.d ndbd defaults
Test The MySQL Cluster
Our MySQL cluster configuration is already finished, now it's time to test it. On the cluster management server (loadp1.pankaj.com), run the cluster management client ndb_mgm to check if the cluster nodes are connected:
loadp1.pankaj.com:
ndb_mgm
You should see this:
-- NDB Cluster -- Management Client --
ndb_mgm>
Now type show; at the command prompt:
show;
The output should be like this:
ndb_mgm> show;
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.0.101 (Version: 5.0.19, Nodegroup: 0, Master)
id=3 @192.168.0.102 (Version: 5.0.19, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.103 (Version: 5.0.19)
[mysqld(API)] 2 node(s)
id=4 @192.168.0.101 (Version: 5.0.19)
id=5 @192.168.0.102 (Version: 5.0.19)
ndb_mgm>
If you see that your nodes are connected, then everything's ok!
Type
quit;
to leave the ndb_mgm client console.
Now we create a test database with a test table and some data on mysql1.pankaj.com:
mysql1.pankaj.com:
mysql -u root -p
CREATE DATABASE mysqlclustertest;
USE mysqlclustertest;
CREATE TABLE testtable (i INT) ENGINE=NDBCLUSTER;
INSERT INTO testtable () VALUES (1);
SELECT * FROM testtable;
quit;
(Have a look at the CREATE statment: We must use ENGINE=NDBCLUSTER for all database tables that we want to get clustered! If you use another engine, then clustering will not work!)
The result of the SELECT statement should be:
mysql> SELECT * FROM testtable;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.03 sec)
Now we create the same database on mysql2.pankaj.com (yes, we still have to create it, but afterwards testtable and its data should be replicated to mysql2.pankaj.com because testtable uses ENGINE=NDBCLUSTER):
mysql2.pankaj.com:
mysql -u root -p
CREATE DATABASE mysqlclustertest;
USE mysqlclustertest;
SELECT * FROM testtable;
The SELECT statement should deliver you the same result as before on mysql1.pankaj.com:
mysql> SELECT * FROM testtable;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.04 sec)
So the data was replicated from mysql1.pankaj.com to mysql2.pankaj.com. Now we insert another row into testtable:
mysql2.pankaj.com:
INSERT INTO testtable () VALUES (2);
quit;
Now let's go back to mysql1.pankaj.com and check if we see the new row there:
mysql1.pankaj.com:
mysql -u root -p
USE mysqlclustertest;
SELECT * FROM testtable;
quit;
You should see something like this:
mysql> SELECT * FROM testtable;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.05 sec)
So both MySQL cluster nodes always have the same data!
Now let's see what happens if we stop node 1 (mysql1.pankaj.com):
mysql1.pankaj.com:
killall ndbd
and check with
ps aux | grep ndbd | grep -iv grep
that all ndbd processes have terminated. If you still see ndbd processes, run another
killall ndbd
until all ndbd processes are gone.
Now let's check the cluster status on our management server (loadp1.pankaj.com):
loadp1.pankaj.com:
ndb_mgm
On the ndb_mgm console, issue
show;
and you should see this:
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 192.168.0.101)
id=3 @192.168.0.102 (Version: 5.0.19, Nodegroup: 0, Master)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.103 (Version: 5.0.19)
[mysqld(API)] 2 node(s)
id=4 @192.168.0.101 (Version: 5.0.19)
id=5 @192.168.0.102 (Version: 5.0.19)
ndb_mgm>
You see, mysql1.pankaj.com is not connected anymore.
Type
quit;
to leave the ndb_mgm console.
Let's check mysql2.pankaj.com:
mysql2.pankaj.com:
mysql -u root -p
USE mysqlclustertest;
SELECT * FROM testtable;
quit;
The result of the SELECT query should still be
mysql> SELECT * FROM testtable;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.17 sec)
Ok, all tests went fine, so let's start our mysql1.pankaj.com node again:
mysql1.pankaj.com:
ndbd
mysql -u root -p
CREATE DATABASE mysqlclustertest;
USE mysqlclustertest;
SELECT * FROM testtable;
The SELECT statement should deliver you the same result as before on mysql1.pankaj.com:
mysql> SELECT * FROM testtable;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.04 sec)
So the data was replicated from mysql1.pankaj.com to mysql2.pankaj.com. Now we insert another row into testtable:
mysql2.pankaj.com:
INSERT INTO testtable () VALUES (2);
quit;
Now let's go back to mysql1.pankaj.com and check if we see the new row there:
mysql1.pankaj.com:
mysql -u root -p
USE mysqlclustertest;
SELECT * FROM testtable;
quit;
You should see something like this:
mysql> SELECT * FROM testtable;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.05 sec)
So both MySQL cluster nodes always have the same data!
Now let's see what happens if we stop node 1 (mysql1.pankaj.com):
mysql1.pankaj.com:
killall ndbd
and check with
ps aux | grep ndbd | grep -iv grep
that all ndbd processes have terminated. If you still see ndbd processes, run another
killall ndbd
until all ndbd processes are gone.
Now let's check the cluster status on our management server (loadp1.pankaj.com):
loadp1.pankaj.com:
ndb_mgm
On the ndb_mgm console, issue
show;
and you should see this:
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 192.168.0.101)
id=3 @192.168.0.102 (Version: 5.0.19, Nodegroup: 0, Master)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.103 (Version: 5.0.19)
[mysqld(API)] 2 node(s)
id=4 @192.168.0.101 (Version: 5.0.19)
id=5 @192.168.0.102 (Version: 5.0.19)
ndb_mgm>
You see, mysql1.pankaj.com is not connected anymore.
Type
quit;
to leave the ndb_mgm console.
Let's check mysql2.pankaj.com:
mysql2.pankaj.com:
mysql -u root -p
USE mysqlclustertest;
SELECT * FROM testtable;
quit;
The result of the SELECT query should still be
mysql> SELECT * FROM testtable;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.17 sec)
Ok, all tests went fine, so let's start our mysql1.pankaj.com node again:
mysql1.pankaj.com:
ndbd
Restart The Cluster
Now let's asume you want to restart the MySQL cluster, for example because you have changed /var/lib/mysql-cluster/config.ini on loadp1.pankaj.com or for some other reason. To do this, you use the ndb_mgm cluster management client on loadp1.pankaj.com:
loadp1.pankaj.com:
ndb_mgm
On the ndb_mgm console, you type
shutdown;
You will then see something like this:
ndb_mgm> shutdown;
Node 3: Cluster shutdown initiated
Node 2: Node shutdown completed.
2 NDB Cluster node(s) have shutdown.
NDB Cluster management server shutdown.
ndb_mgm>
This means that the cluster nodes mysql1.pankaj.com and mysql2.pankaj.com and also the cluster management server have shut down.
Run
quit;
to leave the ndb_mgm console.
To start the cluster management server, do this on loadp1.pankaj.com:
loadp1.pankaj.com:
ndb_mgmd -f /var/lib/mysql-cluster/config.ini
and on mysql1.pankaj.com and mysql2.pankaj.com you run
mysql1.pankaj.com / mysql2.pankaj.com:
ndbd
or, if you have changed /var/lib/mysql-cluster/config.ini on loadp1.pankaj.com:
ndbd --initial
Afterwards, you can check on loadp1.pankaj.com if the cluster has restarted:
loadp1.pankaj.com:
ndb_mgm
On the ndb_mgm console, type
show;
to see the current status of the cluster. It might take a few seconds after a restart until all nodes are reported as connected.
Type
quit;
to leave the ndb_mgm console.
Configure The Load Balancers
MySQL cluster is finished now, and you could start using it now. However, we don't have a single IP address that we can use to access the cluster, which means you must configure your applications in a way that a part of it uses the MySQL cluster node 1 (mysql1.pankaj.com), and the rest uses the other node (mysql2.pankaj.com). Of course, all your applications could just use one node, but what's the point then in having a cluster if you do not split up the load between the cluster nodes? Another problem is, what happens if one of the cluster nodes fails? Then the applications that use this cluster node cannot work anymore.
The solution is to have a load balancer in front of the MySQL cluster which (as its name suggests) balances the load between the MySQL cluster nodes. The load blanacer configures a virtual IP address that is shared between the cluster nodes, and all your applications use this virtual IP address to access the cluster. If one of the nodes fails, then your applications will still work, because the load balancer redirects the requests to the working node.
Now in this scenario the load balancer becomes the bottleneck. What happens if the load balancer fails? Therefore we will configure two load balancers (loadp1.pankaj.com and loadp2.pankaj.com) in an active/passive setup, which means we have one active load balancer, and the other one is a hot-standby and becomes active if the active one fails. Both load balancers use heartbeat to check if the other load balancer is still alive, and both load balancers also use ldirectord, the actual load balancer the splits up the load onto the cluster nodes. heartbeat and ldirectord are provided by the Ultra Monkey package that we will install.
It is important that loadp1.pankaj.com and loadp2.pankaj.com have support for IPVS (IP Virtual Server) in their kernels. IPVS implements transport-layer load balancing inside the Linux kernel.
Install Ultra Monkey
Ok, let's start: first we enable IPVS on loadp1.pankaj.com and loadp2.pankaj.com:
loadp1.pankaj.com / loadp2.pankaj.com:
modprobe ip_vs_dh
modprobe ip_vs_ftp
modprobe ip_vs
modprobe ip_vs_lblc
modprobe ip_vs_lblcr
modprobe ip_vs_lc
modprobe ip_vs_nq
modprobe ip_vs_rr
modprobe ip_vs_sed
modprobe ip_vs_sh
modprobe ip_vs_wlc
modprobe ip_vs_wrr
In order to load the IPVS kernel modules at boot time, we list the modules in /etc/modules:
loadp1.pankaj.com / loadp2.pankaj.com:
vi /etc/modules
ip_vs_dh
ip_vs_ftp
ip_vs
ip_vs_lblc
ip_vs_lblcr
ip_vs_lc
ip_vs_nq
ip_vs_rr
ip_vs_sed
ip_vs_sh
ip_vs_wlc
ip_vs_wrr
Now we edit /etc/apt/sources.list and add the Ultra Monkey repositories (don't remove the other repositories), and then we install Ultra Monkey:
loadp1.pankaj.com / loadp2.pankaj.com:
vi /etc/apt/sources.list
deb http://www.ultramonkey.org/download/3/ sarge main
deb-src http://www.ultramonkey.org/download/3 sarge main
apt-get update
apt-get install ultramonkey libdbi-perl libdbd-mysql-perl libmysqlclient14-dev
Now Ultra Monkey is being installed. If you see this warning:
¦ libsensors3 not functional ¦
¦ ¦
¦ It appears that your kernel is not compiled with sensors support. As a ¦
¦ result, libsensors3 will not be functional on your system. ¦
¦ ¦
¦ If you want to enable it, have a look at "I2C Hardware Sensors Chip ¦
¦ support" in your kernel configuration. ¦
you can ignore it.
Answer the following questions:
Do you want to automatically load IPVS rules on boot?
<-- No
Select a daemon method.
<-- none
The libdbd-mysql-perl package we've just installed does not work with MySQL 5 (we use MySQL 5 on our MySQL cluster...), so we install the newest DBD::mysql Perl package:
loadp1.pankaj.com / loadp2.pankaj.com:
cd /tmp
wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-3.0002.tar.gz
tar xvfz DBD-mysql-3.0002.tar.gz
cd DBD-mysql-3.0002
perl Makefile.PL
make
make install
We must enable packet forwarding:
loadp1.pankaj.com / loadp2.pankaj.com:
vi /etc/sysctl.conf
# Enables packet forwarding
net.ipv4.ip_forward = 1
sysctl –p
Configure heartbeat
Next we configure heartbeat by creating three files (all three files must be identical on loadp1.pankaj.com and loadp2.pankaj.com):
loadp1.pankaj.com / loadp2.pankaj.com:
vi /etc/ha.d/ha.cf
logfacility local0
bcast eth0
mcast eth0 225.0.0.1 694 1 0
auto_failback off
node loadb1
node loadb2
respawn hacluster /usr/lib/heartbeat/ipfail
apiauth ipfail gid=haclient uid=hacluster
Please note: you must list the node names (in this case loadb1 and loadb2) as shown by
uname -n
Other than that, you don't have to change anything in the file.
vi /etc/ha.d/haresources
loadb1 \
ldirectord::ldirectord.cf \
LVSSyncDaemonSwap::master \
IPaddr2::192.168.0.105/24/eth0/192.168.0.255
You must list one of the load balancer node names (here: loadb1) and list the virtual IP address (192.168.0.105) together with the correct netmask (24) and broadcast address (192.168.0.255). If you are unsure about the correct settings, http://www.subnetmask.info/ might help you.
vi /etc/ha.d/authkeys
auth 3
3 md5 palpankaj@123
palpankaj@123 is a password which the two heartbeat daemons on loadb1 and loadb2 use to authenticate against each other. Use your own string here. You have the choice between three authentication mechanisms. I use md5 as it is the most secure one.
/etc/ha.d/authkeys should be readable by root only, therefore we do this:
loadp1.pankaj.com / loadp2.pankaj.com:
chmod 600 /etc/ha.d/authkeys
Configure ldirectord
Now we create the configuration file for ldirectord, the load balancer:
loadp1.pankaj.com / loadp2.pankaj.com:
vi /etc/ha.d/ldirectord.cf
# Global Directives
checktimeout=10
checkinterval=2
autoreload=no
logfile="local0"
quiescent=yes
virtual = 192.168.0.105:3306
service = mysql
real = 192.168.0.101:3306 gate
real = 192.168.0.102:3306 gate
checktype = negotiate
login = "ldirector"
passwd = "ldirectorpassword"
database = "ldirectordb"
request = "SELECT * FROM connectioncheck"
scheduler = wrr
Please fill in the correct virtual IP address (192.168.0.105) and the correct IP addresses of your MySQL cluster nodes (192.168.0.101 and 192.168.0.102). 3306 is the port that MySQL runs on by default. We also specify a MySQL user (ldirector) and password (ldirectorpassword), a database (ldirectordb) and an SQL query. ldirectord uses this information to make test requests to the MySQL cluster nodes to check if they are still available. We are going to create the ldirector database with the ldirector user in the next step.
Now we create the necessary system startup links for heartbeat and remove those of ldirectord (because ldirectord will be started by heartbeat):
loadp1.pankaj.com / loadp2.pankaj.com:
update-rc.d -f heartbeat remove
update-rc.d heartbeat start 75 2 3 4 5 . stop 05 0 1 6 .
update-rc.d -f ldirectord remove
Create A Database Called ldirector
Next we create the ldirector database on our MySQL cluster nodes mysql1.pankaj.com and mysql2.pankaj.com. This database will be used by our load balancers to check the availability of the MySQL cluster nodes.
mysql1.pankaj.com:
mysql -u root -p
GRANT ALL ON ldirectordb.* TO 'ldirector'@'%' IDENTIFIED BY 'ldirectorpassword';
FLUSH PRIVILEGES;
CREATE DATABASE ldirectordb;
USE ldirectordb;
CREATE TABLE connectioncheck (i INT) ENGINE=NDBCLUSTER;
INSERT INTO connectioncheck () VALUES (1);
quit;
mysql2.pankaj.com:
mysql -u root -p
GRANT ALL ON ldirectordb.* TO 'ldirector'@'%' IDENTIFIED BY 'ldirectorpassword';
FLUSH PRIVILEGES;
CREATE DATABASE ldirectordb;
quit;
Prepare The MySQL Cluster Nodes For Load Balancing
Finally we must configure our MySQL cluster nodes mysql1.pankaj.com and mysql2.pankaj.com to accept requests on the virtual IP address 192.168.0.105.
mysql1.pankaj.com / mysql2.pankaj.com:
apt-get install iproute
Add the following to /etc/sysctl.conf:
mysql1.pankaj.com / mysql2.pankaj.com:
vi /etc/sysctl.conf
# Enable configuration of arp_ignore option
net.ipv4.conf.all.arp_ignore = 1
# When an arp request is received on eth0, only respond if that address is
# configured on eth0. In particular, do not respond if the address is
# configured on lo
net.ipv4.conf.eth0.arp_ignore = 1
# Ditto for eth1, add for all ARPing interfaces
#net.ipv4.conf.eth1.arp_ignore = 1
# Enable configuration of arp_announce option
net.ipv4.conf.all.arp_announce = 2
# When making an ARP request sent through eth0 Always use an address that
# is configured on eth0 as the source address of the ARP request. If this
# is not set, and packets are being sent out eth0 for an address that is on
# lo, and an arp request is required, then the address on lo will be used.
# As the source IP address of arp requests is entered into the ARP cache on
# the destination, it has the effect of announcing this address. This is
# not desirable in this case as adresses on lo on the real-servers should
# be announced only by the linux-director.
net.ipv4.conf.eth0.arp_announce = 2
# Ditto for eth1, add for all ARPing interfaces
#net.ipv4.conf.eth1.arp_announce = 2
sysctl -p
Add this section for the virtual IP address to /etc/network/interfaces:
mysql1.pankaj.com / mysql2.pankaj.com:
vi /etc/network/interfaces
auto lo:0
iface lo:0 inet static
address 192.168.0.105
netmask 255.255.255.255
pre-up sysctl -p > /dev/null
ifup lo:0
Start The Load Balancer And Do Some Testing
Now we can start our two load balancers for the first time:
loadp1.pankaj.com / loadp2.pankaj.com:
/etc/init.d/ldirectord stop
/etc/init.d/heartbeat start
If you don't see errors, you should now reboot both load balancers:
loadp1.pankaj.com / loadp2.pankaj.com:
shutdown -r now
After the reboot we can check if both load balancers work as expected :
loadp1.pankaj.com / loadp2.pankaj.com:
ip addr sh eth0
The active load balancer should list the virtual IP address (192.168.0.105):
2: eth0: <BROADCAST,MULTICAST,UP> mtu 1500 qdisc pfifo_fast qlen 1000
link/ether 00:16:3e:45:fc:f8 brd ff:ff:ff:ff:ff:ff
inet 192.168.0.103/24 brd 192.168.0.255 scope global eth0
inet 192.168.0.105/24 brd 192.168.0.255 scope global secondary eth0
The hot-standby should show this:
2: eth0: <BROADCAST,MULTICAST,UP> mtu 1500 qdisc pfifo_fast qlen 1000
link/ether 00:16:3e:16:c1:4e brd ff:ff:ff:ff:ff:ff
inet 192.168.0.104/24 brd 192.168.0.255 scope global eth0
loadp1.pankaj.com / loadp2.pankaj.com:
ldirectord ldirectord.cf status
Output on the active load balancer:
ldirectord for /etc/ha.d/ldirectord.cf is running with pid: 1603
Output on the hot-standby:
ldirectord is stopped for /etc/ha.d/ldirectord.cf
loadp1.pankaj.com / loadp2.pankaj.com:
ipvsadm -L -n
Output on the active load balancer:
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 192.168.0.105:3306 wrr
-> 192.168.0.101:3306 Route 1 0 0
-> 192.168.0.102:3306 Route 1 0 0
Output on the hot-standby:
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
loadp1.pankaj.com / loadp2.pankaj.com:
/etc/ha.d/resource.d/LVSSyncDaemonSwap master status
Output on the active load balancer:
master running
(ipvs_syncmaster pid: 1766)
Output on the hot-standby:
master stopped
(ipvs_syncbackup pid: 1440)
If your tests went fine, you can now try to access the MySQL database from a totally different server in the same network (192.168.0.x) using the virtual IP address 192.168.0.105:
mysql -h 192.168.0.105 -u ldirector -p
(Please note: your MySQL client must at least be of version 4.1; older versions do not work with MySQL 5.)
You can now switch off one of the MySQL cluster nodes for test purposes; you should then still be able to connect to the MySQL database.
Annotations
There are some important things to keep in mind when running a MySQL cluster:
- All data is stored in RAM! Therefore you need lots of RAM on your cluster nodes. The formula how much RAM you need on ech node goes like this:
(SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes
So if you have a database that is 1 GB of size, you would need 1.1 GB RAM on each node!
- The cluster management node listens on port 1186, and anyone can connect. So that's definitely not secure, and therefore you should run your cluster in an isolated private network!
FOR MORE DETAILS PLESE REFER http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
DISCLAIMER: The
information provided on this website comes without warranty of any kind and is
distributed AS IS. Every effort has been made to provide the information as
accurate as possible, but no warranty or fitness is implied. The information
may be incomplete, may contain errors or may have become out of date. The use
of this information described herein is your responsibility, and to use it in
your own environments do so at your own risk.
Copyright
© 2012 LINUXHOWTO.IN
On a replication master, you must enable binary logging and establish a unique server ID. If this has not already been done, this part of master setup requires a server restart.
ReplyDeleteBinary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible.
Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group, and must be a positive integer between 1 and (232)–1. How you organize and select the numbers is entirely up to you.
To configure the binary log and server ID options, you will need to shut down your MySQL server and edit the my.cnf or my.ini file. Add the following options to the configuration file within the [mysqld] section. If these options already exist, but are commented out, uncomment the options and alter them according to your needs. For example, to enable binary logging using a log file name prefix of mysql-bin, and configure a server ID of 1, use these lines:
[mysqld]
log-bin=mysql-bin
server-id=1
After making the changes, restart the server.
Note