Disaster Recovery 101: Architecting Auto-Failovers for MySQL and C++ Deployments on DigitalOcean
Establishing a Highly Available MySQL Cluster on DigitalOcean
Achieving automated failover for a critical MySQL database requires a robust, multi-node architecture. We’ll focus on a primary-replica setup with automatic promotion of a replica to primary in case of failure. This involves leveraging MySQL’s built-in replication capabilities and an external orchestration layer for health checks and failover execution. For this example, we’ll assume a three-node setup: one primary, two replicas, all running on DigitalOcean Droplets.
MySQL Replication Configuration
First, ensure your MySQL instances are configured for replication. This involves setting a unique `server-id` for each node and enabling binary logging. On each MySQL server (primary and replicas), edit your `my.cnf` (or `my.ini`) file. The exact location varies by OS and installation method, but common paths include `/etc/mysql/my.cnf` or `/etc/my.cnf`.
Primary Node Configuration (`my.cnf`)
On the designated primary node, add or modify the following:
[mysqld] server-id = 1 log_bin = /var/log/mysql/mysql-bin.log binlog_format = ROW gtid_mode = ON enforce_gtid_consistency = ON relay_log = /var/log/mysql/mysql-relay-bin.log read_only = OFF
Replica Node Configuration (`my.cnf`)
On each replica node, configure similarly, ensuring a unique `server-id` and pointing to the primary’s IP address. Note that `read_only` should be `ON` for replicas to prevent accidental writes.
[mysqld] server-id = 2 # Or 3 for the second replica log_bin = /var/log/mysql/mysql-bin.log binlog_format = ROW gtid_mode = ON enforce_gtid_consistency = ON relay_log = /var/log/mysql/mysql-relay-bin.log read_only = ON # For replica 1: # master_host = <primary_droplet_ip> # master_user = replication_user # master_password = your_replication_password # master_port = 3306 # master_auto_position = 1 # Use this with GTID # For replica 2 (if applicable): # master_host = <primary_droplet_ip> # master_user = replication_user # master_password = your_replication_password # master_port = 3306 # master_auto_position = 1
After modifying `my.cnf`, restart the MySQL service on all nodes: sudo systemctl restart mysql.
Setting Up Replication User and Initial Sync
On the primary node, create a dedicated user for replication. Replace your_replication_password with a strong, unique password.
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'your_replication_password'; GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%'; FLUSH PRIVILEGES;
For the initial data synchronization, it’s best to take a consistent snapshot of the primary and restore it on the replicas. A common method is using mysqldump or Percona XtraBackup. For GTID-based replication, you can often skip the `MASTER_LOG_FILE` and `MASTER_LOG_POS` arguments in the `CHANGE MASTER TO` command if `master_auto_position = 1` is set in the replica’s `my.cnf` and GTID is enabled.
On each replica, execute the following command, replacing <primary_droplet_ip> and your_replication_password:
CHANGE MASTER TO MASTER_HOST='<primary_droplet_ip>', MASTER_USER='replication_user', MASTER_PASSWORD='your_replication_password', MASTER_PORT=3306, MASTER_AUTO_POSITION=1;
Then, start the replication process:
START SLAVE;
Verify replication status on each replica with SHOW SLAVE STATUS\G. Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes, and ensure Seconds_Behind_Master is low and stable.
Orchestrating Failover with Orchestrator
Manual failover is error-prone and slow. We’ll use Orchestrator, a popular open-source tool for MySQL high availability and replication management. Orchestrator can detect failures, promote replicas, and reconfigure other replicas to follow the new primary.
Installing Orchestrator
Orchestrator can be installed on a separate Droplet or one of the MySQL nodes (though a separate node is recommended for true HA). Follow the official Orchestrator installation guide for your OS. For Ubuntu/Debian:
wget https://github.com/openark/orchestrator/releases/download/v3.2.7/orchestrator_3.2.7_linux_amd64.deb sudo dpkg -i orchestrator_3.2.7_linux_amd64.deb
Configuring Orchestrator
The main configuration file is typically at /etc/orchestrator/orchestrator.conf.json. Key settings include:
{
"Debug": false,
"ListenAddress": ":3000",
"MySQLTopologyUser": "orchestrator_user",
"MySQLTopologyPassword": "orchestrator_password",
"MySQLOrchestratorHostPort": "<orchestrator_droplet_ip>:3306",
"MySQLOrchestratorDatabaseName": "orchestrator",
"MySQLOrchestratorDatabaseUser": "orchestrator_db_user",
"MySQLOrchestratorDatabasePassword": "orchestrator_db_password",
"DiscoveryPeriodSeconds": 10,
"FailureDetectionPeriodSeconds": 30,
"PromotionUser": "root",
"PromotionPassword": "your_mysql_root_password",
"PromotionPort": 3306,
"PostUnsuccessfulFailoverProcesses": [
"/path/to/your/slack_notification_script.sh"
],
"PostSuccessfulFailoverProcesses": [
"/path/to/your/slack_notification_script.sh"
],
"ReplicaLagQuery": "SELECT * FROM mysql.slave_master_info",
"SlaveLagQuery": "SELECT * FROM mysql.slave_master_info",
"DetectClusterAlias": true,
"ClusterDetectionQuery": "SELECT @@global.server_id, @@global.gtid_domain_id",
"AutoDiscoverAtStartup": true,
"AutoOrchestrateOnStartup": true,
"RecoveryPeriodBlockSeconds": 300,
"SnapshotPeriodSeconds": 3600,
"SnapshotAgents": [
"orchestrator-agent"
]
}
You’ll need to create the orchestrator_user on your MySQL primary and grant it necessary privileges (e.g., `REPLICATION SLAVE`, `REPLICATION CLIENT`, `PROCESS`, `SELECT`, `SHOW DATABASES`). Also, create the `orchestrator` database and the `orchestrator_db_user` for Orchestrator’s internal state.
Starting and Discovering Topology
Start the Orchestrator service:
sudo systemctl start orchestrator sudo systemctl enable orchestrator
Access the Orchestrator web UI (usually at http://<orchestrator_droplet_ip>:3000). Orchestrator should automatically discover your MySQL topology. If not, you can manually add instances via the UI or by configuring `DiscoverByShowSlaveHosts` in the config.
Integrating C++ Application with MySQL Failover
Your C++ application needs to be resilient to database failovers. This typically involves:
- Connection Pooling: Maintain a pool of connections.
- Retry Logic: Implement exponential backoff for connection attempts and queries.
- Dynamic Host Discovery: The application must be able to find the *current* primary MySQL instance.
Dynamic Host Discovery Strategy
The most robust way to handle dynamic host discovery is to have a stable endpoint that always resolves to the current primary. This can be achieved using:
- DNS: A CNAME record pointing to the primary’s Droplet hostname, updated by Orchestrator or a script.
- Load Balancer: A DigitalOcean Load Balancer configured to point to the current primary.
- Service Discovery Tool: Tools like Consul or etcd.
For simplicity and integration with Orchestrator, we’ll consider a DNS-based approach. Orchestrator can be configured to run hooks (scripts) on failover events. These hooks can update DNS records via the DigitalOcean API.
Example Failover Hook Script (Bash)
Create a script (e.g., /opt/orchestrator/hooks/update_dns.sh) that Orchestrator will call. This script needs the DigitalOcean API token and the domain/record details.
#!/bin/bash
# Orchestrator passes arguments:
# $1: Cluster Name
# $2: New Primary Hostname
# $3: New Primary IP Address
# $4: Old Primary Hostname
# $5: Old Primary IP Address
# $6: Event Type (e.g., "failover", "recovery")
CLUSTER_NAME="$1"
NEW_PRIMARY_IP="$3"
EVENT_TYPE="$6"
# --- Configuration ---
DO_API_TOKEN="YOUR_DIGITALOCEAN_API_TOKEN"
DNS_ZONE_NAME="yourdomain.com" # The domain for your DNS zone
RECORD_NAME="mysql-primary" # The subdomain (e.g., mysql-primary.yourdomain.com)
RECORD_TYPE="A"
# ---------------------
if [ "$EVENT_TYPE" != "failover" ] && [ "$EVENT_TYPE" != "recovery" ]; then
echo "Skipping DNS update for event type: $EVENT_TYPE"
exit 0
fi
echo "Orchestrator hook: $EVENT_TYPE detected for cluster $CLUSTER_NAME."
echo "New primary IP: $NEW_PRIMARY_IP"
# Get the current DNS record ID (if it exists)
RECORD_ID=$(curl -s -X GET "https://api.digitalocean.com/v2/domains/$DNS_ZONE_NAME/records?type=$RECORD_TYPE&name=$RECORD_NAME.$DNS_ZONE_NAME" \
-H "Authorization: Bearer $DO_API_TOKEN" | jq -r '.domain_records[0].id')
if [ -z "$RECORD_ID" ] || [ "$RECORD_ID" == "null" ]; then
echo "DNS record '$RECORD_NAME.$DNS_ZONE_NAME' not found. Creating new record."
# Create new record
curl -s -X POST "https://api.digitalocean.com/v2/domains/$DNS_ZONE_NAME/records" \
-H "Authorization: Bearer $DO_API_TOKEN" \
-H "Content-Type: application/json" \
-d '{
"type": "'"$RECORD_TYPE"'",
"name": "'"$RECORD_NAME"'",
"data": "'"$NEW_PRIMARY_IP"'",
"ttl": 300
}'
if [ $? -eq 0 ]; then
echo "Successfully created DNS record for $RECORD_NAME.$DNS_ZONE_NAME pointing to $NEW_PRIMARY_IP."
else
echo "Error creating DNS record."
exit 1
fi
else
echo "Found existing DNS record ID: $RECORD_ID. Updating record."
# Update existing record
curl -s -X PUT "https://api.digitalocean.com/v2/domains/$DNS_ZONE_NAME/records/$RECORD_ID" \
-H "Authorization: Bearer $DO_API_TOKEN" \
-H "Content-Type: application/json" \
-d '{
"data": "'"$NEW_PRIMARY_IP"'",
"ttl": 300
}'
if [ $? -eq 0 ]; then
echo "Successfully updated DNS record for $RECORD_NAME.$DNS_ZONE_NAME pointing to $NEW_PRIMARY_IP."
else
echo "Error updating DNS record."
exit 1
fi
fi
exit 0
Make the script executable: chmod +x /opt/orchestrator/hooks/update_dns.sh.
Configure Orchestrator to use this hook by adding it to the PostSuccessfulFailoverProcesses and PostSuccessfulRecoveryProcesses (if applicable) in orchestrator.conf.json. Ensure the jq utility is installed on the Orchestrator node for parsing JSON responses from the DigitalOcean API.
C++ Application Code Snippet (Conceptual)
Your C++ application should connect to the DNS name (e.g., mysql-primary.yourdomain.com). Use a robust MySQL connector library that supports connection pooling and retry mechanisms. Here’s a conceptual example using a hypothetical `MySQLConnector` class:
#include <iostream>
#include <string>
#include <chrono>
#include <thread>
#include <mysql_connector.h> // Hypothetical MySQL Connector Header
// Assume MySQLConnector class handles connection pooling, retries, and dynamic host resolution
class MySQLConnector {
public:
MySQLConnector(const std::string& host, const std::string& user, const std::string& password, const std::string& db)
: db_host_(host), db_user_(user), db_password_(password), db_name_(db), connection_(nullptr) {}
bool connect() {
// Attempt connection, potentially resolving DNS and retrying
// This is where the logic to connect to "mysql-primary.yourdomain.com" would live
// and handle transient errors with backoff.
try {
connection_ = new mysql_connection(db_host_, db_user_, db_password_, db_name_);
if (connection_ && connection_->isConnected()) {
std::cout << "Successfully connected to MySQL at " << db_host_ << std::endl;
return true;
}
} catch (const std::exception& e) {
std::cerr << "Connection failed: " << e.what() << std::endl;
// Implement retry logic with exponential backoff here
}
return false;
}
void executeQuery(const std::string& query) {
if (!connection_ || !connection_->isConnected()) {
// Attempt to reconnect if connection is lost
if (!connect()) {
throw std::runtime_error("Database connection lost and cannot reconnect.");
}
}
try {
connection_->execute(query);
std::cout << "Query executed successfully." << std::endl;
} catch (const mysql_exception& e) {
// Handle specific MySQL errors, e.g., network errors, deadlocks
if (e.isNetworkError() || e.isDeadlock()) {
std::cerr << "Query failed due to network/deadlock: " << e.what() << std::endl;
// Implement retry logic for the query
// Potentially trigger a reconnect attempt
delete connection_;
connection_ = nullptr;
throw; // Re-throw to be caught by higher level retry logic
} else {
std::cerr << "Query failed: " << e.what() << std::endl;
throw;
}
}
}
~MySQLConnector() {
if (connection_) {
delete connection_;
}
}
private:
std::string db_host_;
std::string db_user_;
std::string db_password_;
std::string db_name_;
mysql_connection* connection_; // Pointer to the actual connection object
};
int main() {
// Use the DNS name that resolves to the current primary
MySQLConnector db("mysql-primary.yourdomain.com", "app_user", "app_password", "app_database");
int max_retries = 5;
int retry_delay_ms = 1000; // Start with 1 second
for (int i = 0; i <= max_retries; ++i) {
try {
if (db.connect()) {
db.executeQuery("INSERT INTO logs (message) VALUES ('Application started successfully.');");
// Perform other database operations
break; // Success, exit retry loop
} else {
// Connection failed, wait before retrying
std::this_thread::sleep_for(std::chrono::milliseconds(retry_delay_ms));
retry_delay_ms *= 2; // Exponential backoff
}
} catch (const std::exception& e) {
std::cerr << "Attempt " << (i + 1) << " failed: " << e.what() << std::endl;
std::this_thread::sleep_for(std::chrono::milliseconds(retry_delay_ms));
retry_delay_ms *= 2; // Exponential backoff
}
}
if (i > max_retries) {
std::cerr << "Failed to connect to database after multiple retries." << std::endl;
return 1;
}
return 0;
}
This C++ snippet illustrates the core concepts: connecting to a dynamic hostname, handling connection failures, and implementing retry logic with exponential backoff. A production-ready solution would involve a more sophisticated connection management library and potentially health checks integrated into the application’s lifecycle.
Monitoring and Alerting
Automated failover is only part of the solution. Comprehensive monitoring and alerting are crucial to ensure the system is healthy and to be notified when issues occur, even if failover is successful. Key metrics to monitor include:
- MySQL replication lag (
Seconds_Behind_Master). - Orchestrator’s health and discovery status.
- Droplet resource utilization (CPU, RAM, Disk I/O).
- Network connectivity between nodes.
- Application error rates related to database connectivity.
Tools like Prometheus with MySQL exporter, Grafana for visualization, and Alertmanager for notifications are standard for this. Configure alerts for high replication lag, Orchestrator failures, or any detected primary unavailability.