MySQL replication setup — 6 steps to configure master-slave replication

MySQL replication is a process where data from one database server (master) is copied automatically to one or more database servers (slaves). This creates redundancy and allows you to distribute read operations across multiple servers.

Setting up replication might seem complex, but the process is actually straightforward. It takes about 15-20 minutes to configure a basic master-slave setup. The key steps involve configuring both servers, creating a replication user, and telling the slave where to read data from.

This guide walks through a real-world replication setup. We'll use MySQL 8.0, but these steps work for MySQL 5.7 and MariaDB as well.

Why use MySQL replication

Replication solves several practical problems. You get automatic data redundancy without manually copying databases. Read operations can be distributed to slave servers, reducing load on the master. And if your master server fails, you can promote a slave to take over.

The performance benefits are real. A single master handling all reads and writes will eventually hit limits. With replication, you can send all writes to the master and distribute reads across multiple slaves. Some applications see 60-70% load reduction on the master server just from this split.

Replication is not the same as backups though. If someone accidentally deletes a table on the master, that deletion will replicate to all slaves within seconds. You still need proper MySQL backup for actual data protection.

Prerequisites for setting up replication

You need two MySQL servers. They can be physical servers, VMs, or Docker containers. Both servers should run the same MySQL version or at least compatible versions. Mixing MySQL 5.7 and 8.0 works, but stay within the same major version when possible.

Network connectivity between servers is critical. The slave must be able to reach the master on MySQL's port (usually 3306). If you're using cloud servers, check security groups and firewall rules. Test connectivity with telnet master-ip 3306 before starting.

You'll also need root access to both MySQL instances. Replication requires changing configuration files and creating users with specific privileges. Make sure you can edit /etc/mysql/my.cnf or equivalent on both servers.

Step 1: Configure the master server

Open the MySQL configuration file on your master server. On Ubuntu/Debian, this is usually /etc/mysql/mysql.conf.d/mysqld.cnf. On CentOS/RHEL, check /etc/my.cnf.

Add these lines under the [mysqld] section:

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

The server-id must be unique across all servers in your replication setup. Use 1 for the master, 2 for the first slave, and so on. The log_bin parameter tells MySQL where to store binary logs. These logs contain all changes made to the database. The slave reads these logs to replicate changes.

The binlog_do_db parameter is optional but recommended. It limits replication to specific databases. If you want to replicate all databases, remove this line. If you have multiple databases to replicate, add multiple binlog_do_db lines.

Restart MySQL to apply changes:

sudo systemctl restart mysql

Verify the binary log is working by logging into MySQL and running:

SHOW MASTER STATUS;

You should see output showing the binary log file name and position. If you see an error, check the MySQL error log at /var/log/mysql/error.log.

Step 2: Create a replication user

The slave needs credentials to connect to the master. Create a dedicated user with replication privileges. Log into the master MySQL server:

mysql -u root -p

Create the replication user:

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

The '%' means this user can connect from any IP address. In production, replace % with the slave server's specific IP for better security. For example, 'replica_user'@'192.168.1.50'.

The password should be strong. Replication traffic is not encrypted by default, so anyone with network access could potentially intercept it. You can enable SSL for replication, but that's beyond basic setup.

Keep these credentials handy. You'll need them in step 4 when configuring the slave.

Step 3: Get the master position and create initial backup

Before setting up the slave, you need to capture the exact position in the binary log where replication should start. You also need a copy of the master's data.

Lock the master database to prevent changes:

FLUSH TABLES WITH READ LOCK;

This prevents any writes to the database. Your application will still be able to read data, but writes will block. Work quickly through the next steps.

Get the current binary log position:

SHOW MASTER STATUS;

Write down the File and Position values. You'll need these exact values later. They look something like mysql-bin.000003 and 157.

