- Server1 IP: 10.1.1.1
- Server2 IP: 10.1.1.2
- We will completely replicate all databases
- Servers are not in production yet. If they are – be careful when rsyncing data
- 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):
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
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 comes back up, replication will be intact.