For this recipe we got two identical (virtual) nodes, Both Ubuntu 16Lts, Quad CPU, 16G ram, OS on a 16G vda1, and a 2T data partition on vda2
Two network interfaces, 1st is public (192.168.32.x/24) and second in the replication network on 10Gbe (192.168.33.0/24)
DB01:
eth0 192.168.32.13
eth1 192.168.33.14
DB02:
eth0 192.168.32.23
eth1 192.168.33.23
First, set up both nodes for replication, Edit /etc/mysql/mysql.conf.d/mysqld.cnf
<snip>
bind-address = 0.0.0.0
server-id = 10 (20 for DB02)
log_slave_updates = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log-error = /var/log/mysql/error.log
auto_increment_increment = 2
auto_increment_offset = 1
expire_logs_days = 10
max_binlog_size = 100M
</snip>
Then restart Mysql: service mysql restart
On DB01 do mysql -u root -p and login
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000019 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
The file and position are needed for the master configuration on DB02, this is no longer done in the my.cnf file but in the DB, so open up mysql on DB02 and configure:
change master to master_host='192.168.33.13', master_user='slaveuser', master_password='somesupersecretpassword', master_log_file='mysql-bin.000019', master_log_pos=154, master_port=3306
grant replication slave on *.* to slaveuser@'db01.yourdomain.com' identified by 'somesupersecretpassword';
grant replication slave on *.* to slaveuser@'192.168.33.13' identified by 'somesupersecretpassword';
flush privileges;
start slave;
slave status \G;
Now you have one way replication, to make it two way, do a show master status on DB02, and do the same configuration on DB01, replacing .13 with .23 and db01 with db02 and of course the file and position values where applicable.
Now create some databases and see them appear on the other side.
Note, this does not replicate existing databases, this is meant for a CLEAN server. If you have existing databases, you will first need to backup/restore them to the replica node before setting up the replication. If you do any action on a pre-existing DB the replication will halt, when this happens, just do a show master on the source and get the file and position values, then reset the replication on the slave DB (first stop the replication slave with stop slave, start it again when done.
{the Document (F)ault} 