Failsafe system based on mySQL replication and CARP network protocol

And so the problem.

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

A little about the "fish".

First, let's deal with RP

CARP (from the Common Address Redundancy Protocol) is a network protocol whose main task is to use one 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 the 2nd server we do the same.



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



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 is running.

#pass is the password for group authentication.

#advskew is a priority, the less, the more important.

# 192.168.10.3 / 24 - this will be a common IP address.



On the second server (Let it be conditionally called Slave2) add to /etc/rc.conf records:



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, set the sysctl option.



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

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



Add on both servers in /etc/sysctl.conf this option:

net.inet.carp.preempt = 1



# Sends a signal to disable the carp interfaces when the system is turned off.



4. Reboot the server. After rebut do

ifconfig carp0 on master system:



carp0: flags = 49 metric 0 mtu 1500

inet 192.168.10.3 netmask 0xffff0000

carp: MASTER vhid 1 advbase 1 advskew 100



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, move on to setting up replication.

Replication setup

The details about mySQL replication are described here , so we’ll go straight to the configuration.



On both servers, install mySQL from ports.



Master1 # cd / usr / ports / databases / mysql55-server

Master1 # make install clean && rehash



We copy from config examples

Master1 # cp /usr/local/share/mysql/my-huge.cnf /etc/my.cnf



1. Open the /etc/my.cnf config on Master1 and add

in section [mysqld]



## Master 1

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 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, in order 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 subtleties of the config, then you can leave the default. But I would suggest that you use the rather convenient service tools.percona.com from Percona Server for configuring configs, by the way, you can use the perkon itself instead of mySQL-server.



3. We start mysql

/usr/local/etc/rc.d/mysql-server start

Starting mysql.



3. Enter mysql on Wizard1:

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 wizard1 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> FLUSH PRIVILEGES;



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 bundle is almost ready, one moment remains.

If Master1 falls over the network, then using CARP, passive Slave2 will become an active Master. 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

Let's sum up.

We have turned out to be quite simple, but at the same time an effective fault-tolerant system based on mySQL Mater-Master (Active-Passive) replication and network protocol CARP.

Thanks for attention.