Database Replication: Types, Features, and Technologies

By /

Listen to this Post

Featured Image
Database replication is a critical process for ensuring data availability, fault tolerance, and performance optimization. It involves copying and synchronizing database objects across multiple systems to maintain redundancy and enable seamless failover.

Types of Database Replication

1. Master-Slave Replication

  • The master node handles write operations, while slave nodes replicate data for read operations.
  • Example MySQL command: 
    CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='repl_user', MASTER_PASSWORD='password';
START SLAVE;

2. Master-Master Replication

  • Multiple nodes can accept writes and synchronize bidirectionally.
  • Example PostgreSQL setup: 
    ALTER SYSTEM SET wal_level = 'logical';
CREATE PUBLICATION pub_name FOR ALL TABLES;
CREATE SUBSCRIPTION sub_name CONNECTION 'host=node2 dbname=db' PUBLICATION pub_name;

3. Synchronous Replication

  • Transactions commit only after all replicas confirm receipt.
  • Used in high-consistency systems like financial databases.

4. Asynchronous Replication

  • Primary commits first, then propagates changes.
  • Faster but risks data loss if the primary fails.

5. Transactional Replication

  • Real-time replication preserving transaction order.
  • Common in SQL Server: 
    EXEC sp_addsubscription @publication = 'pub_name', @subscriber = 'sub_server';

6. Snapshot Replication

  • Periodic full database copies.
  • Useful for reporting servers.

Features of Database Replication

  • Data Redundancy: Ensures backups exist across locations.
  • Automatic Failover: MongoDB Replica Sets auto-elect a new primary.
  • Load Distribution: Distributes queries across replicas.
  • Real-Time Sync: PostgreSQL WAL streaming minimizes delay.
  • Multi-Site Support: Geo-distributed databases for disaster recovery.

Common Replication Technologies

MySQL Replication

-- On Master: 
GRANT REPLICATION SLAVE ON . TO 'repl_user'@'slave_host'; 
FLUSH PRIVILEGES;

-- On Slave: 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

PostgreSQL Streaming Replication

 Enable WAL archiving: 
wal_level = replica 
archive_mode = on 
archive_command = 'cp %p /var/lib/postgresql/wal/%f'

MongoDB Replica Sets

rs.initiate({ 
_id: "rs0", 
members: [ 
{ _id: 0, host: "mongo1:27017" }, 
{ _id: 1, host: "mongo2:27017" } 
] 
});

SQL Server Always On

-- Create Availability Group: 
CREATE AVAILABILITY GROUP [bash] 
WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY);

You Should Know:

  • Monitoring Replication Lag: 
    MySQL: 
SHOW SLAVE STATUS\G

PostgreSQL: 
SELECT pg_current_wal_lsn() - replay_lsn FROM pg_stat_replication;

  • Forcing Failover in MongoDB: 

    rs.stepDown(300); // Forces primary to step down for 300 seconds

  • Resyncing a Broken Replica: 

    PostgreSQL: 
pg_rewind --target-data-directory /var/lib/postgresql/data --source-server="host=primary"

What Undercode Say:

Database replication is essential for high availability and disaster recovery. Implementing it correctly requires understanding trade-offs between consistency and performance. Always monitor replication health and test failover procedures regularly.

Expected Output:

A robust replication setup ensuring minimal downtime and data loss.

Prediction:

Increased adoption of multi-cloud replication solutions for hybrid environments.

🔗 Relevant URLs:

References:

Reported By: Ashsau Database – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅

Join Our Cyber World:

💬 Whatsapp | 💬 Telegram

Related Posts: