System/MySQL/Replication

Configuration

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 --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;

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