API Gateway: Managing binlog (binary log) files with the manage_binlogs.sh maintenance script.

Products

• CA API Gateway

Issue/Introduction

A MySQL database server uses binary logs and relay logs that are used to manage changes between Gateway database instances in a Gateway cluster. The binary logs contain the queries and instructions originating from a master database that are awaiting transmission to its slave database.

The relay logs contain the queries and instructions originating from a master database that are awaiting execution on the slave database. If replication fails or is delayed for a period then one or both types of logs may start to accrue on the file system of the Gateway appliance. Unchecked growth of these files can result in over-utilization of the storage allocated to a Gateway appliance. This allows for excess disk space consumption which could cause a MySQL service outage and thus a Gateway application outage too.

The binary log management script will periodically verify the current running state of replication on the local Gateway database. If replication is functional and not delayed extensively then it will remove old binary and relay log files from the file system. This ensures that no data is lost (by verifying the state of replication) and that no excessive storage utilization occurs.

Environment

This article applies to all versions of the CA API Gateway.

Resolution

Implementation

The binary log management script should be stored in a central location. Traditionally, the script is located in /usr/local/bin/ or /opt/SecureSpan/Appliance/bin/. The invocation of this script is typically handled by the Gateway appliance's default scheduled task handler--crond. It is expected that this script will be configured to run via crontab. If you need assistance with configuring the Gateway appliance to run this script via cron then please open a new Support request.

1. Download the file attached to this article (manage_binlogs.sh) to a workstation.
2. Upload the script to both Gateway appliances ruining the master and secondary SSG DB via SFTP or SCP as the ssgconfig user.
3. Move the script from the ssgconfig user's home directory to the desired location.
4. Make it executable: chmod u+x manage_binlogs.sh
5. Open the script and edit the following properties to reflect the current configuration:

SLAVE="slave.example.net"
ROOTDBPWD="password"

NOTIFY_SMTP_MAIL="yes"
NOTIFY_WEB_SERVICE="yes"  
NOTIFY_TO="[email protected]"

Note: The values for "SLAVE" and  "ROOTDBPWD" should be set to the hostname of the other Gateway database node and the password for the privileged MySQL user.

6. Run the script with a '-f' flag: ./manage_binlogs.sh -f
7. Complete steps 1-8 on the remaining Gateway database node.
8. Set up the crontab on both appliances to run this script.

Enabling Email Alerts for Replication Status

It may be necessary or preferable to configure the Gateway to transmit emails or other types of messages when the replication state is not in a good condition. If this process is required or desired then do the following:

1. Log in to the Policy Manager as an administrative user
2. Publish a Web API with the following parameters:

• Name: Replication Monitoring Service
• Target: None
• Resolution Path: /replmail

3. Import the policy included in the compressed archive attached to this article

Testing the Implementation

This script will send an email notification if it is unable to manage the binary log files and if email has been configured for use. This serves as a helpful notification for a replication failure as this process should only fail if replication is not running or is not configured. The following procedure is optional and can be run in any environment where this script is being deployed for the first time.

1. Break replication on both database nodes: mysql -e 'stop slave'.
2. Run the script on each database node in the cluster in verbose mode: ./manage_binlogs.sh -f -v
3. Start replication on both database nodes: mysql -e 'start slave'
4. Check the status of replication: mysql -e 'show slave status\G'

Additional Information

Both the manage_binlogs.sh script and replication_email_service.xml policy example are included in the ZIP file attached to this article.

By using the manage_binlogs.sh script allows to resolve disk space issues without outage or interruptions.