API Gateway: Reinitialize replication in a multi-node cluster
search cancel

API Gateway: Reinitialize replication in a multi-node cluster

book

Article ID: 44402

calendar_today

Updated On:

Products

CA API Gateway

Issue/Introduction

This procedure will explain how to reinitialize failed replication in a multi-node cluster during a maintenance window.

The CA API Gateway uses MySQL replication to provide database failover and availability should one Gateway appliance or database server become unavailable or degraded. MySQL replication will ensure that a duplicated copy of a database object is maintained in one or more locations. The Gateway uses a master-master implementation in a multi-node environment to ensure that database changes to one host are replicated to the other database host.

MySQL replication is capable of repairing itself if the other database node is unavailable. Replication can repair itself after a node in the cluster experiences a graceful shutdown, MySQL server shutdown, or a network outage. Replication will fail if a data consistency error is experienced.

The following data may be visible when running the SHOW SLAVE STATUS query against the local MySQL database:

Slave_IO_Running: No 
Slave_SQL_Running: No 

The following log entries may be present in the Gateway log files:

2380 WARNING "Error accessing host/database 
2381 WARNING "Replication failing for host/database 

Environment

This article applies to all supported API Gateway versions.

Cause

MySQL replication can break for a multitude of reasons, which falls outside the scope of this KB article.

Resolution

Important notes:

  • The processes below is strongly recommended to be performed during a maintenance window as the API Gateway 'ssg' service should be disabled during this process.
  • The processes below assume that the primary node contains the most accurate/up-to-date database. Accommodations will be needed if up-to-date database is on secondary node instead of the primary node.
  • The processes below may result in a significant period of downtime if a large quantity of audits (typically over 500,000) are present in the local Gateway database. It is recommended to purge the audit records in advance of this procedure via at least one of the following articles (2nd one recommended):
    1. KB 42874 - Deleting old audit events from the local Gateway database 
    2. KB 42833 - Removing audit records from the Gateway database in a multi-node cluster without downtime
  • Make sure the appropriate process is chosen below for the Gateway version appliances

Gateway 10.x and 11.x process

If running MySQL on an API Gateway 10.x appliance, then this process applies. If you are running on a Gateway 9.x appliance, please start at the Gateway 9.x process heading above instead.

The following procedure should be executed on Gateway 10.x appliances in order to reinitialize replication in a multi-node cluster:

  1. Stop the API Gateway 'ssg' service on Primary/Secondary AND all PROCESSING nodes: service ssg stop
  2. Backup the database on the PRIMARY node first as a precaution: mysqldump --all-databases --set-gtid-purged=OFF | gzip > ~/all_databases_`date '+%Y%m%d_%T'`.sql.gz
    • Make sure that the last line of the newly created SQL file contains "-- Dump completed <date-time>"
    • Store the file for future use in case the database needs to be restored.
    • Consider also taking a VM snapshot if possible to most easily restore a virtual appliance.
  3. Stop slave replication on both nodes: mysqladmin stop-slave
    If you received deprecated WARNING message RUN # mysqladmin stop-replica 

    # mysqladmin stop-slave

    WARNING: stop-slave is deprecated and will be removed in a future version. Use stop-replica instead.

    Replication stopped

  4. Reset the master configuration on both nodes: mysql -e "reset master"
  5. Reset the slave configuration on both nodes: mysql -e "reset slave; reset slave all"
  6. Execute the create_slave.sh script on the SECONDARY node: /opt/SecureSpan/Appliance/bin/create_slave.sh
    1. Follow the prompts of the script, keeping the following in mind:
      • Provide the FQDN of the primary node
      • Enter yes to the prompt "Do you want to clone a database from $Master (yes or no)?"
  7. On the secondary node after step 6 is completed, reset the master configuration one more time: mysql -e "reset master" <-- IMPORTANT STEP DIFFERENT FROM 9.4 PROCEDURE! DO NOT MISS!
  8. Execute the create_slave.sh script on the PRIMARY node: /opt/SecureSpan/Appliance/bin/create_slave.sh
    1. Follow the prompts of the script, keeping the following in mind:
      • Provide the FQDN of the secondary node
      • Enter no to the prompt "Do you want to clone a database from $Master (yes or no)?"
  9. Start the Gateway 'ssg' service on Primary/Secondary AND all PROCESSING nodes: service ssg start
  10. Query the status of the replication on both nodes: mysql -e "show slave status\G"
  11. Verify both nodes return the following lines:
    • Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
      Seconds_Behind_Master: 0

Replication should be re-initialized at this point. The above output above indicates that the master/slave relationship is functioning.

Gateway 9.x process

If running MySQL on an API Gateway 9.x appliance, then this process applies. If you are running on a Gateway 10.x appliance, please skip this section and start at the Gateway 10.x process heading.

The following procedure should be executed on Gateway 9.x appliances in order to reinitialize replication in a multi-node cluster:

  1. Stop the API Gateway 'ssg' service on on Primary/Secondary AND all PROCESSING nodes: service ssg stop
  2. Backup the database on the PRIMARY node first as a precaution: mysqldump --all-databases > ~/all_databases_`date '+%Y%m%d_%T'`.sql
    • Make sure that the last line of the newly created SQL file contains "-- Dump completed <date-time>"
    • Store the file for future use in case the database needs to be restored.
    • Consider also taking a VM snapshot if possible to most easily restore a virtual appliance.
  3. Stop slave replication on both nodes: mysqladmin stop-slave
  4. Reset the master configuration on both nodes: mysql -e "reset master"
  5. Reset the slave configuration on both nodes: mysql -e "reset slave; reset slave all"
  6. Execute the create_slave.sh script on the SECONDARY node: /opt/SecureSpan/Appliance/bin/create_slave.sh
    1. Follow the prompts of the script, keeping the following in mind:
      • Provide the FQDN of the primary node
      • Enter yes to the prompt "Do you want to clone a database from $Master (yes or no)?"
  7. Execute the create_slave.sh script on the PRIMARY node: /opt/SecureSpan/Appliance/bin/create_slave.sh
    1. Follow the prompts of the script, keeping the following in mind:
      • Provide the FQDN of the secondary node
      • Enter no to the prompt "Do you want to clone a database from $Master (yes or no)?"
  8. Start the Gateway 'ssg' service on both nodes: service ssg start
  9. Query the status of the replication on both nodes: mysql -e "show slave status\G"
  10. Verify both nodes return the following lines:
    • Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
      Seconds_Behind_Master: 0

Replication should be re-initialized at this point. The above output above indicates that the master/slave relationship is functioning.

 

Powered by Wolken Software