Cross-Datacenter Replication Strategies

Cross-datacenter replication ensures your data is available across multiple geographic locations, providing both disaster recovery and improved availability. This comprehensive guide covers database replication, storage synchronization, and failover strategies for distributed infrastructure.

Table of Contents

1. Replication Architecture Overview
2. MySQL Replication
3. PostgreSQL Replication
4. Storage Synchronization
5. Network Considerations
6. Monitoring Replication
7. Failover Strategies
8. Troubleshooting
9. Conclusion

Replication Architecture Overview

Cross-datacenter replication architecture typically includes:

• Primary datacenter: Hosts the main database and application servers
• Secondary datacenter: Receives replicated data with minimal lag
• Network: Dedicated replication link with sufficient bandwidth
• Monitoring: Real-time synchronization status and alerts

# Network topology validation
validate_replication_network() {
    local primary_ip="10.0.1.10"
    local secondary_ip="10.0.2.10"
    local replication_port=3306
    
    echo "Validating replication network"
    
    # Test connectivity
    if nc -zv "$primary_ip" "$replication_port" 2>/dev/null; then
        echo "✓ Primary datacenter reachable"
    else
        echo "✗ Cannot reach primary datacenter"
        return 1
    fi
    
    # Measure latency
    latency=$(ping -c 5 "$secondary_ip" 2>/dev/null | tail -1 | awk -F'/' '{print $5}')
    echo "✓ Network latency: ${latency}ms"
    
    # Recommended: < 50ms for synchronous replication
    if (( $(echo "$latency < 50" | bc -l) )); then
        echo "✓ Latency acceptable for synchronous replication"
    else
        echo "⚠ High latency - consider asynchronous replication"
    fi
}

validate_replication_network

MySQL Replication

MySQL replication uses binary logs (binlog) to replicate data changes from primary to replica servers.

Primary Server Configuration

# Configure MySQL primary server
cat > /etc/mysql/mysql.conf.d/replication.cnf << 'EOF'
[mysqld]
# Enable binary logging
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_row_image = FULL

# Server ID (must be unique across replication topology)
server_id = 1

# Replication user
create_replication_user = 1

# Binary log retention (7 days)
expire_logs_days = 7

# Optional: Semi-synchronous replication (stronger consistency)
plugin_load_add = semisync_master.so
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000

# Replication settings
binlog_cache_size = 32768
max_binlog_size = 1073741824
EOF

# Restart MySQL
systemctl restart mysql

# Verify replication is enabled
mysql -u root -e "SHOW VARIABLES LIKE 'log_bin%';"
mysql -u root -e "SHOW VARIABLES LIKE 'server_id';"

Create Replication User

# Create dedicated replication user with proper permissions
mysql -u root << 'EOF'
CREATE USER 'repl_user'@'10.0.2.%' IDENTIFIED BY 'secure_replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.2.%';
FLUSH PRIVILEGES;

-- Show grants to verify
SHOW GRANTS FOR 'repl_user'@'10.0.2.%';
EOF

Create Base Backup

# Create consistent backup for replica initialization
mysql -u root << 'EOF'
-- Create backup directory user if needed
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup_password';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
EOF

# Take backup with mysqlbackup (or mysqldump)
mysqldump \
    -u backup \
    -p \
    --all-databases \
    --single-transaction \
    --master-data=2 \
    --quick \
    > /tmp/mysql-replication-base.sql

# Get binary log position from backup
grep -i "CHANGE MASTER" /tmp/mysql-replication-base.sql

# Compress and transfer to secondary datacenter
gzip -c /tmp/mysql-replication-base.sql | \
    ssh -C [email protected] 'zcat > /tmp/mysql-replication-base.sql'

Secondary Server Configuration

# Configure MySQL secondary (replica) server
cat > /etc/mysql/mysql.conf.d/replication.cnf << 'EOF'
[mysqld]
# Enable binary logging on replica (for chained replication)
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

# Unique server ID
server_id = 2

# Read-only mode (optional but recommended for replicas)
read_only = 1
super_read_only = 1

# Parallel replication (increase throughput)
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1

# Semi-synchronous replication
plugin_load_add = semisync_slave.so
rpl_semi_sync_slave_enabled = 1

# Relay log
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
EOF

systemctl restart mysql

Initialize Replica

# On secondary server: import base backup
mysql -u root < /tmp/mysql-replication-base.sql

# Configure replication connection
mysql -u root << 'EOF'
CHANGE MASTER TO
    MASTER_HOST='10.0.1.10',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='secure_replication_password',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=12345;

