SQL & MySQL Interview Questions 46-50 (High Availability & Scaling)
Hello! Welcome to this lesson on high-level database architecture. So far, we've mostly treated the database as a single, reliable box. But what happens when that box gets overwhelmed with traffic, or worse, what happens when it fails?
This lesson covers the essential concepts for scaling (handling more traffic) and ensuring high availability (surviving failures). These are critical topics for any professional backend or full-stack role, and we'll use simple analogies to make them clear and understandable.
46. Explain replication in MySQL. What are its types?
Replication is the process of creating and maintaining live, read-only copies of your main database. The main database is called the primary (or master), and the copies are called replicas (or slaves).
Analogy: The Magic Notebook
Imagine you have a Master Notebook (the primary). Everything you write in it (`INSERT`, `UPDATE`) is instantly and magically copied, word-for-word, into several Replica Notebooks.
This is incredibly useful for two main reasons:
- 1. Read Scaling: If 100 people want to read your notebook, they don't all have to crowd around the master. 99 of them can read from the replicas, leaving the master free for new writes.
- 2. Backup & Failover: If you spill coffee on your master notebook and destroy it, you have an identical replica ready to take its place.
The types of replication describe how patient the master is when writing:
- Asynchronous (Default): The master writes to its own log and tells the application "Done!" immediately. It sends the update to the replicas but doesn't wait for them to confirm.
- Pro: Very fast for the application.
- Con: If the master crashes 1 second after a write, the replica might not have received that write yet (risk of data loss). - Synchronous: The master writes the change and waits for every single replica to confirm it has received and applied the change before telling the application "Done!".
- Pro: Guarantees zero data loss.
- Con: Extremely slow. If one replica is slow or crashes, your entire application stops. Rarely used. - Semi-Synchronous (The balance): The master writes the change and waits for at least one replica to confirm receipt.
- Pro: Much safer than async, much faster than sync. This is the common choice for production systems.
47. What is MySQL clustering?
Clustering is a more advanced concept where multiple database servers (nodes) work together to act as one single database.
It's a "shared-nothing" architecture where data is automatically sharded (split) across all the nodes, but to your application, it just looks like one big database.
Analogy: Replication vs. Clustering
- Replication is one master chef (primary) and several assistants (replicas) who are just copying what the chef does. Only the master chef can create new dishes.
- Clustering (like Percona XtraDB Cluster or Group Replication) is a team of master chefs all working in the same kitchen. You can give your order (`INSERT`) to any of them, and they all coordinate to make sure the meal is consistent. If one chef goes home, the others seamlessly take over.
Here's a quick comparison:
| Feature | Replication (Primary-Replica) | Clustering (e.g., Group Replication) |
|---|---|---|
| Write To | Only the Primary node. | You can write to any node (multi-master). |
| Data Consistency | Eventually consistent (replicas can lag). | Strongly consistent (all nodes must agree). |
| Failover | Manual or needs an external tool. | Automatic and built-in. |
| Complexity | Simpler to set up and manage. | Much more complex (networking, quorum). |
48. What is MySQL failover? How can it be implemented?
Failover is the automatic process of detecting that the primary database has crashed and promoting a replica to become the new primary.
This is the "high availability" (HA) payoff of having a replica. The goal is to minimize downtime.
Analogy: The Understudy
The primary is the "star actor" and the replica is the "understudy." Failover is the system where, the moment the star gets sick (crashes), the understudy (replica) is automatically pushed onto the stage to take over the role.
How it is Implemented
The process has two main challenges:
- 1. Detecting the Failure & Promoting: You don't want to do this manually at 3 AM. You use an orchestration tool like Orchestrator or the older MHA. This tool constantly pings the primary. If the primary fails, the tool automatically picks the best replica and promotes it.
- 2. Routing the Traffic: How does your application know the address of the new primary?
- Virtual IP (VIP): A "floating" IP address. The orchestration tool moves this IP from the failed server to the newly promoted one. Your application is always pointed at the VIP and doesn't need to change.
- Proxy (Recommended): A tool like ProxySQL or HAProxy sits between your app and your databases. The proxy detects the failure (or is told by the orchestrator) and simply starts routing all write traffic to the new primary. This is seamless to your application.
49. Explain read-write splitting in MySQL.
Read-write splitting is the entire purpose of having replicas for scaling.
It is the practice of configuring your application (or a proxy) to send all "write" queries (`INSERT`, `UPDATE`, `DELETE`) to the primary database, while sending all "read" queries (`SELECT`) to the replicas.
Analogy: The Busy Office
This is the perfect analogy.
- The Boss (Primary) is the only one who can sign new contracts or approve paperwork (`WRITE` operations).
- The Assistants (Replicas) have copies of all old memos.
- Read-write splitting is the company policy: If you have new paperwork, you must go to the boss. If you just want to read an old memo, you must go to an assistant.
This keeps the boss free to handle the important write operations, while the assistants handle the high volume of read requests.
-- Your application (or proxy) sees this query:
SELECT * FROM Users WHERE id = 123;
-- It says: "This is a SELECT, it's a read."
-- >> Routes to: replica_server_1.example.com
-- Your application (or proxy) sees this query:
UPDATE Users SET name = 'Alice' WHERE id = 123;
-- It says: "This is an UPDATE, it's a write."
-- >> Routes to: primary_server.example.com
Key Caveat: Replication Lag!
You must always remember "replication lag." If you use asynchronous replication, the assistant (replica) might not have the memo that the boss just wrote 100 milliseconds ago. Your application must be able to handle this "eventual consistency."
50. What is MySQL ProxySQL, and why is it used?
This question ties everything together.
ProxySQL is a high-performance, database-aware proxy that sits between your application and your database cluster.
Analogy: The Smart Receptionist
If your database cluster is a busy office with one boss (primary) and five assistants (replicas), ProxySQL is the smart receptionist that sits in the lobby.
Your application (the employee) doesn't need to know who is who. The employee just hands all their work to the receptionist. The receptionist (ProxySQL) looks at each request and decides where to send it.
Why is it used?
- To Implement Read-Write Splitting: This is its main job. The receptionist looks at the query. If it's a `SELECT`, they send it to an assistant (replica). If it's an `UPDATE`, they send it to the boss (primary).
- To Handle Failover: The receptionist is constantly monitoring the boss's office. If the boss (primary) doesn't answer, the receptionist instantly stops sending new paperwork there and routes it to the new, promoted boss. Your application never even knew there was a problem.
- To Cache Queries: If 20 people in a row ask for the same memo (`SELECT`), the receptionist just hands them a copy from their own desk (ProxySQL's cache) without bothering the assistants at all.
- To Route Queries: It can be configured to send certain queries (e.g., long analytics) to a specific server.
In short, ProxySQL provides a single, stable endpoint for your application and handles all the complex logic of failover, scaling, and routing behind the scenes.