MySQL replication allows you have one MySQL machine crash and still have the entire database available and update to date. It works by each MySQL machine acting as the slave to the other, and copying updates to the database in the other machine's binary logs in real time. When used in combination with corosync and pacemaker it gives high availability for the database.

To configure replication

1. If using NFS, ensure that it's configured and working on both machines.
2. Edit /etc/my.cnf or /etc/mysql/my.cnf on both machines, and set the following. Don't set auto_increment_increment before "enswitch install mysql-primary" has been run in the MySQL installation.
   • log_bin = /var/lib/mysql/mysql-bin.log
   • binlog_format = statement
   • server-id = 1 on the primary machine, and server-id = 2 on the backup machine.
   • auto_increment_increment = 2
   • auto_increment_offset = 1 on the primary machine, and auto_increment_offset = 2 on the backup machine.
   • Comment out "bind-address = 127.0.0.1".
   • Some MySQL installations may require to explicitly set either the "relay-log" or the "relay-log-index" options. If that is the case, in the primary machine set "relay-log = primary-relay-bin" and in the backup machine set "relay-log = backup-relay-bin" (the hostname of each machine could also be used as the prefix before the "-relay-bin" part).
3. Restart mysqld on both machines.
4. On the primary machine, do:
   • mysqldump -u root --single-transaction --master-data -q -p enswitch >/var/lib/enswitch/tmp/enswitch.sql
   • head -n 100 /var/lib/enswitch/tmp/enswitch.sql | grep MASTER
5. On the primary machine, do the following SQL command:
   • reset slave;
6. If not using NFS, copy enswitch.sql to the backup machine.
7. On the backup machine, do the following SQL commands:
   • drop database enswitch;
   • create database enswitch;
   • use enswitch;
   • \. /var/lib/enswitch/tmp/enswitch.sql
   • reset master;
   • show master status;
8. On both machines, do the following SQL commands:
   • create user 'replicate'@'%' identified by '<password>';
   • grant super, replication client, replication slave, reload on *.* to replicate@'%';
   • change master to master_host='<IP address of other machine>', master_user='replicate', master_password='<password>', master_log_file='<master log file on other machine>', master_log_pos=<master log position on other machine>;
   • slave start;
   • show slave status;
   The "show slave status" should report no errors, and the seconds behind master should be zero or a small number that gradually reduces to zero over a matter of minutes.
9. Optionally, delete the enswitch.sql file(s) to save disk space.