-- Start replication
START SLAVE;

-- Check replication status
SHOW SLAVE STATUS\G
EOF

# Monitor replication status
watch_replica_status() {
    while true; do
        mysql -u root -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_State|Seconds_Behind_Master|Last_Error"
        sleep 5
    done
}

MySQL Replication Monitoring

# Check replication lag
check_replication_lag() {
    mysql -u root << 'EOF'
SHOW SLAVE STATUS\G | grep -E "Seconds_Behind_Master|Master_Server_Id|Slave_IO_Running|Slave_SQL_Running"
EOF
}

# Alert on replication lag
monitor_mysql_lag() {
    local lag_threshold=10  # seconds
    
    lag=$(mysql -u root -sNe "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master:" | awk '{print $NF}')
    
    if [ -z "$lag" ] || [ "$lag" = "NULL" ]; then
        echo "ERROR: Replication not running"
        return 1
    fi
    
    if [ "$lag" -gt "$lag_threshold" ]; then
        echo "WARNING: Replication lag is ${lag}s (threshold: ${lag_threshold}s)"
    fi
}

# Create monitoring alert
cat > /usr/local/bin/mysql-replication-monitor.sh << 'EOF'
#!/bin/bash

ALERT_EMAIL="[email protected]"
LAG_THRESHOLD=60
ERROR_CHECK_SQL="SHOW SLAVE STATUS\G | grep Last_Error"

check_replica_health() {
    local status=$(mysql -u root -sNe "SHOW SLAVE STATUS\G")
    
    # Check if replication is running
    if ! echo "$status" | grep -q "Slave_IO_Running: Yes"; then
        echo "CRITICAL: Replication IO thread is stopped" | mail -s "MySQL Replication Alert" "$ALERT_EMAIL"
    fi
    
    if ! echo "$status" | grep -q "Slave_SQL_Running: Yes"; then
        echo "CRITICAL: Replication SQL thread is stopped" | mail -s "MySQL Replication Alert" "$ALERT_EMAIL"
    fi
    
    # Check lag
    local lag=$(echo "$status" | grep "Seconds_Behind_Master:" | awk '{print $NF}')
    if [ "$lag" -gt "$LAG_THRESHOLD" ]; then
        echo "WARNING: Replication lag is ${lag}s" | mail -s "MySQL Replication Alert" "$ALERT_EMAIL"
    fi
}

check_replica_health
EOF

chmod +x /usr/local/bin/mysql-replication-monitor.sh

PostgreSQL Replication

PostgreSQL uses WAL (Write-Ahead Logging) archiving and streaming replication for cross-datacenter redundancy.

Primary Server Configuration

# Configure PostgreSQL primary for replication
cat > /etc/postgresql/14/main/postgresql.conf << 'EOF'
# Replication settings
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
hot_standby = on
hot_standby_feedback = on

# WAL archiving
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300

# Connection settings
listen_addresses = '*'
EOF

# Create WAL archive directory
mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

# Configure replication access
cat >> /etc/postgresql/14/main/pg_hba.conf << 'EOF'
# Allow replication connections from secondary datacenter
host    replication     repl_user       10.0.2.0/24        md5
EOF

systemctl restart postgresql

Create Replication User

# Create replication user
sudo -u postgres psql << 'EOF'
CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'secure_password';

-- Verify role was created
\du repl_user
EOF

Create Base Backup

# Use pg_basebackup for replica initialization
pg_basebackup \
    -h 10.0.1.10 \
    -U repl_user \
    -D /var/lib/postgresql/14/replication-backup \
    -Xstream \
    -v

Secondary Server Configuration

# Restore base backup to secondary
sudo -u postgres pg_basebackup \
    -h 10.0.1.10 \
    -U repl_user \
    -D /var/lib/postgresql/14/main \
    -Xstream \
    -R \
    -v

# Create recovery.conf (or postgresql.auto.conf)
cat > /var/lib/postgresql/14/main/recovery.conf << 'EOF'
# Streaming replication
primary_conninfo = 'host=10.0.1.10 port=5432 user=repl_user password=secure_password'
primary_slot_name = 'standby1'

# Recovery settings
recovery_target_timeline = 'latest'
EOF

# Adjust permissions
chown postgres:postgres /var/lib/postgresql/14/main/recovery.conf
chmod 600 /var/lib/postgresql/14/main/recovery.conf

# Start PostgreSQL
systemctl start postgresql

