Fail-safe Master-master MySQL replication

Reference:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Assumptions:

1. Server1 IP: 10.1.1.1
2. Server2 IP: 10.1.1.2
3. We will completely replicate all databases
4. Servers are not in production yet. If they are – be careful when rsyncing data

Notes:

• This setup can be used in HA environments, provided that only one of the servers is used for writes (INSERTs, UPDATEs, DELETEs) at a given moment
• In case one of the servers crashes, and data corruption occurs (by corrupting binlogs or innodb data files), there is the possibility that replication will fail and data will have to be re-synchronized from the known good server to the other one

Run (on 1st server, replace “password” with actual password):
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.%' IDENTIFIED BY 'password';

Stop mysql on both servers, rsync the mysql datadir from 1st to 2nd server, delete any existing binlogs or relay logs.

The global my.cnf should be quite similar on both servers (including any parameters used for tuning, like max_heap_table_size, etc), apart from the replication-related settings (very important: sync_binlog, and auto_increment_*, which ensure fail-safe replication):
Server1:
server-id = 1
master-host = 10.1.1.2
master-user = repl
master-password = password
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
#adjusted due to mysql 5.x placing relay logs in wrong directory
relay-log=/var/lib/mysql/slave-relay.log
relay-log-index=/var/lib/mysql/slave-relay-log.index
relay-log-info-file=/var/lib/mysql/slave-relay.info

Server2:
server-id = 2
master-host = 10.1.1.1
master-user = repl
master-password = password
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
#adjusted due to mysql 5.x placing relay logs in wrong directory
relay-log=/var/lib/mysql/slave-relay.log
relay-log-index=/var/lib/mysql/slave-relay-log.index
relay-log-info-file=/var/lib/mysql/slave-relay.info

Start mysql on both servers, run:
SHOW MASTER STATUS;
SHOW SLAVE STATUS \G
on both servers to check the status.

In order to achieve a HA solution, we can setup heartbeat on both servers, similar to the setup in Service high-availability using open-source tools

We can use mon or monit to detect mysql failures.

In case we are using monit, following configuration can be used:

check process mysqld with pidfile /var/run/mysql/mysql.pid
if failed host 127.0.0.1 port 3306 protocol mysql then exec "/etc/monit-scripts/stop-heartbeat.sh"

/etc/monit-scripts/stop-heartbeat.sh itself should contain:
#!/bin/bash
/etc/init.d/mysql stop
/usr/lib64/heartbeat/hb_standby local

sync_binlog=1 indicated above will add a performance loss, since every query / transaction has to be logged immediately to the binlog. It can be safely disabled, but in case of hardware failures on one of the MySQL servers, there’s no guarantee that after it will come back up, replication will be intact.