Configuring ProVision for 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 apache2 stop

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

vi /etc/mysql/mysql.conf.d/mysqld.cnf

6. Find the line that reads:

bind-address = 127.0.0.1

And change it to:

bind-address = 0.0.0.0
Note:  you may wish to consult with your security department to determine the least-permissible values.

7. 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
Note:  “name_of_database” is a placeholder for the name of the 6connect ProVision database.  This is typically “provision” but may be different based on the choices made during installation.

8. 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.


9. 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

Note you will also need to change the database name as in the previous step.


10. 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 username@67.221.241.164:~
Note:  You will need to customize this command with your credentials on Rep2.


11. Run the following command:

rm /var/lib/mysql/auto.cnf

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

mysql -u root -p

13. Run the following commands in MySQL:

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

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
Note:  The specific requirements for password strength are governed by your local policies.



14. 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.”


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


16. 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.


17. Stop the apache2 service using the following command:

service apache2 stop


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

vi /etc/mysql/mysql.conf.d/mysqld.cnf


19. Find the line that reads

bind-address = 127.0.0.1

And change it to:

bind-address = 0.0.0.0
Note:  you may wish to consult with your security department to determine the least-permissible values.


20. 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
Note:  “name_of_database” is a placeholder for the name of the 6connect ProVision database.  This is typically “provision” but may be different based on the choices made during installation.


21. 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.


22. Run the following command:

rm /var/lib/mysql/auto.cnf


23. Log into MySQL as root:

mysql -u root -p


24. Run the following commands to delete the current ProVision database and to adopt the MySQL dump that was created in step (9).  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;


Note:  you may have to customize the final line with the location of the “initial.sql” file that was transferred to Rep2 in step (10).
Note:  you will have to replace name_of_database with the 6connect ProVision database chosen at install.


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

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

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
Note:  The specific requirements for password strength are governed by your local policies.



26. 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.”


27. 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.


28. 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.


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

service apache2 start


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

service apache2 start


31. 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.


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

Note:  local security policies may mandate specific firewall settings. See your network administrator for acceptable firewalld policies.

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”.

Note:  you may wish to consult with your security department to determine the least-permissible values.

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
Note:  “name_of_database” is a placeholder for the name of the 6connect ProVision database.  This is typically “provision” but may be different based on the choices made during installation.

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

Note you will also need to change the database name as in the previous step.

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:~
Note:  You will need to customize this command with your credentials on Rep2. You may also have to customize this command to use it on an IPv6-only network.

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'@'%';
Note:  The specific requirements for password strength are governed by your local policies.

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


Note:  local security policies may mandate specific firewall settings. See your network administrator for acceptable firewalld policies.


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”.

Note:  you may wish to consult with your security department to determine the least-permissible values.

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
Note:  “name_of_database” is a placeholder for the name of the 6connect ProVision database.  This is typically “provision” but may be different based on the choices made during installation.


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;


Note:  you may have to customize the final line with the location of the “initial.sql” file that was transferred to Rep2 in step (10).
Note:  you will have to replace name_of_database with the 6connect ProVision database chosen at install.

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

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

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
Note:  The specific requirements for password strength are governed by your local policies.


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.


A Note On Passwords!

For local password storage, you will need to sure that the encryption key is "in sync" across your replicated instances. 

The way ProVision/ACP works is that when we have to store a password to an external system we encrypt it in our database, then store the encryption key outside the database on the file system.  This way if there is a database compromise, access credentials are unusable. 

The encryption key is stored in a file described in the "secure_dir" parameter in the ProVision/ACP config.php file.  It is typically:

'secure_dir' => '/var/www/secure/'

The file will be named "[your-database].keyFile".

If you have installations in master-master configuration, and one or more of the keyFiles is out of sync with the rest of the cluster, that out-of-sync node will be encrypting passwords that the other nodes cannot decrypt.  

The solution for this is to just pick a keyFile and replicate it across all the nodes.

If a keyFile is lost it means that no stored passwords will be able to be decrypted.  This can be fixed by just re-entering the passwords. 

After copying the KeyFile, please ensure that you check permissions on the file.