Master Salve Setup Explained in Mysql (master/slave explained)

🚀 Setting Up MySQL Master-Slave Replication

MySQL replication is a powerful way to create a backup server that stays in sync with your main (master) server. This ensures high availability, load balancing, and disaster recovery. Let’s go through the setup in a clear, beginner-friendly way.

📌 What is Master-Slave Replication?

In this setup, the Master server holds the original data, and the Slave server continuously updates itself by reading the master's binary logs. This creates a near real-time copy of your database.

[MASTER] ---> (Binlog) ---> [SLAVE]

🛠 Step 1: Configure the Master

# Edit my.cnf
[mysqld]
server-id=1
log_bin=/var/log/mysql/mysql-bin.log
binlog_do_db=mydatabase

Restart MySQL:

sudo systemctl restart mysql

Create a replication user:

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

🛠 Step 2: Get the Master Status

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
File: mysql-bin.000001
Position: 154

🛠 Step 3: Configure the Slave

# Edit my.cnf
[mysqld]
server-id=2
relay_log=/var/log/mysql/mysql-relay-bin.log

Restart MySQL:

sudo systemctl restart mysql

Connect the slave to the master:

CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='replica',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

Start replication:

START SLAVE;

✅ Step 4: Verify the Setup

SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

🎯 Conclusion

You’ve successfully set up MySQL master-slave replication! This setup improves redundancy, allows load distribution, and helps in disaster recovery. Always monitor your replication status to ensure data stays synchronized.