Local Installations - Master-Master Replication

1. Create two ProVision installs on two different servers. These servers can be bare metal or they can be VMs. These servers will be referred to as Rep1 and Rep2.

2. Open an SSH session to Rep1.

3. Confirm that Rep1 can communicate with Rep2 by issuing a ping command:

ping ip-of-Rep2

If the Rep1 cannot communicate with Rep2, consult your system or network administrator to clear this error.

4. Stop the apache2 service using the following command:

service httpd stop

5. Stop the software firewall, if running:

service firewalld stop
sudo systemctl disable firewalld

6. Run the following command to open the MySQL configuration file for editing:

vi /etc/my.cnf

7. Find the line that reads

bind-address = 127.0.0.1

And change it to:

bind-address = 0.0.0.0

If this line does not appear, add it to the bottom of the config.

If you are using an IPv6-only network, use “::” instead of “0.0.0.0”.

8. Add the following lines to the end of the file, then save and exit:

server-id=1

   log-bin="mysql-bin"

    binlog-do-db=name_of_database

    replicate-do-db=name_of_database

    relay-log="mysql-relay-log"

    auto-increment-offset = 1

auto-increment-increment = 10

9. Enter the following command:

service mysql restart

If you get errors, please re-check that you have correctly entered the changes in step (6) to the configuration file, and that you have replaced the name of the database correctly.

10. Create a dump of the current database state:

mysqldump -uroot -p name_of_database > initial.sql

You will need the MySQL root password for this, which can normally be found at:

/var/www/html/data/globals.php

11. Transfer the database dump to Rep2, to serve as the basis for the synchronization:

scp initial.sql username@location-of-rep2:~

You will have to customize this command with a user on Rep2 and its location.  Ex:

scp initial.sql root@67.221.241.164:~

12. Run the following command:

rm /var/lib/mysql/auto.cnf

13. Restart MySQL:

service mysqld restart

14. While still on Rep1, log in to MySQL as root.

mysql -u root -p

15. Run the following commands in MySQL:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'replication_password';

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

16. Run the following command in MySQL:

SHOW MASTER STATUS;

Note the ‘File’ and ‘Position’ columns.  These will be referred to later as “rep1_file” and “rep1_position.”

17. Leave the SSH session to Rep1 open.  Open a second SSH session to Rep2.

18. Confirm that Rep2 can communicate with Rep1 by issuing a ping command:

ping ip-of-Rep1

If the Rep2 cannot communicate with Rep1, consult your system or network administrator to clear this error.

19. Stop the apache2 service using the following command:

service httpd stop

20. Stop the software firewall, if running:

service firewalld stop
sudo systemctl disable firewalld

21. Run the following command to open the MySQL configuration file for editing:

vi /etc/my.cnf

22. Find the line that reads

bind-address = 127.0.0.1

And change it to:

bind-address = 0.0.0.0

If this line does not appear, add it to the bottom of the config.

If you are using an IPv6-only network, use “::” instead of “0.0.0.0”.

23. Add the following lines to the end of the file, then save and exit:

server-id=2

   log-bin="mysql-bin"

    binlog-do-db=name_of_database

    replicate-do-db=name_of_database

    relay-log="mysql-relay-log"

    auto-increment-offset = 2

auto-increment-increment = 10

24. Enter the following command:

service mysql restart

If you get errors, please re-check that you have correctly entered the changes in step (20) to the configuration file, and that you have replaced the name of the database correctly.

25. Run the following command:

rm /var/lib/mysql/auto.cnf

26. Restart MySQL:

service mysqld restart

27. Log into MySQL as root:

mysql -u root -p

28. Run the following commands to delete the current ProVision database and to adopt the MySQL dump that was created in step (10).  If this is not part of the initial ProVision configuration, ensure that proper backups have been made and are accessible before deleting.

DROP DATABASE name_of_database;

CREATE DATABASE name_of_database;

USE name_of_database;

source /home/root/initial.sql;

29. While still in MySQL on Rep2, run the following commands:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'replication_password';

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

30. Run the following command in MySQL:

SHOW MASTER STATUS;

Note the ‘file’ and ‘position’ columns.  These will be referred to later as “rep2_file” and “rep2_position.”

31. On Rep1, in MySQL, run the following commands:

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST = 'location_of_rep2',MASTER_USER = 'replicator', MASTER_PASSWORD = 'replication_password', MASTER_LOG_FILE = 'rep2_file', MASTER_LOG_POS = rep2_position;

START SLAVE;

Note that you must replace “location_of_rep2”, “replication_password”, “rep2_file” and “rep2_position” with values from earlier in this tutorial.

32. On Rep2, in MySQL, run the following commands:

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST = 'location_of_rep1',MASTER_USER = 'replicator', MASTER_PASSWORD = 'replication_password', MASTER_LOG_FILE = 'rep1_file', MASTER_LOG_POS = rep1_position;

START SLAVE;

Note that you must replace “location_of_rep1”, “replication_password”, “rep1_file” and “rep1_position” with values from earlier in this tutorial.

33. On Rep1, exit MySQL and enter the following command:

service httpd start

34. On Rep2, exit MySQL and enter the following command:

service httpd start

35. Replication should now be running between the two servers. To test this, log in to MySQL on both Rep1 and Rep2, switch to the ProVision database on both and run the following command:

SELECT COUNT(*) FROM log;

This will show how many logs are recorded by each database. Now, use a web browser to navigate to the ProVision GUI on Rep1 and log in. This will produce additional log events within the ProVision database. 

Return to MySQL on both databases and run the above command again. You should see two things: that the log count has increased, and that the count is identical on both servers. If this matches what you see then replication is working.

To complete the test now use a web browser to navigate to the ProVision GUI on Rep2 and log in. This should again produce an equal increase in log counts on both servers.