AWS RDS Blue/Green Deployments

ProxySQL can monitor an Amazon RDS Multi-AZ DB instance blue/green deployment and keep traffic on the stable blue endpoint names while AWS promotes the green deployment. This guide separates AWS metadata, behavior seen in a released test trace, and the policy ProxySQL applies to that metadata.

Availability

AWS RDS blue/green monitoring is available in ProxySQL 3.0.10, 3.1.10, and 4.0.10. It applies to Amazon RDS Multi-AZ DB instance blue/green deployments. It is not the monitor for Aurora, RDS Multi-AZ DB Clusters, Group Replication, Galera, or PostgreSQL.

What ProxySQL Observes

ProxySQL checks whether mysql.rds_topology exists, reads its rows, and identifies the AWS-defined source and target roles. AWS defines these target statuses:

AVAILABLE
SWITCHOVER_INITIATED
SWITCHOVER_IN_PROGRESS
SWITCHOVER_IN_POST_PROCESSING
SWITCHOVER_COMPLETED

In the bounded release trace, both source and target rows were present through post-processing, only the target row remained at writer completion, and the table became empty about 44 seconds later. That timing is an observation, not an AWS guarantee.

ProxySQL policy treats SWITCHOVER_COMPLETED as writer completion. Because the metadata has no reader switchover status, ProxySQL treats the later disappearance or emptying of the topology as the reader-cleanup signal. A topology drain before writer completion is treated as cancellation or rollback. Other query failures are retried and are not interpreted as completion.

Prerequisites

• Configure the blue writer and reader hostgroups in mysql_replication_hostgroups and add an eligible blue writer to mysql_servers.
• Give the ProxySQL monitor account permission to query information_schema.TABLES and mysql.rds_topology on the RDS endpoints, and configure mysql-monitor_username and mysql-monitor_password.
• Ensure ProxySQL can resolve the blue and green RDS endpoint names. Source and target pairs must use the same port.
• For explicit mode, configure both green hostgroups and their eligible mysql_servers rows. Servers in OFFLINE_SOFT or OFFLINE_HARD do not participate.
• Assign each endpoint to only one active blue/green deployment. ProxySQL does not detect overlapping assignments.

See the canonical mysql_servers, mysql_replication_hostgroups, and mysql_aws_rds_bgd_hostgroups definitions before choosing hostgroup ids.

Explicit Configuration

The following Admin SQL configures blue writer/reader hostgroups 10/11 and green hostgroups 20/21. Replace the sample RDS endpoint names and monitor credentials before running it:

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup)
VALUES (10, 11);

INSERT INTO mysql_servers (hostgroup_id, hostname, port, use_ssl) VALUES
    (10, 'db-prod.abcdefgh.us-east-1.rds.amazonaws.com', 3306, 1),
    (11, 'db-prod-reader-1.abcdefgh.us-east-1.rds.amazonaws.com', 3306, 1),
    (20, 'db-prod-green-xyz.abcdefgh.us-east-1.rds.amazonaws.com', 3306, 1),
    (21, 'db-prod-reader-1-green-xyz.abcdefgh.us-east-1.rds.amazonaws.com', 3306, 1);

INSERT INTO mysql_aws_rds_bgd_hostgroups
    (writer_hostgroup, reader_hostgroup, green_writer_hostgroup, green_reader_hostgroup,
     active, writer_is_also_reader, check_interval_ms, check_timeout_ms, comment)
VALUES (10, 11, 20, 21, 1, 0, 1000, 800, 'production RDS blue/green deployment');

SET mysql-monitor_username = 'proxysql_monitor';
SET mysql-monitor_password = 'replace-with-monitor-password';
SET mysql-monitor_enabled = 'true';
SET mysql-monitor_aws_rds_topology_discovery_interval = 10;
SET mysql-aws_blue_green_deployment_auto_discovery = 'false';

LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;

Both green hostgroup columns are required in an operator-created row. Field ranges and defaults are defined in the canonical table reference; the LOAD and SAVE operations are covered by Admin Commands.

Automatic Discovery

Automatic mode starts with only the blue replication hostgroups and blue servers. When topology discovery sees a blue/green topology in AVAILABLE, ProxySQL creates a runtime row with auto_generated=1 and NULL green hostgroups. That row is deliberately not written to the persistent table.

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup)
VALUES (10, 11);

INSERT INTO mysql_servers (hostgroup_id, hostname, port, use_ssl) VALUES
    (10, 'db-prod.abcdefgh.us-east-1.rds.amazonaws.com', 3306, 1),
    (11, 'db-prod-reader-1.abcdefgh.us-east-1.rds.amazonaws.com', 3306, 1);

