How To setup Master-Master MySQL Replication in a Software form factor CA API Gateway multi-nodes implementation

Document ID : KB000111626
Last Modified Date : 24/09/2018
Show Technical Document Details
Introduction:

In the Appliance form factor, a series of  pre-built scripts are you to easily setup MySQL replication in a Master-Master mode for clustered multi-nodes Gateway.
However for Software form factor Gateway, scripts are not included and this can be a useful reading and good exercise to get familiar with MySQL replication setup. Recommended to be tested in a low environment.
The following example is based on CA API Gateway 9.3 and MySQL 5.5.x
 
Background:

Before to start:
• Verify Gateway (Software) pre-requisites https://docops.ca.com/ca-api-gateway/9-3/en/install-configure-upgrade/configure-the-software-gateway/install-the-gateway-software
• Ensure both nodes are capable to resolve their own hostnames
Environment:
API Gateway 9.x
Instructions:
1. Install MySQL
 
Reference URL: https://dev.mysql.com/downloads/mysql/5.5.html?os=31
 
Extract RPMs from the TAR file:
# tar xvf MySQL-5.5.58-1.el7.x86_64.rpm-bundle.tar

Install required RPMs:
# rpm -Uvh MySQL-server-5.5.58-1.el7.x86_64
# rpm -Uvh MySQL-client-5.5.58-1.el7.x86_64
# rpm -Uvh MySQL-shared-compat-5.5.58-1.el7.x86_64
# rpm -Uvh MySQL-shared-5.5.58-1.el7.x86_64

2. Install Gateway RPM on both nodes:
 
# rpm -Uvh ssg-9.3.00-7814.noarch.rpm

3. Setup Gateway:
# /opt/SecureSpan/Gateway/runtime/bin/setup.sh
 
NOTE: setup both nodes to connect their self as main DB. This to allow Secondary node to create the SSG database and so there is no need to copy it over from the Primary. A change needs to be made later on the Secondary to reconnect it to the Primary DB. Setup both nodes to failover on the Secondary.

IMPORTANT: DO NOT START the Gateway till we complete the entire setup
 
4. On both nodes, open MySQL console and set log-bin trust function, create a replication user and grant to it permissions:
 
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
mysql> CREATE USER 'repluser'@'%' IDENTIFIED BY '7layer';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';
mysql> FLUSH PRIVILEGES;

5. On both nodes, create and edit MySQL a configuration file /etc/my.cnf and paste below replication settings 
NOTE: the following is a basic operational setup. Fine tuning and customization of the database settings can be defined later.

On the Primary node:

[mysqld]
server-id=1
log-bin=mysql-bin
relay-log=mysql-relay-log
auto-increment-increment = 2
auto-increment-offset = 1
binlog-do-db=test
binlog-do-db=ssg
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=test
replicate-do-db=ssg
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=information_schema


On the Secondary node:

[mysqld]
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-log
auto-increment-increment = 2
auto-increment-offset = 2
binlog-do-db=test
binlog-do-db=ssg
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=test
replicate-do-db=ssg
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=information_schema


6. Restart MySQL service on both nodes to allow new settings to take effect
 
# /etc/init.d/mysql restart

7. Run MySQL console and verify “server ID” is correctly showing on both nodes:
 
SHOW VARIABLES LIKE 'server_id';

here an example from the Primary node...
 
mysql> SHOW VARIABLES LIKE 'server_id';
Variable_name Value
server_id 1

... and from the Secondary
 
mysql> SHOW VARIABLES LIKE 'server_id';
Variable_name Value
server_id 2

 
8. Check Master status on Primary node and then setup and start the SLAVE mode on Secondary node.

On the Primary node execute SHOW MASTER STATUS\G and take note of mysql-bin.xxxxx file name and its position.

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 107

On Secondary node execute the following replacing <hostname of the primary> with the correct hostname and master_log_file and master_log_pos values with the information we noted previously.

mysql> CHANGE MASTER TO MASTER_HOST='<HOSTNAME
PRIMARY>',MASTER_USER='repluser',MASTER_PASSWORD='7layer',MASTER_LOG_FILE='mysqlbin.
000001',MASTER_LOG_POS=107;


mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G
 
9. Set up replication to Master-Master by repeating point 8 steps in the opposite direction.
On Secondary execute SHOW MASTER STATUS\G and take note of file/position as we did previously 
 
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 107

On Primary execute the following:
mysql> CHANGE MASTER TO MASTER_HOST='HOSTNAME SECONDARY',MASTER_USER='repluser',MASTER_PASSWORD='7layer',MASTER_LOG_FILE='mysqlbin.
000001',MASTER_LOG_POS=107
;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G
 
10. Run again setup on the Secondary node
 
# /opt/SecureSpan/Gateway/runtime/bin/setup.sh
 
Choose option 2 "Configure CA API Gateway" and again option 2 "Database Connection".
This time, point main DB connection to Primary server. Keep failover onto Secondary.

11. Verify on both node that the node properties file has been updated correctly:
 
# cat /opt/SecureSpan/Gateway/node/default/etc/conf/node.properties

Check that for both nodes, the following 2 lines are same and showing correct DB connections:

node.db.config.main.host=<PRIMARY HOSTNAME>
node.db.config.failover.host=<SECONDARY HOSTNAME>

12. Start Gateway on Primary, connect through Policy Manager and install license.
 
# /opt/SecureSpan/Gateway/runtime/bin/gateway.sh start

13. Start Gateway on Secondary and from the Policy Manager, check Cluster status through Dashboard