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=fastprod.com
REPL2USERNAME=repl
REPL2PASSWD=''

CRITICAL_VALUE=3000
WARNING_VALUE=1000

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

iSlave_2=`mysql -u $REPLUSERNAME -p$REPLPASSWD -e "show slave status" | grep bin | cut -f7`
iMaster_2=`mysql -h $REPL2 -u $REPL2USERNAME -p$REPL2PASSWD -e "show master status" | grep bin | cut -f2`
iDiff_2=`expr $iMaster_2 - $iSlave_2`
echo "Master1 (local) Log Position:"$iMaster" Slave1 Log Position:"$iSlave_1 " Diff1:"$iDiff_1" Master2 ("$REPL2") Log Position:"$iMaster_2" Slave2 Log Position:"$iSlave_2" Diff2:"$iDiff_2

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