Replication

Replication in MySQL is the process of copying data from one database server (the master / primary) to one or more other servers (slaves / replicas).

The goal is to keep the replica servers synchronized with the master.

Limitations of Replication

• Replication Lag → Slaves may not be up-to-date.
• Writes still go to master → not good for heavy write workloads.
• Conflict risk in multi-source replication → if both masters write to the same table.
• Not a full backup → accidental deletes on master replicate to slaves.

How MySQL Replication Works

Replication happens in 3 steps:

1. Master writes changes to binary log (binlog).
2. Replica reads master’s binlog into its relay log.
3. Replica SQL thread applies changes to its own database.

Replication is usually asynchronous (replicas might lag), but can also be semi-synchronous (master waits for at least one replica acknowledgment).

Master-Slave Replication

• One master server handles all writes (INSERT/UPDATE/DELETE).
• One or more slave servers handle reads (SELECT).
• Slaves replicate data from the master continuously.

Setting Up Master-Slave Replication

Initial Setup

Create Docker Network

docker network create mysqlnet

Step 1: On the Master

Create a custom configuration file at T:\project\master.cnf

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW

Run the Master Container

docker run -d ^ --name mysql-master ^ --network mysqlnet ^ -e MYSQL_ROOT_PASSWORD=rootpass ^ -v T:\project\master.cnf:/etc/mysql/conf.d/master.cnf ^ -v master_data:/var/lib/mysql ^ -p 3308:3306 ^ mysql:latest

docker exec -it mysql-master bash

mysql -uroot -p

Verify Installation

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'server_id';

Restart MySQL:

docker restart mysql-master

Create a replication user:

CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'replicapass';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

Check master status:

SHOW BINARY LOG STATUS;

Output example:

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |      863 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.006 sec)

Note these values; they’ll be used on the slave.

Step 2: On the Slave

Run the Slave Container

docker run -d --name mysql-slave --network mysqlnet -e MYSQL_ROOT_PASSWORD=rootpass -v slave_data:/var/lib/mysql -p 3309:3306 mysql:latest --server-id=2 --relay-log=relay-bin --log-bin=mysql-bin --read-only=1

docker exec -it mysql-slave bash

mysql -uroot -p

Verify Installation

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'server_id';

Connect slave to master:

RESET REPLICA ALL;
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='mysql-master',
  SOURCE_USER='replica',
  SOURCE_PASSWORD='replicapass',
  SOURCE_LOG_FILE='binlog.000005',
  SOURCE_LOG_POS=548,
  GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

Check status:

SHOW REPLICA STATUS\G

If both Replica_IO_Running and Replica_SQL_Running are Yes, replication is successful.

Step 3: Test

On master:

INSERT INTO employees (name, department, salary) VALUES ('Alice', 'IT', 70000);

On slave:

SELECT * FROM employees WHERE name = 'Alice';

You’ll see the row replicated.

Multi-Source Replication

Multi-source replication allows one slave to replicate data from multiple masters.

Useful for merging data from different databases into a central server.

Setting Up Multi-Source Replication

Suppose we have:

• Master1 = HR database
• Master2 = Sales database
• Slave = Central analytics server

Step 1: Configure Master1 and Master2

Enable binary logs on both:

[mysqld]
server-id=1   # On Master1
log_bin=mysql-bin

[mysqld]
server-id=2   # On Master2
log_bin=mysql-bin

Create replication users on both masters.

Step 2: Configure Slave

Enable multi-source replication in my.cnf:

[mysqld]
server-id=3

Step 3: Connect Slave to Master1 and Master2

-- For Master1
CHANGE MASTER TO
  MASTER_HOST='master1_ip',
  MASTER_USER='replica',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154
FOR CHANNEL 'master1';

START SLAVE FOR CHANNEL 'master1';

-- For Master2
CHANGE MASTER TO
  MASTER_HOST='master2_ip',
  MASTER_USER='replica',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=200
FOR CHANNEL 'master2';

START SLAVE FOR CHANNEL 'master2';

Check replication channels:

SHOW SLAVE STATUS FOR CHANNEL 'master1'\G
SHOW SLAVE STATUS FOR CHANNEL 'master2'\G

Replication in the Cloud

Cloud providers (like AWS RDS, Google Cloud SQL, Azure Database for MySQL) provide managed replication:

• You don’t need to manually configure binlogs.
• They offer read replicas for scaling reads.
• Failover and monitoring are automated.

Example in AWS RDS:

• Create a primary MySQL instance.
• Add a read replica via AWS console.
• AWS handles replication setup automatically

Load Balancing

Load balancing means distributing client requests across multiple database servers to improve performance and scalability.

In MySQL, this is usually done in replication setups:

• Master (Primary) → handles writes (INSERT, UPDATE, DELETE)
• Slaves (Replicas) → handle reads (SELECT)
• A load balancer (proxy or middleware) distributes queries.

Benefits of Load Balancing

• Better performance (scale reads across replicas).
• Prevents a single server from being overloaded.
• Enables horizontal scaling (add more replicas).

Load Balancing Reads

Suppose we have:

• 1 Master: master-db (for writes).
• 2 Slaves: slave1-db, slave2-db (for reads).
• Load Balancer: ProxySQL (or HAProxy, MySQL Router).

Without Load Balancer: Applications must manually connect to master for writes and slaves for reads.

-- Application connects directly to master for writes
INSERT INTO employees (name, department, salary) VALUES ('Alice', 'HR', 60000);

-- Application connects directly to a slave for reads
SELECT * FROM employees WHERE department = 'HR';

Problem: Application must handle logic for routing queries.

With Load Balancer

Applications connect to ProxySQL/MySQL Router → it automatically sends writes to master and reads to slaves.

-- Application just connects to the proxy
SELECT * FROM employees;   -- Proxy routes to slave1 or slave2
INSERT INTO employees VALUES ('Bob', 'IT', 70000); -- Proxy routes to master

Load balancer ensures read queries are spread across replicas, and write queries always go to master.

High Availability

High Availability means the database remains available even if a server crashes.

This is achieved with:

• Replication → Having multiple copies of the database.
• Automatic failover → If the master fails, a slave is promoted to master.
• Monitoring → To detect failures and trigger failover.

High Availability Approaches in MySQL

1. Master-Slave with Failover
   • One master, multiple slaves.
   • A monitoring tool (MHA, Orchestrator, or cloud service) promotes a slave if the master fails.
2. MySQL Group Replication / InnoDB Cluster
   • Multiple servers form a group.
   • Supports multi-master writes.
   • Provides built-in failover and HA.
3. Cloud Provider HA
   • AWS RDS / Aurora: Automated failover to replica.
   • Google Cloud SQL: Multi-zone replication with automatic failover.
   • Azure Database for MySQL: Geo-replication for HA.

High Availability with Replication

Without HA

• Master fails → application cannot process writes until manually fixed.

With HA (Orchestrator / MHA)

• Monitoring detects master failure.
• A replica (slave1-db) is promoted to master.
• Load balancer (ProxySQL/MySQL Router) automatically reroutes writes to new master.

Application continues to work with minimal downtime.