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.
   • slave-skip-errors = 1032
   • Comment out "bind-address = 127.0.0.1".
3. Restart mysqld on both machines.
4. If MySQL is using row-based replication then it's very important that the backup machine has nothing write to its database between the following "create database enswitch" and the "show master status", otherwise the "show master status" will not include the binary log for those writes, and those changes won't be replicated back to the primary machine. Any discrepancy at all between databases can break row-based replication. Change all Enswitch processes to use the database on the master machine, and as the MySQL root user run "set global read_only = 1" on the backup machine to ensure nothing writes to it.
5. 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
6. On the primary machine, do the following SQL command:
   • reset slave;
7. If not using NFS, copy enswitch.sql to the backup machine.
8. 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;
9. On both machines, do the following SQL commands:
   • create user 'replicate'@'%' identified by '<password>';
   • alter user 'replicate'@'%' identified with mysql_native_password by '<password>'; # Run this only if using MySQL 8 or later
   • 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.
10. If read_only was enabled on the backup machine run "set global read_only = 0" to disable it, and optionally change any desired Enswitch processes back to using the database on the backup machine.
11. Optionally, delete the enswitch.sql file(s) to save disk space.