Configuring Circular Replication

Create a replication user on each MySQL instance.

Using the MySQL command line client or a GUI tool such as MySQL Workbench, enter the following on the PRIMARY index:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'<secondary index IP address>' IDENTIFIED BY '<your_password>';

Using the MySQL client again, enter the following on the SECONDARY index:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'<primary index IP address>' IDENTIFIED BY '<your_password>';

Enable binary logging on the PRIMARY host.

Log into the Index Virtual Machine (VM) of the PRIMARY host and shut down the MySQL service by entering the following:
```
systemctl stop mysqld
```

Make a back-up instance of the file /etc/my.cnf (for example, /tmp/my.cnf), and then edit /etc/my.cnf by entering the following in the [mysqld] section:

log-bin=Primary1-mysql-bin
server-id=1
#Replication increments to avoid primary key auto-increment 
#collisions for 2 hosts
auto_increment_increment=2 
auto_increment_offset=1
#Set the db/tables to replicate
replicate-do-db=ipcr_db 
replicate-ignore-table=ipcr_db.log
replicate-ignore-table=ipcr_db.heartbeats
#Set the master for replication reporting (optional)
report-host= secondary host ip address

Note:

If copying and pasting directly from this document, ensure there are no erroneous carriage returns impacting the configuration "my.cnf" file formatting. This could prevent MySQL Server from starting properly.

Make sure the following lines are in the mysqld section:
```
binlog-format=mixed
slave-skip-errors=1032
sync_binlog=1
```

Enable binary logging on the SECONDARY host.

Using a secure shell client (SSH), log into the Index VM of the SECONDARY host. Then shut down the MySQL service by entering the following :
```
systemctl stop mysqld
```
Make a back-up instance of the file /etc/my.cnf (for example, /tmp/my.cnf), and then edit /etc/my.cnf by entering the following in the [mysqld] section:
```
log-bin=Secondary2-mysql-bin
server-id=2
#Replication increments to avoid primary key auto-increment #collisions for 2 hosts
auto_increment_increment=2
auto_increment_offset=2
#Set the db/tables to replicate
replicate-do-db=ipcr_db
replicate-ignore-table=ipcr_db.log
replicate-ignore-table=ipcr_db.heartbeats
#Set the master for replication reporting (optional)
report-host=primary host address
```
Note:
If copying and pasting directly from this document, ensure there are no erroneous carriage returns impacting the configuration "my.cnf" file formatting. This could prevent MySQL Server from starting properly.
Make sure the following lines are in the mysqld section:
```
binlog-format=mixed slave-skip-errors=1032 sync_binlog=1
```

Start the MySQL instance on both the PRIMARY and SECONDARY hosts by entering the following:

systemctl restart mysqld

WARNING:

Ensure there are no connections to the ISR Record and Store Server (RSS), and that the Dashboard on both primary and secondary hosts is disabled. To disable the Dashboard via the CLI, issue the following command:

systemctl stop puma

To restart the Dashboard via the CLI, issue the following command:

systemctl start puma

Using the MySQL client, check the Master status on the PRIMARY host by entering the following:

mysql> FLUSH TABLES WITH READ LOCK; 
mysql> SHOW MASTER STATUS;

The following is an example of the output from the above commands.

File	Position	Binlog_Do_DB	Binlog_Ignore_DB
Primary1-mysql-bin.000002	98	test	manual, mySQL

Make a note of the filename and position values from the output table.

Free the read lock by entering the following:

mysql> UNLOCK TABLES;

Using the MySQL client, on the SECONDARY host, edit the MySQL replication Slave configuration using the file and position values from the output in Step 5, and enter the following using the CHANGE MASTER command:

RESET SLAVE; CHANGE MASTER TO MASTER_HOST='<primary host IP address>', MASTER_USER='repl', MASTER_PASSWORD='example123', MASTER_LOG_FILE='Primary1-mysql-bin.000002', MASTER_LOG_POS=98;

Start the Slaves & ensure there are no errors in the "MySQL logs" by entering the following:

mysql> START SLAVE;

Check the Master status on the SECONDARY host by entering the following:

mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS;

The following is an example of the output from the above commands.

File	Position	Binlog_Do_DB	Binlog_Ignore_DB
Secondary2-mysql-bin.000002	98	test	manual, mySQL

Make a note of the filename and position values from the output table.

Free the read lock by entering the following:

mysql> UNLOCK TABLES;

Using the MySQL client, on the PRIMARY host, edit the MySQL replication Slave configuration using the filename and position values from the output in Step 10 (from the SECONDARY host), and enter the following using the CHANGE MASTER command:

RESET SLAVE; CHANGE MASTER TO MASTER_HOST='<secondary host IP address>', MASTER_USER='repl', MASTER_PASSWORD='example123', MASTER_LOG_FILE='Secondary2-mysql-bin.000002', MASTER_LOG_POS=98; START SLAVE;

The following commands, run on each host, display the current replication status. To improve the formatting, append the command with \G.

mysql>SHOW MASTER STATUS \G mysql>SHOW SLAVE STATUS \G