Database Replication, Backup Strategies, and Disaster Recovery: 20 Real Interview Questions

Database replication and backup fundamentals are about protecting data from loss and ensuring continuous availability. Replication copies data to multiple servers (redundancy). Backups create snapshots for recovery. Disaster recovery plans minimize downtime. Understanding these separates engineers who keep systems running from those who cause outages.

Understanding Replication Fundamentals

Replication copies database changes from a primary server to replica (secondary) servers. If primary fails, a replica becomes the new primary (failover). Replication fundamentals answer: how many copies? How fast must replicas catch up? What consistency level do you need?

In layman terms: Replication is like having backup copies of important documents. If your office burns down, you have copies in another building. The copies must stay in sync.

Master-Slave Replication: The Classic Pattern

One master (primary) accepts writes. Multiple slaves (replicas) receive changes via replication logs. Reads distribute to slaves (load balancing). If master fails, promote a slave to master. Simple, but master is a bottleneck.

Master-Master Replication: Multi-Master Writes

Multiple masters accept writes independently. Changes replicate bidirectionally. Improves write availability but introduces conflict challenges: if two masters write different values to the same row simultaneously, which wins? Requires conflict resolution (last-write-wins, merge logic, or application handling).

Backup Strategies: RTO and RPO

RTO (Recovery Time Objective): How long can the system be down? 1 hour? 1 minute? RTO drives technology choices. RPO (Recovery Point Objective): How much data can you lose? 1 day of data? 1 hour? RPO drives backup frequency. Backups are useless without testing recovery (restore drills).

Disaster Recovery Planning

Disaster recovery fundamentals include: identifying critical systems, defining failover procedures, testing regularly, and documenting everything. A disaster recovery plan that hasnt been tested will fail during an actual disaster. Regular drills catch problems before they matter.

5 Complete Interview Questions with Solutions

Question 1: Master-Slave Replication Setup

Set up replication: primary server accepts writes, replica stays in sync. If primary fails, promote replica to primary. How do you ensure no data loss during failover?

-- Master (Primary)
SERVER: master.db
- Accepts all writes
- Logs all changes (binary log)
- Sends log to replicas

-- Slave (Replica)
SERVER: slave.db
- Reads binary log from master
- Applies changes (replication lag)
- Available for reads (load balancing)

-- Problem: If master crashes
// Writes after last replicated change are LOST
// If slave is 5 seconds behind, lose 5 seconds of data

-- Solution: Synchronous Replication
-- Master waits for slave acknowledgment before committing
SET GLOBAL binlog_format = ROW;
CHANGE MASTER TO MASTER_HOST=master, 
  MASTER_LOG_FILE=mysql-bin.000001, 
  MASTER_LOG_POS=154;

-- With semi-sync replication:
// Master writes to log
// Sends to slave
// WAITS for slave acknowledgment
// Then commits
// Guarantees: No data loss if master crashes
// Trade-off: Slower writes (wait for network round-trip)

Explanation: Asynchronous replication is fast but risky. Synchronous replication guarantees no data loss but slower. Replication fundamentals: choose based on RPO. Financial systems need synchronous. Social media tolerates asynchronous.

Google – Senior Database EngineerAmazon – Senior ArchitectMicrosoft – Data Architect

Question 2: RTO and RPO Trade-offs

Your business requirement: RTO 1 hour (acceptable downtime), RPO 15 minutes (lose max 15 min of data). What backup and replication strategy achieves this cost-effectively?

-- RTO 1 hour: 1 hour downtime acceptable
-- RPO 15 min: Lose max 15 min of data

-- Strategy:
1. Master-Slave Replication (Asynchronous)
   - Cheap, simple
   - Slave ready for failover (RTO ~5-10 min)
   - But replication lag = data loss risk

2. Incremental Backups Every 15 Minutes
   - Backup to S3 or offsite
   - Combined with replication = RPO 15 min
   - Keep 2-3 recent backups (cost effective)

3. Backup Strategy:
   00:00 - Full backup (2 GB)
   00:15 - Incremental (50 MB)
   00:30 - Incremental (60 MB)
   00:45 - Incremental (40 MB)
   
   If disaster at 00:47:
   - Restore full backup
   - Apply incremental backups up to 00:45
   - Max loss: 2 minutes

