MySQL Master/Master Replication for On-Prem

Sometimes you want to scale your MySQL deployment across multiple machines with replication to add redundancy and increase operational efficiency. With a master-master replication, we can copy data from either one server to another one, with read/write events on either server.

To get started, make sure to run through steps 1-10 in this post. After successfully implementing a Master/Slave replication, the following 3 steps should be a breeze in finalizing a Master/Master set up:

1. Grant replication slave permission on db2

# mysql -u root -p
mysql> grant replication slave on *.* TO [replication-username]@'[db1-ip]' identified by '[replication-password]';
mysql> flush privileges;
mysql> quit

2. Get the master log file and position on db2 for use in replicating from db2 to db1

mysql> SHOW MASTER STATUS;

+------------------+--------------------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000014 |        11 | cloud9       | mysql            |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)

3. Finalize replication on db1

# mysql -u root -p
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='[db2-ip]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-db2-status]',
MASTER_LOG_POS=[log-position-listed-on-db2-status];
mysql> START SLAVE;

Finally, in you <tomcat>/web/conf/context.xml, your url connection string would look like this with '&' character URI encoded to '&'

jdbc:mysql://address=(protocol=tcp)(type=master)(host=db1-ip)(port=3306),address=(protocol=tcp)(type=master)(host=db2-ip)(port=3306)/cloud9?failOverReadOnly=false&secondsBeforeRetryMaster=60

For more information, check out this great post by Digital Ocean