MySQL Replication Setup Guide: Master-Slave Configuration for Beginners

MySQL replication is a powerful feature that lets you copy data from one MySQL server (the master) to one or more others (the slaves). This is commonly used for load balancing, backups, and high availability.

If you're new to MySQL replication, this guide will walk you through setting up a simple master-slave configuration.

Why Use MySQL Replication?

Before diving into the setup, let’s understand why it’s useful:

• Scalability: Spread read queries across multiple servers.
• Backup & Recovery: Create real-time replicas for disaster recovery.
• ‍Analytics: Run heavy analytics queries on slaves without affecting the master.

Prerequisites

To follow along, you’ll need:

• Two MySQL servers (can be VMs, Docker containers, or separate machines)
• MySQL installed on both (version 5.7+ or 8.x)
• Basic knowledge of the terminal and MySQL commands
• Open firewall ports (typically 3306)

We'll call them:

• Master: 192.168.1.100
• Slave: 192.168.1.101

Step 1: Configure the Master Server

1.1 Edit MySQL Configuration

Open the MySQL config file (usually /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf):

Code

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = database_name
      

• server-id: Must be unique across all servers.
• log_bin: Enables binary logging.
• binlog_do_db: Replicate only this database.

Restart MySQL:

Code

sudo systemctl restart mysql
      

1.2 Create Replication User

Log in to MySQL and create a user for replication:

Code

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

1.3 Get Master Status

This tells the slave where to start replicating:

Code

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
      

Note down the File and Position. Keep the terminal open to maintain the lock or dump the database now.

1.4 Dump the Database

Use mysqldump to export the database:

Code

mysqldump -u root -p --databases database_name > database_name.sql
      

Copy the dump to the slave server:

Code

scp database_name.sql user@192.168.1.101:/tmp/
      

Now unlock the master:

Code

UNLOCK TABLES;
      

Step 2: Configure the Slave Server

2.1 Edit MySQL Configuration

On the slave server:

Code

[mysqld]
server-id = 2
      

No need to set log_bin unless you plan to cascade.

Restart MySQL:

Code

sudo systemctl restart mysql
      

2.2 Import the Database

Code

mysql -u root -p < /tmp/database_name.sql
      

2.3 Configure Replication

Tell the slave where the master is and what log position to start from:

Code

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

(Replace file and position with what you got earlier.)

2.4 Start the Slave

Code

START SLAVE;
      

Check the status:

Code

SHOW SLAVE STATUS\G
      

Look for:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes

If both are Yes, you're good to go! 

Testing the Replication

On the master:

Code

USE database_name;
CREATE TABLE test (id INT PRIMARY KEY);
INSERT INTO test VALUES (1);
      

Now on the slave:

Code

SELECT * FROM database_name.test;
      

You should see the inserted row!

Hire Now!

HIRE MySQL Developers Today!

Ready to elevate your digital product's user experience? Start your project with Zignuts expert MySQL Developers.

**Hire now**Hire Now**Hire Now**Hire now**Hire now

Hire now

References & Further Reading

• Official MySQL Documentation – Replication https://dev.mysql.com/doc/refman/8.0/en/replication.html

 A comprehensive guide covering all aspects of replication, including GTID, multi-source replication, and replication formats.

• MySQL High Availability Book (O’Reilly) : ISBN-13: 978-0596807290

 A go-to guide if you're aiming to master replication for high availability and scaling.

Our skilled MySQL developers provide end-to-end support for replication, scaling, and database optimization. Contact us today to ensure smooth and reliable performance.