4. Failover Process (RTO 1 hour):
   - Detect primary failure (5 min)
   - Promote replica to primary (10 min)
   - Update application connection strings (10 min)
   - Verify system (30 min)
   = 55 minutes (within 1 hour RTO)

-- Cost:
- Replication: Cheap (minimal overhead)
- Backups: Cheap (incremental small)
- DR server: Cost-effective

Explanation: RTO drives failover technology (replication, standby servers). RPO drives backup frequency. Replication fundamentals: combine async replication (fast) with frequent backups (safety). Most businesses use this approach: balance cost with requirements.

Meta – Senior Database EngineerOracle – Data ArchitectIBM – Senior DBA

Question 3: Backup Verification and Restore Testing

You have full backups but never tested restoration. A disaster strikes, restoration fails. What went wrong, and how do you prevent this?

-- Problem Examples:
1. Backup corrupted but never validated
   - Backup created but data corrupted
   - Only discovered during restoration
   
2. Backup incomplete
   - Missing transaction logs
   - Cant restore to point-in-time
   
3. Incompatible version
   - Backup from MySQL 5.7
   - Restore to MySQL 8.0 fails
   
4. Credentials lost
   - Backup exists but password forgotten
   - Cant restore

-- Solution: Restore Drills
-- Monthly: Restore to isolated server, verify data
mysqldump --single-transaction --master-data 
  --all-databases > /backups/full_$(date).sql

-- Weekly: Validate backup integrity
mysqlcheck --all-databases

-- Monthly Drill:
1. Restore backup to test server
2. Verify data completeness
3. Run application queries
4. Document restore time
5. Update runbook if issues found

-- Result:
- Confidence in restoration
- Discover problems before disaster
- Train team on recovery procedure

Explanation: A backup that doesnt restore is worthless. Replication fundamentals: verify backups regularly. Monthly restore drills catch problems early. Document the recovery procedure and time required.

Stripe – Senior Data EngineerLinkedIn – Database Architect

Question 4: Replication Lag and Consistency Issues

Replica is 10 seconds behind primary. An application reads from replica, gets stale data. How do you prevent this read inconsistency?

-- Problem: Read-after-write inconsistency
1. Write to primary: UPDATE user SET name = John
2. App reads from replica (lagging 10 sec): Gets old name
3. User sees outdated information

-- Solution 1: Read from Primary After Write
// Write: Go to primary
// Read: If recent write, read from primary. Else replica.
if (recent_write) {
  query_primary();
} else {
  query_replica();
}

-- Solution 2: Sticky Connections
// Route users reads to same replica that has their writes
// If replica catches up, distribute other users
// Complex but low latency

-- Solution 3: Synchronous Replication
// Write waits for replica confirmation
// Guarantees consistency but slower

-- Solution 4: Replication Position Checking
// Read replica position, wait if behind
WAIT_FOR_EXECUTED_GTID_SET(
  gtid_from_write
);
// Only read after replica has applied this transaction

-- Best Practice:
// Write: Primary only
// Read: Replica normally, primary for critical reads
// Accepts eventual consistency for most queries

Explanation: Replication lag is fundamental in async replication. Accept it for most reads (eventual consistency). For critical reads (after user write), route to primary. Replication fundamentals: consistency is a spectrum, not binary.

Google – Senior Data ArchitectAmazon – Database Engineer

Question 5: Point-in-Time Recovery (PITR)

A user accidentally deleted important data at 3:00 PM. You need to recover to 2:59 PM. How does PITR work, and what infrastructure supports it?

-- PITR: Point-In-Time Recovery
// Recover to any exact moment in time

-- Infrastructure Required:
1. Full Backup
   - 2:00 PM full backup (100 GB)
   
2. Transaction Logs (Binary Logs)
   - 2:00 PM - 4:00 PM transaction log
   - Every change recorded with timestamp
   - Enable: log_bin = ON
   
3. Steps to Recover:
   - Restore full backup (2:00 PM state)
   - Replay transaction log up to 2:59:59 PM
   - All changes from 2:00-2:59 applied
   - Data at 2:59 PM exactly recovered
   - Changes after 3:00 PM discarded

