Database Replication Setup for High Availability
This guide walks you through setting up database replication to achieve high availability, ensuring your data remains accessible even if one server fails. You’ll learn how to configure a primary-replica setup, monitor its health, and handle failover scenarios. This process is essential for any application that requires minimal downtime and continuous data access.
What You’ll Need
- Two or more servers (physical or virtual) with the same database software installed (e.g., MySQL, PostgreSQL).
- Administrative (root or sudo) access to each server.
- Network connectivity between the servers, typically on port 3306 (MySQL) or 5432 (PostgreSQL).
- An existing database on the primary server that you want to replicate.
- Unique server IDs for each instance (if using MySQL).
Step-by-Step
- Configure the Primary Server – Edit the database configuration file (e.g.,
my.cnffor MySQL,postgresql.conffor PostgreSQL). Enable binary logging (MySQL:log-bin=mysql-bin,server-id=1) or setwal_level=replicaandmax_wal_senders=10(PostgreSQL). Restart the database service. - Create a Replication User – On the primary, create a dedicated user with replication privileges. For MySQL:
CREATE USER 'replica'@'%' IDENTIFIED BY 'strong_password'; GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';For PostgreSQL:CREATE USER replica REPLICATION LOGIN PASSWORD 'strong_password'; - Take a Consistent Snapshot – If the primary already has data, lock the tables (MySQL:
FLUSH TABLES WITH READ LOCK;) or usepg_basebackup(PostgreSQL) to create a consistent backup. Record the binary log coordinates (MySQL:SHOW MASTER STATUS;) or the WAL position (PostgreSQL:SELECT pg_start_backup('label');). - Copy the Snapshot to the Replica – Transfer the backup file to the replica server. For MySQL, use
mysqldumpor copy the data directory. For PostgreSQL, usepg_basebackupdirectly to the replica. - Configure the Replica Server – On the replica, set a unique
server-id(MySQL) or configurehot_standby=on(PostgreSQL). Restore the backup if necessary. - Start Replication – On the replica, issue the command to start replication. MySQL:
CHANGE MASTER TO MASTER_HOST='primary_ip', MASTER_USER='replica', MASTER_PASSWORD='strong_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;thenSTART SLAVE;PostgreSQL: create arecovery.conffile withprimary_conninfoandstandby_mode=on, then start the server. - Verify Replication Status – Check that replication is running. MySQL:
SHOW SLAVE STATUS\Gand look forSlave_IO_Running: YesandSlave_SQL_Running: Yes. PostgreSQL:SELECT * FROM pg_stat_replication;to see the WAL sender process. - Test Failover (Optional but Recommended) – Simulate a primary failure by stopping the primary service. Promote the replica to primary (MySQL:
STOP SLAVE; RESET SLAVE ALL;PostgreSQL:pg_ctl promote). Verify applications can connect to the new primary. Then, if needed, reconfigure the old primary as a replica of the new primary.
Common Mistakes
- Forgetting to set a unique
server-idon each server – this causes replication to fail silently. - Using a weak password for the replication user, which exposes your data to attacks.
- Not recording the exact binary log coordinates before taking a snapshot, leading to data inconsistency.
- Opening the database to external connections without firewall rules, creating a security risk.
- Assuming replication is automatic after configuration – you must verify it regularly.
Tips
- Use a dedicated network or VLAN for replication traffic to reduce latency and improve security.
- Monitor replication lag (the delay between primary and replica) using tools like
pt-heartbeat(MySQL) orpg_stat_replication(PostgreSQL). - Set up alerts for when replication stops or lags exceed a threshold.
- Test your failover procedure regularly to ensure it works when you actually need it.
- Consider using a connection pooler or proxy to automatically redirect traffic during failover.
Troubleshooting
If replication is not working, check the following:
- Check the error logs – Both primary and replica logs often contain the exact reason for failure.
- Verify network connectivity – Ensure the replica can reach the primary on the correct port (use
telnetornc). - Confirm credentials – Test the replication user login from the replica host.
- Check firewall rules – Ensure the database port is open on both servers.
- Review binary log coordinates – If the replica is using wrong coordinates, it will not sync. Reset and start over if necessary.
- Ensure disk space – A full disk on the replica can stop replication.
Frequently Asked Questions
What is the difference between synchronous and asynchronous replication?
Synchronous replication waits for the replica to confirm a write before committing on the primary, ensuring zero data loss but adding latency. Asynchronous replication does not wait, offering better performance but risking data loss if the primary fails before the replica catches up.
Can I use replication for backups?
Replication is not a substitute for backups. It protects against hardware failure but not against accidental deletions or corruption, which are replicated to the replica. Always maintain regular backups.
How do I handle schema changes (e.g., adding a column) with replication?
Most replication setups handle DDL statements automatically, but it’s safest to perform schema changes during low-traffic periods and test them on the replica first. Some tools like pt-online-schema-change (MySQL) can minimize disruption.
What happens if the replica fails?
The primary continues to operate normally, but you lose redundancy. You should repair or replace the replica and re-sync it from the primary.
Can I have multiple replicas?
Yes, you can have multiple replicas from the same primary, which helps with read scaling. However, each replica adds load to the primary, so plan accordingly.
Is replication the same as clustering?
No. Replication copies data from one server to another, while clustering typically involves multiple servers sharing the same data storage and providing automatic failover. Clustering is often more complex but offers higher availability.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.