System/MySQL/Replication

Configuration

For a more general mysql configuration, see System/MySQL

Edit /etc/my.cnf to have the following options (change server-id if necessary):

...
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
auto-increment-increment = 2
auto-increment-offset = 1
...

And for each database you want to replicate:

replicate-wild-do-table = db1.%
replicate-wild-do-table = db2.%
...

Init and start a slave

First, on the master, dump the data _with_ the --master-data and --single-transaction parameters:

mysqldump -u root -p -R --master-data --single-transaction --databases db1 db2 > dump.sql

Then source it on the slave and start slave:

cat dump.sql | mysql -u root -p
mysql -u root -p -e "start slave"

Replication user on Master

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'passwd';

Slave initialization commands

CHANGE MASTER TO MASTER_HOST = 'x.x.x.x', MASTER_USER = 'repl', MASTER_PASSWORD = 'passwd', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 259, MASTER_SSL = 0;

Repairing after a failed replicated statement

SHOW SLAVE STATUS;
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SHOW SLAVE STATUS;

Auto instanciation script of slave

#!/bin/bash

set -x

REPL_USER=<replication user>
REPL_PASS=<replication pass>

TARGET_USER=root
TARGET_PASS=
TARGET_HOST=<local host>

SOURCE_USER=<source user>
SOURCE_PASS=<source pass>
SOURCE_HOST=<source>

ssh deploy@$SOURCE_HOST "time mysqldump -u $SOURCE_USER -p$SOURCE_PASS -A --master-data | gzip > /tmp/fulldump.sql.gz"
scp deploy@$SOURCE_HOST:/tmp/fulldump.sql.gz /var/tmp/
ssh deploy@$SOURCE_HOST 'rm /tmp/fulldump.sql.gz'

gunzip -c /var/tmp/fulldump.sql.gz | mysql -h 127.0.0.1 -P 3307 -u $TARGET_USER

CHGMASTER=$(gunzip -c /var/tmp/fulldump.sql.gz | head -n 40 | grep 'CHANGE MASTER' | sed 's/;//g')
echo "<CHANGEMASTER>, MASTER_HOST='$SOURCE_HOST', MASTER_USER='$REPL_USER', MASTER_PASSWORD='$REPL_PASS', MASTER_SSL = 0;" | sed "s/<CHANGEMASTER>/$CHGMASTER/g"

Temporarily deactivate Binary Log

SET sql_log_bin = 0;

Nagios check

Check for replication lag by connecting to master1 and master2

#!/bin/sh

# Nagios alert status
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3
STATE_DEPENDENT=4

# Script Variables
REPLUSERNAME=repl
REPLPASSWD=''

REPL2=<remote host>
REPL2USERNAME=repl
REPL2PASSWD=''

CRITICAL_VALUE=3000
WARNING_VALUE=1000

Slave_1=`mysql -h $REPL2 -u $REPL2USERNAME -p$REPL2PASSWD -e "show slave status" | grep bin | cut -f7`
Master_1=`mysql -u $REPLUSERNAME -p$REPLPASSWD -e "show master status" | grep bin | cut -f2`
Diff_1=`expr $Master_1 - $Slave_1`

Slave_2=`mysql -u $REPLUSERNAME -p$REPLPASSWD -e "show slave status" | grep bin | cut -f7`
Master_2=`mysql -h $REPL2 -u $REPL2USERNAME -p$REPL2PASSWD -e "show master status" | grep bin | cut -f2`
Diff_2=`expr $Master_2 - $Slave_2`
echo "Master1 (local):"$Master_1" Slave1:"$Slave_1" ["$Diff_1"] Master2 ("$REPL2"):"$Master_2" Slave2:"$Slave_2" ["$Diff_2"]"

if [ $Diff_1 -gt $CRITICAL_VALUE ]; then
        exit $STATE_CRITICAL
elif [ $Diff_1 -gt $WARNING_VALUE ]; then
        exit $STATE_WARNING
elif [ $Diff_2 -gt $CRITICAL_VALUE ]; then
        exit $STATE_CRITICAL
elif [ $Diff_2 -gt $WARNING_VALUE ]; then
        exit $STATE_WARNING
else
        exit $STATE_OK
fi