Now create a backup of your database. Open another terminal session (don't close the MySQL session with the lock) and run:

mysqldump -u root -p production_db > master_backup.sql

Wait for the dump to complete. The time depends on your database size. Once finished, go back to your MySQL session and unlock the tables:

UNLOCK TABLES;

Your master is now free to accept writes again. Transfer the backup file to your slave server:

scp master_backup.sql user@slave-server:/tmp/

Step 4: Configure the slave server

The slave configuration is similar to the master. Edit the MySQL configuration file on the slave server and add these lines:

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = production_db

The server-id must be different from the master. Use 2 for your first slave. The relay-log parameter specifies where the slave stores logs received from the master. The slave processes these relay logs to apply changes.

Including log_bin on the slave is optional but useful. If you ever need to chain replication (slave of a slave), you'll need binary logs enabled. It also helps with backup tools that read binary logs.

Restart the slave MySQL server:

sudo systemctl restart mysql

Import the backup you transferred earlier:

mysql -u root -p production_db < /tmp/master_backup.sql

This ensures the slave starts with the exact same data as the master had at the moment you locked tables.

Step 5: Start replication

Log into the slave MySQL server and tell it where to replicate from. Use the master position you wrote down in step 3:

STOP SLAVE;

CHANGE MASTER TO
  MASTER_HOST='master-ip-address',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='strong_password_here',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=157;

START SLAVE;

Replace master-ip-address with your master server's IP. Use the exact MASTER_LOG_FILE and MASTER_LOG_POS values from step 3. Getting these wrong means the slave won't know where to start reading.

Check if replication is working:

SHOW SLAVE STATUS\G

Look for these two lines in the output:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Both must say "Yes". If either says "No" or "Connecting", something went wrong. Check the error messages in the same output. Common issues include wrong credentials, network connectivity problems, or incorrect log positions.

The Seconds_Behind_Master value shows replication lag. Zero means the slave is caught up. A few seconds is normal. If this number keeps growing, the slave can't keep up with the master's write load.

Step 6: Test the replication

Create a test table on the master to verify replication works. Log into the master MySQL:

USE production_db;
CREATE TABLE replication_test (
  id INT PRIMARY KEY,
  message VARCHAR(100)
);

INSERT INTO replication_test VALUES (1, 'Replication is working');

Now check the slave. Log into the slave MySQL and run:

USE production_db;
SELECT * FROM replication_test;

You should see the same data. The table and row should appear on the slave within seconds. If you don't see it, replication is not working properly. Go back and check the slave status with SHOW SLAVE STATUS\G.

Try updating and deleting rows on the master. All changes should replicate to the slave. But remember: never write data directly to the slave. The slave should be read-only except for replication. You can enforce this by adding read_only=1 to the slave's MySQL configuration.

Common replication issues

The most frequent problem is replication stopping after a while. This usually happens when a query succeeds on the master but fails on the slave. For example, if you have a unique key constraint and somehow the same value gets inserted twice (maybe from manual changes to the slave).

When replication stops, SHOW SLAVE STATUS\G will show an error message. Read it carefully. It tells you exactly what query failed and why. Sometimes you can skip the problematic query:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

This skips one event from the master. Use it carefully because you're intentionally making the slave different from the master. Only skip events if you understand why the error happened and know skipping won't cause data inconsistency.

Another common issue is replication lag. The slave falls behind because it can't process changes fast enough. This happens when the master has high write load. The slave processes changes sequentially by default, while the master uses multiple threads.

MySQL 8.0 supports parallel replication which helps a lot. Enable it on the slave:

STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;
START SLAVE;

This allows the slave to use 4 threads for applying changes. Adjust the number based on your slave server's CPU cores.

Replication vs backups

Here's something important to understand: replication is not a backup solution. New MySQL administrators often confuse the two. Replication creates live copies of your data, but if you accidentally drop a table on the master, that drop command replicates instantly to all slaves.

Feature	Replication	Backups
Purpose	High availability and read scaling	Data protection and recovery
Recovery point	Current state only	Historical points in time
Accidental deletions	Replicated to slaves	Can be restored from backup
Hardware failure	Switch to slave in seconds	Restore from backup (minutes to hours)
Corruption protection	No	Yes

You need both. Use replication for availability and performance. Use proper backups for actual data protection. Databasus handles MySQL backups with scheduled dumps, compression and storage to S3 or other locations. It's an industry standard for MySQL backup tools and works alongside replication without issues.

Monitoring replication health

Set up monitoring for replication status. At minimum, check these metrics every few minutes:

• Slave_IO_Running and Slave_SQL_Running must be "Yes"
• Seconds_Behind_Master should stay low (under 10 seconds for most applications)
• Last_Error should be empty

Write a simple monitoring script:

#!/bin/bash
SLAVE_STATUS=$(mysql -u root -p'password' -e "SHOW SLAVE STATUS\G")

IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
  echo "ALERT: Replication stopped"
  exit 1
fi

if [ "$SECONDS_BEHIND" -gt 60 ]; then
  echo "WARNING: Replication lag is $SECONDS_BEHIND seconds"
fi

Run this script via cron every 5 minutes. Send alerts to your monitoring system or just email them. You need to know immediately if replication breaks.

Scaling beyond one slave

Once you have one slave working, adding more is easy. Each additional slave follows the same process. Just remember to use a unique server-id for each slave.

Some applications use multiple slaves for different purposes. One slave handles reporting queries that run for minutes. Another slave serves read traffic from the application. This prevents slow queries from affecting application performance.

Slave type	Purpose	Configuration
Read replica	Handles application read queries	Standard configuration, parallel replication enabled
Reporting slave	Runs long analytics queries	Lower priority, larger query cache
Backup slave	Dedicated for taking backups	Delays replication during backup window

You can also set up a delayed slave. This slave runs a few hours behind the master on purpose. If someone accidentally deletes data, you have a few hours to stop the delayed slave before the deletion replicates. Configure it with CHANGE MASTER TO MASTER_DELAY = 10800 (3 hours in seconds).

Conclusion

MySQL replication setup takes about 20 minutes once you know the steps. Configure the master to enable binary logging, create a replication user, capture the log position, set up the slave configuration, start replication and verify it works.

The most common mistakes are using the wrong binary log position or having network connectivity issues. Take your time with step 3 and double-check the position values. Monitor your replication setup actively.

And remember: replication provides high availability and read scaling, but it doesn't replace backups. Use both together for a robust database infrastructure.