What is Replication in MySQL?

Replication in MySQL is a process of copying data from one database server (master) to one or more servers (slaves/replicas). It ensures high availability, data redundancy, load balancing, and disaster recovery.

Common use cases include:

• Scaling out reads across multiple servers
• Backup without affecting the master
• Real-time reporting or analytics
• Failover solutions in production systems

There are two main types:

• Asynchronous Replication: Slave may lag behind master slightly.
• Semi-Synchronous Replication: Slaves confirm receipt before commit.

1. General Setup Syntax (Classic Statement-Based Replication)

Replication setup includes configuration in both master and slave servers.

On the Master Server:

1. Edit the MySQL config file:   In my.cnf or my.ini:

[mysqld]

server-id=1

log-bin=mysql-bin

binlog_do_db=your_database

1. Create a replication user:

CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘password’;

GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;

FLUSH PRIVILEGES;

1. Check current binary log position:

SHOW MASTER STATUS;

This returns:

File: mysql-bin.000001

Position: 154

On the Slave Server:

1. Edit the MySQL config:

[mysqld]

server-id=2

relay-log=relay-log

1. Connect the slave to the master:

CHANGE MASTER TO

  MASTER_HOST=’master_ip’,

  MASTER_USER=’repl’,

  MASTER_PASSWORD=’password’,

  MASTER_LOG_FILE=’mysql-bin.000001′,

  MASTER_LOG_POS=154;

1. Start the replication:

START SLAVE;

2. Example: Setting Up One-Way Replication

On Master (192.168.1.100):

CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘replica123’;

GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;

FLUSH PRIVILEGES;

SHOW MASTER STATUS;

— Returns File = mysql-bin.000003, Position = 120

On Slave (192.168.1.101):

CHANGE MASTER TO

  MASTER_HOST=’192.168.1.100′,

  MASTER_USER=’repl’,

  MASTER_PASSWORD=’replica123′,

  MASTER_LOG_FILE=’mysql-bin.000003′,

  MASTER_LOG_POS=120;

START SLAVE;

To check if replication is working:

SHOW SLAVE STATUSG

3. Explanation

• server-id: Each MySQL instance in replication must have a unique ID.
• log-bin: Enables binary logging on the master (required).
• binlog_do_db: Limits replication to only the specified database.
• SHOW MASTER STATUS: Shows binary log file name and position to sync from.
• CHANGE MASTER TO: Configures the slave’s connection to the master.

START SLAVE: Starts the replication threads.