Failover system based on mySQL replication and network protocol CARP

And so is the task.
There are 2 servers: 1 - Master, 2 - Slave. It is necessary to configure a fault-tolerant system, which will allow in the event of a fall of the 1st server and / or database, automatically switch to the 2nd with a minimum downtime. After restoration of the Master, he should become a slave, and Slave - a Master. OS FreeBSD.

A little about the "fish".

To begin with, we’ll deal with
CARP CARP (from the Common Address Redundancy Protocol), a network protocol whose main task is to use a single IP address by several hosts within a network segment. // taken from Wikipedia

## 192.168.10.1 - Master 1
## 192.168.10.2 - Slave 2

1. Add carp to the configuration and rebuild the kernel.

master1 # ee / usr / src / sys / i386 / conf / MYKERNEL
device carp # Common Address Redundancy Protocol
master1 # cd / usr / src /
master1 # make biuldkernel KERNCONF = MYKERNEL
master1 # make installkernel KERNCONF = MYKERNEL

// On 2m server same.

2. Next, add the following entries to /etc/rc.conf on Master1:

master1 # ee /etc/rc.conf

## Configure CARP
cloned_interfaces = "carp0"
ifconfig_carp0 = "vhid 1 advskew 100 pass seCret 192.168.10.3/24"

#vhid is the number of the group in which the interface works.
#pass is the password for authentication in the group.
#advskew is a priority, the less, the more important.
# 192.168.10.3 / 24 - this will be a general IP.

On the second server (let it be called Slave2), add the following entries to /etc/rc.conf:

slave2 # ee /etc/rc.conf

## Configure CARP
cloned_interfaces = "carp0"
ifconfig_carp0 = "vhid 1 advskew 200 pass seCret 192.168.10.3/ 24 »

3. Next, on both machines we set the option sysctl

master1 # sysctl net.inet.carp.preempt = 1
slave2 # sysctl net.inet.carp.preempt = 1

Add this option on both servers in /etc/sysctl.conf:
net.inet.carp.preempt = 1

# Sends a disconnect signal to carp interfaces during a regular system shutdown.

4. Reboot the server. After reboot we make
ifconfig carp0 on the master system:

carp0: flags = 49 metric 0 mtu 1500
inet 192.168.10.3 netmask 0xffff0000
carp: MASTER vhid 1 advbase 1 advskew 100

we do ifconfig carp0 on the slave system:

carp0: flags = 49 metric 0 mtu 1500
inet 192.168.10.3 netmask 0xffff0000
carp: BACKUP vhid 1 advbase 1 advskew 200

With the fish finished, let's move on to setting up replication.

Replication setup

It’s described in sufficient detail about mySQL replication here , so let's move on to the configuration right away.

On both servers, install mySQL from the ports.

Master1 # cd / usr / ports / databases / mysql55-server
Master1 # make install clean && rehash Copy the

config
Master1 # cp /usr/local/share/mysql/my-huge.cnf /etc/my.cnf from the examples

1. Open config /etc/my.cnf on Master1 and add it
to the [mysqld]

## Master 1 section
auto_increment_increment = 2
auto_increment_offset = 1
server-id = 1
relay-log = mysql-relay-bin
log_slave_updates = 1
skip_slave_start
relay-log-space- limit = 1G
log-bin = mysql-bin

2. On Slave2, add to the config /etc/my.cnf

## Master 2
auto_increment_increment = 2
auto_increment_offset = 2
log-bin = mysql-bin
server-id = 2
relay-log = mysql-relay-bin
log_slave_updates = 1
skip_slave_start
relay- log-space-limit = 1G

// Of course, to optimize the work of mySQL with configs, you will need to work with a “file”. Who is very lazy, or does not want to go into the intricacies of the config, you can leave default. But I would suggest you use the rather convenient tools.percona.com service from Percona Server for configuring, by the way, you can use the percon instead of mySQL-server.

3. We start mysql
/usr/local/etc/rc.d/mysql-server start
Starting mysql.

3. Enter mysql on Master1:
mysql @ master1> show master status;
+ ------------------ + ---------- + -------------- + ---- -------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ---- -------------- +
| mysql-bin.000001 | 499 | | |
+ ------------------ + ---------- + -------------- + ---- -------------- +
1 row in set (0.00 sec)

4. Enter mysql on Slave2:
mysql @ slave2> show master status;
+ ------------------ + ---------- + -------------- + ---- -------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ---- -------------- +
| mysql-bin.000002 | 499 | | |
+ ------------------ + ---------- + -------------- + ---- -------------- +
1 row in set (0.00 sec)

5. Enter mysql on master1 and add users:

mysql @ master1> GRANT REPLICATION SLAVE ON *. * TO repl@192.168. 10.1 IDENTIFIED BY 'replpass';
mysql @ master1> GRANT REPLICATION SLAVE ON *. * TO repl@192.168.10.2 IDENTIFIED BY 'replpass';
mysql @ master1> FLUSH PRIVILEGES;

6. Similarly on slave2:

mysql @ slave2> GRANT REPLICATION SLAVE ON *. * TO repl@192.168.10.1 IDENTIFIED BY 'replpass';
mysql @ slave2> GRANT REPLICATION SLAVE ON *. * TO repl@192.168.10.2 IDENTIFIED BY 'replpass';
mysql @ slave2>

7. On slave2:
mysql @ slave2> CHANGE MASTER TO MASTER_HOST = "192.168.10.1", MASTER_USER = "repl", MASTER_PASSWORD = "replpass", MASTER_LOG_FILE = "mysql-bin.000001", MASTER_LOG_POS = 499;

mysql @ slave2> start slave;

8. On Master1:
mysql @ master1> CHANGE MASTER TO MASTER_HOST = "192.168.10.2", MASTER_USER = "repl", MASTER_PASSWORD = "replpass", MASTER_LOG_FILE = "mysql-bin.000002", MASTER_LOG_POS = 499;

mysql @ master1> start slave;

9. On both masters we do:
mysql @ master1> show slave status \ G The

information we need is Slave_IO_Running and Slave_SQL_Running

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Hooray. Master - Master replication is ready.

Script

At the moment, the bunch is almost ready, there is one moment left.
If Master1 falls over the network, then passive Slave2 will become the active Master using CARP means. Now you need to teach the "fish" to do with a passive Slave -> active Master if the database falls.

Let's write a simple script.

Slave2 # cd / tmp
Slave2 # touch switch_script.sh && chmod + x switch_script.sh
Slave2 # ee switch_script.sh

#!/bin/sh
HOST1='192.168.10.1';   # master on server1
HOST2='192.168.10.2';   # slave  on server2
GENERAL='192.168.10.3'; # General IP-adress
MYSQL='/usr/local/bin/mysql'
# Create infinite loop
x=1
while [ $x -le 5 ];	do 
	${MYSQL} -s -h${HOST1} -urepl -preplpass --connect-timeout=10 -e 'SELECT VERSION()' > /dev/null
	out=$?
		if [ $out -eq 0 ]; then
			echo "server ${HOST1} is OK"
			sleep 60 		# delay 1 min 
		else
   			/usr/local/bin/mysqladmin stop-slave
			/sbin/ifconfig carp0 ${GENERAL} vhid 1 advskew 50 
			echo "FAILED, cannot connect to mySQL"
			echo "This host ${HOST2} became a MASTER "
			exit 0
		fi
done

Run the script:
Slave2 # ./switch_script.sh

To summarize.

We got a fairly simple, but effective fail-safe system based on mySQL Mater-Master (Active-Passive) replication and the CARP network protocol.
Thanks for attention.