-- Example:
RESTORE BACKUP full_backup_2pm
REPLAY BINARY LOG FROM 2:00 PM TO 2:59:59 PM

-- Requirements:
1. Binary logging enabled (disk space cost)
2. Backups stored separately from primary
3. Transaction logs preserved
4. Recovery procedure tested

-- Cost-Benefit:
- Pro: Recover from user errors (deletes, updates)
- Con: Requires transaction log storage (10GB+/day)
- Worth it for data-critical systems

Explanation: PITR combines full backups with transaction logs. Recovery fundamentals: without logs, you can only restore to backup time (coarse). With logs, recover to any second (fine-grained). Replication fundamentals: PITR is essential for data protection.

Microsoft – Senior Database ArchitectMeta – Data Engineer

15 Practice Questions (Test Yourself)

Question 6: When you have multiple replicas in different geographic regions, how would you handle failover and ensure that the closest replica becomes primary to minimize latency for users?

Google – Senior Data EngineerAmazon – Senior Architect

Question 7: If your backup storage location is on the same physical server as the primary database, what disaster scenarios would this backup strategy fail to protect against?

Microsoft – Senior Database EngineerMeta – Data Architect

Question 8: When replication lag varies between 1 second and 30 seconds unpredictably, how would you design an application that tolerates this inconsistency without sacrificing user experience?

Oracle – Senior Data ArchitectIBM – Database EngineerLinkedIn – Senior DBA

Question 9: If master-master replication causes write conflicts where two masters update the same row simultaneously with different values, how would you implement conflict resolution that preserves data integrity?

Stripe – Senior Data EngineerGoogle – Database Architect

Question 10: When your RPO requirement changes from 1 hour to 15 minutes, what specific changes would you make to your backup and replication strategy, and what additional infrastructure costs would result?

Amazon – Senior Data ArchitectMicrosoft – Database EngineerMeta – Senior DBA

Question 11: If a backup takes 6 hours to complete and your maintenance window is only 4 hours, how would you implement incremental or differential backups to fit within this constraint?

Flipkart – Senior Database EngineerLinkedIn – Data Architect

Question 12: When setting up disaster recovery across multiple availability zones, what architectural decisions would ensure automatic failover without requiring manual intervention?

Google – Senior Data ArchitectOracle – Senior DBAIBM – Data Architect

Question 13: If transaction logs grow to consume all available disk space, how would you implement log rotation and archival without losing the ability to perform point-in-time recovery?

Stripe – Database EngineerAmazon – Senior Data Engineer

Question 14: When a replica falls significantly behind due to network latency or heavy load, how would you prevent it from causing replication to block on the primary server?

Microsoft – Senior Data ArchitectMeta – Database EngineerGoogle – BI Architect

Question 15: If you need to migrate a large database to a new server during production with near-zero downtime, how would you leverage replication and backup strategies to achieve this safely?

LinkedIn – Senior Data EngineerFlipkart – Data Architect

Question 16: When your organization requires compliance audits with proof of backup integrity, what documentation and testing procedures would satisfy regulatory requirements?

Stripe – Senior Data ArchitectAmazon – Database EngineerOracle – Senior DBA

Question 17: If you have both synchronous and asynchronous replicas, how would you route different write patterns to appropriate replicas based on consistency requirements?

Google – Senior Data EngineerIBM – Data Architect

Question 18: When restoring from backup to a production environment, what verification steps would you perform to ensure data integrity before directing user traffic to the restored database?

Meta – Senior Data ArchitectMicrosoft – Database EngineerLinkedIn – Senior BI Engineer

Question 19: If you discover that your backup encryption keys are stored in the same location as the backups, what security breach and recovery challenges does this create?

Stripe – Database EngineerAmazon – Senior Data Architect

Question 20: How would you design a disaster recovery plan that balances cost, recovery speed, and data protection to provide business continuity for a system with 99.99% availability requirement?

Google – Senior Database ArchitectOracle – Senior DBAIBM – Data Architect

Ready to Master Database Replication & Disaster Recovery?

These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.