SET mysql-monitor_username = 'proxysql_monitor';
SET mysql-monitor_password = 'replace-with-monitor-password';
SET mysql-monitor_enabled = 'true';
SET mysql-monitor_aws_rds_topology_discovery_interval = 10;
SET mysql-aws_blue_green_deployment_auto_discovery = 'true';

LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;

The discovery interval is disabled at 0; use a positive value to scan eligible replication-hostgroup members. See mysql-monitor_aws_rds_topology_discovery_interval and mysql-aws_blue_green_deployment_auto_discovery.

Runtime State and Verification

Inspect runtime state and endpoint placement from the Admin interface:

SELECT writer_hostgroup, reader_hostgroup,
       green_writer_hostgroup, green_reader_hostgroup,
       auto_generated, status
FROM runtime_mysql_aws_rds_bgd_hostgroups
WHERE writer_hostgroup = 10;

SELECT hostgroup_id, hostname, port, status, use_ssl
FROM runtime_mysql_servers
WHERE hostgroup_id IN (10, 11, 20, 21)
ORDER BY hostgroup_id, hostname;

An explicit row reports auto_generated=0. An automatically discovered row reports auto_generated=1 and keeps both green hostgroups NULL. During a deployment, status exposes the monitor state derived from the AWS phase plus ProxySQL’s reader-cleanup phases.

Switchover Behavior

While the deployment is AVAILABLE, ProxySQL builds the endpoint map and pre-resolves green addresses. It polls at 250 ms in that phase and 100 ms during the active writer phases, regardless of the slower configured baseline. During switchover it suppresses ordinary read-only monitoring for endpoints owned by the deployment so the two monitors do not compete over placement.

At writer in-progress, ProxySQL demotes the mapped blue writer. During post-processing it pins stable blue names to resolved green addresses, retires matching connections, places the writer, and shuns blue readers that have no mapped green counterpart. If that would empty the reader hostgroup, it can temporarily make the writer available for reads. At writer completion it removes the writer pin and waits for topology drain before final reader cleanup.

Rollback and Cancellation

AWS permits cancellation during the initiated and in-progress stages; an observed cancellation returned the topology to AVAILABLE. ProxySQL also selects rollback when topology drains before writer completion, when it observes a recognized backward transition, or when an active worker is stopped or must restart its state after a late configuration change.

Rollback restores a writer that ProxySQL demoted, restores reader placement according to writer_is_also_reader, unshuns readers owned by the worker, removes its DNS entries, and purges its monitor connections. It does not remove green server rows or change their configured status.

DNS Pinning and Connection Retirement

ProxySQL resolves the green writer before the connection gap and probes its IP directly. During post-processing, it pins each mapped blue hostname to that green IP so new application connections keep using the stable blue name. The author observed that the temporary green hostname stopped resolving after completion; the direct IP avoids depending on that name during the transition.

Free connections for a retired endpoint are deleted immediately. In-use connections are marked unhealthy and non-reusable: they can finish their current ownership, but they are destroyed instead of returning to a pool. Writer DNS resumes normally after writer completion; reader pins remain until successful cleanup.

Limitations and Administrator Responsibilities

• The monitor does not persist in-progress state, DNS pins, or cleanup ownership. A process restart begins with fresh worker state and rebuilds runtime configuration from normal inputs.
• Automatic rows and automatically added green membership are runtime-only unless independently configured.
• Source and target pairs with different ports are unsupported. The topology row’s target port is not used.
• Offline servers are excluded from mapping, draining, and active green selection.
• Reader completion is ProxySQL policy inferred from topology drain, not an AWS guarantee.
• Cleanup is best effort and one shot; administrators must verify endpoint placement and connection health after a failed operation or process interruption.

Troubleshooting

• No runtime row: confirm an eligible blue writer is online, monitor credentials can run both topology queries, the discovery interval is positive in automatic mode, and the variables and servers were loaded.
• Status remains NONE: verify this is an RDS Multi-AZ DB instance blue/green deployment and query mysql.rds_topology directly with the monitor account.
• Green endpoint is not selected: check exact RDS hostname relationships, matching ports, DNS resolution, and that the explicit green row is not offline.
• Readers stay shunned or traffic reaches an old address: inspect runtime_mysql_servers, DNS, and logs. Reconcile placement manually if a one-shot cleanup was interrupted.
• Automatic configuration vanished after restart: this is expected for auto_generated=1; keep the blue configuration and discovery variables persistent, or replace it with an explicit row.