PostgreSQL Replication Monitoring

# Check replication status
check_postgresql_replication() {
    sudo -u postgres psql << 'EOF'
-- Check standby connections
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    sync_state,
    flush_lsn,
    replay_lsn
FROM pg_stat_replication;

-- Check replication slots
SELECT 
    slot_name,
    slot_type,
    active,
    restart_lsn,
    confirmed_flush_lsn
FROM pg_replication_slots;

-- Check WAL level
SHOW wal_level;
EOF
}

# Monitor replication lag
monitor_postgresql_lag() {
    sudo -u postgres psql -t << 'EOF'
SELECT 
    EXTRACT(EPOCH FROM (NOW() - pg_last_xact_replay_timestamp()))::INT as replication_lag_seconds;
EOF
}

# Continuous monitoring script
cat > /usr/local/bin/pg-replication-monitor.sh << 'EOF'
#!/bin/bash

ALERT_EMAIL="[email protected]"
LAG_THRESHOLD=60

monitor_lag() {
    local lag=$(sudo -u postgres psql -t -c \
        "SELECT EXTRACT(EPOCH FROM (NOW() - pg_last_xact_replay_timestamp()))::INT;")
    
    if [ -z "$lag" ] || [ "$lag" = "NULL" ]; then
        echo "CRITICAL: Cannot determine replication lag" | \
            mail -s "PostgreSQL Replication Alert" "$ALERT_EMAIL"
        return 1
    fi
    
    if [ "$lag" -gt "$LAG_THRESHOLD" ]; then
        echo "WARNING: PostgreSQL replication lag is ${lag}s" | \
            mail -s "PostgreSQL Replication Alert" "$ALERT_EMAIL"
    fi
}

monitor_lag
EOF

chmod +x /usr/local/bin/pg-replication-monitor.sh

Storage Synchronization

For non-database storage, use rsync and DRBD for efficient cross-datacenter synchronization.

Rsync-Based Replication

# Rsync configuration for continuous sync
cat > /usr/local/bin/sync-storage.sh << 'EOF'
#!/bin/bash

SOURCE_DIR="/data/storage"
REMOTE_HOST="secondary-dc.com"
REMOTE_USER="backup"
REMOTE_DIR="/data/storage"
LOG_FILE="/var/log/storage-sync.log"
LOCK_FILE="/var/run/storage-sync.lock"

# Prevent concurrent syncs
if [ -f "$LOCK_FILE" ]; then
    echo "Sync already running" >> "$LOG_FILE"
    exit 1
fi
touch "$LOCK_FILE"
trap "rm -f $LOCK_FILE" EXIT

echo "[$(date)] Starting storage sync" >> "$LOG_FILE"

# Perform rsync with compression and bandwidth limit
rsync -avz \
    --bwlimit=50000 \
    --delete \
    --exclude='.tmp/*' \
    --exclude='*.log' \
    "$SOURCE_DIR/" \
    "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR" \
    >> "$LOG_FILE" 2>&1

if [ $? -eq 0 ]; then
    echo "[$(date)] Sync completed successfully" >> "$LOG_FILE"
else
    echo "[$(date)] Sync failed with status $?" >> "$LOG_FILE"
fi
EOF

chmod +x /usr/local/bin/sync-storage.sh

# Schedule continuous replication
(crontab -l 2>/dev/null; echo "*/15 * * * * /usr/local/bin/sync-storage.sh") | crontab -

DRBD Configuration (Block-Level Replication)

# DRBD configuration for synchronous replication
cat > /etc/drbd.d/storage.res << 'EOF'
resource storage {
    protocol C;  # Synchronous replication (waits for acknowledgment)
    
    on primary-server {
        device /dev/drbd0;
        disk /dev/sda1;
        address 10.0.1.20:7788;
        meta-disk internal;
    }
    
    on secondary-server {
        device /dev/drbd0;
        disk /dev/sda1;
        address 10.0.2.20:7788;
        meta-disk internal;
    }
    
    net {
        # Performance tuning
        max-buffers 8192;
        max-epoch-size 20000;
        sndbuf-size 512K;
        rcvbuf-size 512K;
        
        # Fencing
        fencing resource-only;
        
        # Data integrity
        verify-alg sha256;
    }
}
EOF

# Initialize DRBD
drbdadm create-md storage
systemctl start drbd

# Promote primary
drbdadm primary storage --force

# Create filesystem on DRBD device
mkfs.ext4 /dev/drbd0

