How do I set up MySQL replication for high availability on dedicated servers?

MySQL replication creates a real-time synchronized copy of your primary database on one or more replica servers. Reads distribute across replicas (reducing primary load), and if the primary fails, a replica promotes to primary - achieving both horizontal read scaling and high availability.

DETAILED EXPLANATION:
Replication types:
- Statement-based: Replicates SQL statements (compact but non-deterministic queries can cause issues)
- Row-based: Replicates actual row changes (accurate, more bandwidth) - RECOMMENDED
- Mixed: Row-based with automatic fallback

Common topology: Primary (writes) + Replica (reads)
WordPress benefit: HyperDB plugin sends SELECT to replica, INSERT/UPDATE to primary
Result: Primary handles 20% of traffic (writes only), replica handles 80% (reads)

Replication lag: Asynchronous by default - replica typically 1-5 seconds behind
For zero-lag: Use MySQL Group Replication (synchronous) or Galera Cluster

WHEN TO USE:
- Any production database with uptime requirements
- WordPress/WooCommerce with high read:write ratio
- Before major maintenance (fail over to replica, maintain primary safely)
- Connect Quest dedicated server pairs (two servers, one primary + one replica)

STEP-BY-STEP - Primary-Replica setup:

=== PRIMARY SERVER (10.0.1.10) ===
Add to /etc/mysql/mysql.conf.d/replication.cnf:
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
max_binlog_size = 100M
expire_logs_days = 7

systemctl restart mysql

In MySQL on primary:
CREATE USER 'replicator'@'10.0.1.11' IDENTIFIED BY 'StrongPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.0.1.11';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
-- Note: File = mysql-bin.000001, Position = 1234

Export initial snapshot:
mysqldump --all-databases --master-data=2 --single-transaction -u root -p > /tmp/full.sql
scp /tmp/full.sql [email protected]:/tmp/

=== REPLICA SERVER (10.0.1.11) ===
Add to /etc/mysql/mysql.conf.d/replication.cnf:
[mysqld]
server-id = 2
relay_log = /var/log/mysql/relay.log
read_only = 1

systemctl restart mysql

Import and configure:
mysql -u root -p < /tmp/full.sql
mysql -u root -p -e "
CHANGE MASTER TO
MASTER_HOST='10.0.1.10',
MASTER_USER='replicator',
MASTER_PASSWORD='StrongPass123!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1234;
START SLAVE;"

Verify replication:
mysql -e "SHOW SLAVE STATUS\G" | grep -E "Running|Behind|Error"
Expected: Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0

REAL EXAMPLES:
WordPress read-write split with HyperDB:
Add to wp-config.php:
$wpdb->add_database(array(
'host' => '10.0.1.10', 'write' => 1, 'read' => 0,
'user' => 'wpuser', 'password' => 'pass', 'name' => 'wordpress'
));
$wpdb->add_database(array(
'host' => '10.0.1.11', 'write' => 0, 'read' => 1,
'user' => 'wpuser', 'password' => 'pass', 'name' => 'wordpress'
));

FLOW:
WordPress Writes (INSERT/UPDATE) -> Primary MySQL (10.0.1.10) -> Binary Log -> Replica applies changes
WordPress Reads (SELECT) -> Replica MySQL (10.0.1.11) - same data, replicated within 1-5 seconds

KEY POINTS:
- server-id MUST be unique across all MySQL servers in the cluster
- read_only on replica prevents accidental writes that break replication
- GTID replication (--gtid-mode=ON) simplifies failover vs position-based
- Connect Quest dedicated server pairs ideal for primary + replica setup

COMMON MISTAKES:
- Forgetting server-id uniqueness (replication silently breaks)
- Not enabling read_only on replica (writes break replication)
- Not monitoring replication lag (set alert at > 30 seconds behind)

QUICK FIX:
Replication broken - check: SHOW SLAVE STATUS\G, find Last_SQL_Error
Common fix for duplicate key: STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
For persistent issues: re-initialize replication from fresh dump

DIFFICULTY: Advanced
RELATED: Dedicated Hosting, VPS Hosting, High Availability, Database Performance