mario a. spinthiras

You might have searched high and low on the net to find a method of properly deploying a twin machine setup with mysql databases where you want to replicate one to another and vice versa. Well the truth is that when I searched around nothing spectacularly working was found ; and if found it had problems ergo why it wouldn’t be considered to be spectacular in any way. This is how I did it and it works!

Before we dive into replication between mysql databases I feel it only moral and correct that we should discuss the purpose of such a setup. Let us give an example.

You have 1 machine of which you basically want to add another machine in order to take over the original machine’s services in the case that it fails. Lets say for the sake of our example that this is a simple database that application XYZ adds data to this database accordingly. This applicaiton is not residing on these machines but on another machine (e.g your web server , your application server).

From this we have derived the purpose of the machine which is our very discussion in this article. They are:

• High availability - The machine is to have a failover method.
• Consistency. In the case that the machine indeed fails then we assume that the failover machine has to have the same data in order to take over the services where the active machine died and stopped offering services.

This is a lot easier than you might have thought.

Ingredients:

• heartbeat (from the Linux-HA project)
• mysql
• coffee (I am not referring to an application here but to actual filter coffee which I will be making right now)…Done!

This article assumes you have already setup mysql and your server. We will move along directly into heartbeat and the replication onwards.

Heartbeat (Linux-HA) : Active-Passive Single IP Address Configuration.

Heartbeat can be installed using your favourite package manager. Since I use a Debian based Linux distribution then I use apt-get install heartbeat. If you feel that you want to compile please do so but be aware that you will not find this procedure here. More information is available on this from the Linux-HA website which can be found at the end of this article.

Once you have installed heartbeat you will need to know more on the actual configuration and the physical setup. Heartbeat sends “heartbeats” every X time units and expects the other machine to answer. This can be done with broadcast or unicast traffic using the UDP protocol. In order to accomplish this “heartbeat” interface if you will ; I used a private interface that had nothing to do with their production environment interfaces. This means that if eth0 is your production environment interface (the one you use by default) then I would suggest connecting a crossover cable from eth1 to eth1 so the machines have a back to back configuration.

For this you will have to pick a network that eth1 on each machine will have. Go for a /30 which is small and tidy.

In our scenario here we will use 10.10.10.0/30.

Machine A: 10.10.10.1
Machine B: 10.10.10.2

Configure your interfaces and up them. Once this is done test connectivity and we move along.

Once this is done lets go back to configuring heartbeat. The directory /etc/ha.d/ has all the information we are looking for to configure heartbeat. There are only three files to configure and more or less they are the same on each machine.

1st file: /etc/ha.d/ha.cf - The Configuration File - after each hash the directive is explained.

/etc/ha.d/ha.cf (Explanations are made next to each configuration directive)
logfacility daemon #Logging is active
node nodea nodeb #Preference of active node in sequential order. This is defined by the haresources file.
keepalive 1 #How many seconds to send heartbeat
deadtime 3 #How many seconds must pass to consider the other node dead.
bcast eth1 #Which network interface must be used to send out our heartbeats
ping 192.168.1.1 # An address used to test connectivity to our network. If this fails then the node fallsback to the failover cluster.
auto_failback no #Failback to original node when it returns from it’s dead period.
respawn hacluster /usr/lib/heartbeat/ipfail # DO NOT TOUCH THIS.

2nd file: /etc/ha.d/haresources - The Resources File - Explained after hash “#”

nodea 192.168.1.100 # This defines the preferred node and the active IP address it should have.

3rd file - /etc/ha.d/authkeys - Used for authentication for the heartbeats and info sent and recieved between the nodes.

auth 1
1 md5 simple-minds123xyz

Since we did configure eth1 as the broadcast that means that everything happens on the 10.10.10.0/30 network. This is good.Imagine now that eth0 on each machine has an address on the 192.168.1.0/24 network which so happens to be the network where we want to add availability to 192.168.1.100 which is noted above into the configuration.

DO NOT CONFIGURE THE IP ADDRESS USED FOR THE FAILOVER. This will break heartbeat and it will not work.

Start heartbeat on both machines using /etc/init.d/heartbeat restart

Test your failover. If you ping 192.168.1.100 now and pull the active machine you should be able to see that the IP is taken over and fails back to the failover machine.

MySQL Replication

Now that your active-failover cluster is working proceed with setting up mysql replication in a dual master->slave status.

Go to Node A and create a database called “replication”. Then edit your my.cnf file which usually can be found somewhere in /etc to look like the following:

NODE A
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 10.10.10.2
master-user = ninja
master-password = ninja!
master-connect-retry = 60
replicate-do-db = replication
log-bin = /var/log/mysql/mysql-bin.log
expire-logs-days = 20
max_binlog_size = 104857600
binlog-do-db = replication
#binlog-ignore-db = mysql

NODE A
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 1
auto-increment-offset = 2
master-host = 10.10.10.1
master-user = ninja
master-password = ninja!
master-connect-retry = 60
replicate-do-db = replication
log-bin = /var/log/mysql/mysql-bin.log
expire-logs-days = 20
max_binlog_size = 104857600
binlog-do-db = replication
#binlog-ignore-db = mysql

Pretty straight forward? We will use the private back-to-back network to replicate. This makes it faster and avoids any point of failures on a networking aspect concerning third devices beyond each machine’s NIC.

restart the mysql server (e.g /etc/init.d/mysql restart).

Login to mysql and type show slave status\G; . This should show output where you have to find the lines as follows.

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If this is running then your slave is perfectly fine. One more command to check the maste is show master status;