# Mount the replicated filesystem
mkdir -p /data/replicated
mount /dev/drbd0 /data/replicated

# Verify replication status
cat /proc/drbd

Network Considerations

Bandwidth Planning

# Calculate required bandwidth for replication
cat > /usr/local/bin/bandwidth-calculator.sh << 'EOF'
#!/bin/bash

# Input parameters
database_size_gb=$1        # Total database size in GB
daily_change_percent=$2    # Percentage of data changing daily (0-100)
replication_window_hours=$3  # Available replication window in hours

if [ -z "$database_size_gb" ] || [ -z "$daily_change_percent" ] || [ -z "$replication_window_hours" ]; then
    echo "Usage: $0 <size_gb> <change_percent> <window_hours>"
    echo "Example: $0 500 5 4"
    exit 1
fi

# Calculate required throughput
daily_change_bytes=$((database_size_gb * $daily_change_percent * 10737418))  # GB to bytes
throughput_bytes=$((daily_change_bytes / ($replication_window_hours * 3600)))
throughput_mbps=$((throughput_bytes * 8 / 1000000))

echo "Replication Bandwidth Calculator"
echo "=================================="
echo "Database Size: ${database_size_gb}GB"
echo "Daily Change: ${daily_change_percent}%"
echo "Replication Window: ${replication_window_hours} hours"
echo ""
echo "Required Throughput: ${throughput_mbps} Mbps"
echo "Recommended Link Speed: $((throughput_mbps * 2)) Mbps (with redundancy)"
EOF

chmod +x /usr/local/bin/bandwidth-calculator.sh
bash /usr/local/bin/bandwidth-calculator.sh 500 5 4

Network Optimization

# Optimize network for replication
configure_network_optimization() {
    # Increase buffer sizes for TCP window scaling
    sysctl -w net.core.rmem_max=134217728
    sysctl -w net.core.wmem_max=134217728
    sysctl -w net.ipv4.tcp_rmem="4096 87380 67108864"
    sysctl -w net.ipv4.tcp_wmem="4096 65536 67108864"
    
    # Enable TCP window scaling
    sysctl -w net.ipv4.tcp_window_scaling=1
    
    # Optimize for replication (disable Nagle's algorithm)
    sysctl -w net.ipv4.tcp_nodelay=1
    
    # Increase backlog
    sysctl -w net.core.netdev_max_backlog=5000
    
    # Persist changes
    cat >> /etc/sysctl.conf << 'EOF'
net.core.rmem_max=134217728
net.core.wmem_max=134217728
net.ipv4.tcp_rmem=4096 87380 67108864
net.ipv4.tcp_wmem=4096 65536 67108864
net.ipv4.tcp_window_scaling=1
net.ipv4.tcp_nodelay=1
net.core.netdev_max_backlog=5000
EOF
}

configure_network_optimization

Monitoring Replication

# Comprehensive replication monitoring
cat > /usr/local/bin/replication-health.sh << 'EOF'
#!/bin/bash

ALERT_EMAIL="[email protected]"
METRICS_FILE="/var/metrics/replication-health.txt"

check_replication_health() {
    echo "=== Replication Health Report ===" > "$METRICS_FILE"
    echo "Timestamp: $(date)" >> "$METRICS_FILE"
    echo "" >> "$METRICS_FILE"
    
    # Check MySQL replication
    echo "MySQL Replication Status:" >> "$METRICS_FILE"
    mysql -u root -sNe "SHOW SLAVE STATUS\G" >> "$METRICS_FILE" 2>&1
    echo "" >> "$METRICS_FILE"
    
    # Check PostgreSQL replication
    echo "PostgreSQL Replication Status:" >> "$METRICS_FILE"
    sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;" >> "$METRICS_FILE" 2>&1
    echo "" >> "$METRICS_FILE"
    
    # Check DRBD status
    if command -v drbdadm &> /dev/null; then
        echo "DRBD Status:" >> "$METRICS_FILE"
        cat /proc/drbd >> "$METRICS_FILE"
        echo "" >> "$METRICS_FILE"
    fi
    
    # Check network connectivity
    echo "Network Connectivity:" >> "$METRICS_FILE"
    ping -c 3 secondary-dc.com >> "$METRICS_FILE" 2>&1
    echo "" >> "$METRICS_FILE"
}

check_replication_health
cat "$METRICS_FILE"
EOF

chmod +x /usr/local/bin/replication-health.sh

Failover Strategies

Automatic Failover with Keepalived

