MysqlHow-ToBeginner · 4 min read

How to Set Up Master Slave Replication in MySQL

To set up master-slave replication in MySQL, configure the master server to log binary changes and create a replication user, then configure the slave server to connect to the master and start replication using CHANGE MASTER TO and START SLAVE commands. This setup allows the slave to copy data changes from the master automatically.

📐

Syntax

Master-slave replication involves commands on both master and slave servers:

On Master: Enable binary logging and set a unique server ID.
On Slave: Set a unique server ID, then use CHANGE MASTER TO to specify master connection details.
Start replication with START SLAVE.

sql

/* On Master (my.cnf or my.ini) */
[mysqld]
server-id=1
log-bin=mysql-bin

/* On Slave (my.cnf or my.ini) */
[mysqld]
server-id=2

/* On Slave MySQL shell */
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;

START SLAVE;

💻

Example

This example shows how to configure master and slave servers for replication, including creating a replication user and starting the slave.

sql

/* On Master server */
-- Edit my.cnf to add:
-- server-id=1
-- log-bin=mysql-bin

-- Restart MySQL server

-- Create replication user
CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

-- Get current binary log file and position
SHOW MASTER STATUS;

/* Output example:
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      154 |              |                  |
+------------------+----------+--------------+------------------+

-- On Slave server */
-- Edit my.cnf to add:
-- server-id=2

-- Restart MySQL server

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

START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G;

Output

Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0

⚠️

Common Pitfalls

Common mistakes when setting up replication include:

Not setting unique server-id on master and slave.
Forgetting to enable binary logging on the master.
Incorrect replication user permissions or password.
Using wrong MASTER_LOG_FILE or MASTER_LOG_POS values.
Not restarting MySQL after config changes.

Always verify SHOW SLAVE STATUS\G to check replication health.

sql

/* Wrong: Same server-id on master and slave */
-- my.cnf on both servers
server-id=1

/* Right: Unique server-ids */
-- Master my.cnf
server-id=1
-- Slave my.cnf
server-id=2

📊

Quick Reference

Step	Command/Setting	Description
1	server-id=1 (master)	Unique ID for master server
2	log-bin=mysql-bin	Enable binary logging on master
3	CREATE USER 'replica_user'@'%'	Create replication user on master
4	GRANT REPLICATION SLAVE ON .	Give replication rights to user
5	server-id=2 (slave)	Unique ID for slave server
6	CHANGE MASTER TO ...	Configure slave with master details
7	START SLAVE;	Start replication process on slave
8	SHOW SLAVE STATUS\G	Check replication status

✅

Key Takeaways

Set unique server IDs and enable binary logging on the master server.

Create a replication user with proper permissions on the master.

Use CHANGE MASTER TO on the slave with correct master log file and position.

Always restart MySQL after config changes and verify replication status.

Check SHOW SLAVE STATUS\G regularly to ensure replication is running smoothly.