# Configure automatic failover with Keepalived
cat > /etc/keepalived/keepalived.conf << 'EOF'
global_defs {
    router_id DATABASE_HA
    script_user root
    enable_script_security
}

vrrp_script check_database {
    script "/usr/local/bin/check-db-health.sh"
    interval 5
    weight -20
    fall 3
    rise 2
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    
    authentication {
        auth_type PASS
        auth_pass database_failover
    }
    
    virtual_ipaddress {
        10.0.1.50/24
    }
    
    track_script {
        check_database
    }
    
    notify_master "/usr/local/bin/notify-master.sh"
    notify_backup "/usr/local/bin/notify-backup.sh"
    notify_fault "/usr/local/bin/notify-fault.sh"
}
EOF

# Health check script
cat > /usr/local/bin/check-db-health.sh << 'EOF'
#!/bin/bash

# Check if database is responding
mysql -u health_check -p'password' -e "SELECT 1" > /dev/null 2>&1
if [ $? -eq 0 ]; then
    exit 0  # Health check passed
else
    exit 1  # Health check failed
fi
EOF

chmod +x /usr/local/bin/check-db-health.sh
systemctl restart keepalived

Manual Failover Procedure

# Manual failover procedure
failover_to_secondary() {
    echo "Starting failover to secondary datacenter"
    
    # 1. Stop replication on secondary
    echo "Step 1: Stopping replication on secondary"
    mysql -h secondary-dc.com -u root << 'EOF'
STOP SLAVE;
SHOW SLAVE STATUS\G;
EOF
    
    # 2. Promote secondary to primary
    echo "Step 2: Promoting secondary to primary"
    mysql -h secondary-dc.com -u root << 'EOF'
RESET SLAVE ALL;
SHOW MASTER STATUS\G;
EOF
    
    # 3. Update application connection strings
    echo "Step 3: Updating application configuration"
    # Update /etc/myapp/database.conf
    # Restart application services
    
    # 4. Point DNS to new primary
    echo "Step 4: Updating DNS records"
    # Update DNS to point database.example.com to secondary-dc.com
    
    # 5. Verify connectivity
    echo "Step 5: Verifying connectivity"
    mysql -h secondary-dc.com -u app_user -p << 'EOF'
SELECT @@hostname, @@server_id, NOW();
EOF
    
    echo "Failover completed"
}

Troubleshooting

# Common replication issues and solutions

# Issue 1: Replication lag
diagnose_replication_lag() {
    echo "Diagnosing replication lag..."
    
    # Check binary log position on primary
    echo "Primary Binary Log Status:"
    mysql -h primary-dc.com -u root -e "SHOW MASTER STATUS\G"
    
    # Check relay log position on secondary
    echo "Secondary Relay Log Status:"
    mysql -h secondary-dc.com -u root -e "SHOW SLAVE STATUS\G"
    
    # Check for long-running queries
    echo "Long-running queries on primary:"
    mysql -h primary-dc.com -u root -e "SHOW PROCESSLIST WHERE Time > 300\G"
}

# Issue 2: Replication error
fix_replication_error() {
    local secondary_host=$1
    
    echo "Attempting to recover from replication error..."
    
    # Get error details
    mysql -h "$secondary_host" -u root << 'EOF'
SHOW SLAVE STATUS\G | grep -i error;
EOF
    
    # Skip the error (use with caution!)
    # mysql -h "$secondary_host" -u root -e "SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;"
    
    echo "Consult error details before skipping"
}

# Issue 3: Binary log not found
recover_missing_binlog() {
    local primary_host=$1
    
    # Get available binlogs
    mysql -h "$primary_host" -u root -e "SHOW BINARY LOGS;"
    
    # If log is missing, may need to resync from scratch
    echo "If required binlog is missing, perform full resync:"
    echo "1. Take new backup on primary"
    echo "2. Restore on secondary"
    echo "3. Reinitialize replication"
}

Conclusion

Effective cross-datacenter replication requires:

1. Database Replication: Use native replication (MySQL binlog, PostgreSQL WAL)
2. Storage Sync: Complement with rsync or DRBD for non-database data
3. Network Optimization: Ensure sufficient bandwidth and low latency
4. Monitoring: Continuous health checks and lag monitoring
5. Testing: Regular failover drills to validate procedures
6. Documentation: Keep runbooks updated for your specific setup

Start with asynchronous replication for simplicity, then upgrade to synchronous replication for critical systems where data loss is unacceptable. Remember that replication is not a backup—maintain separate backups alongside replication.