How to Configure MySQL 8 Replication (Master-Slave) on Ubuntu

MySQL replication is a powerful feature that allows data from one MySQL database server (the master) to be replicated to one or more servers (the slaves). This setup is essential for load balancing, high availability, backup, and disaster recovery strategies.

In this step-by-step guide, you’ll learn how to configure MySQL 8 replication on Ubuntu — setting up both the master and replica (slave) servers.

🧰 Prerequisites

Before you begin, ensure the following:

• Two Ubuntu servers (20.04+ recommended)
  • Master: IP = 192.168.1.100
  • Replica: IP = 192.168.1.101
• MySQL 8 installed on both
• Root/sudo access
• Static IPs configured
• Open ports (especially 3306)

⚙️ Step 1: Install MySQL 8 on Both Servers

If not already installed, run:

sudo apt update
sudo apt install mysql-server

Verify MySQL version:

mysql --version

Make sure it’s MySQL 8.x.

🔐 Step 2: Secure MySQL Installation

Run the built-in security script:

sudo mysql_secure_installation

• Set root password
• Remove anonymous users
• Disallow remote root login (on master)
• Remove test DB
• Reload privileges

📁 Step 3: Configure the Master Server

1. Edit MySQL configuration

Open the config file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find and update or add:

bind-address = 192.168.1.100
server-id = 1
log_bin = mysql-bin
binlog_do_db = your_database_name  # Optional: replicate only a specific DB

Replace your_database_name with the database you want to replicate.

Restart MySQL:

sudo systemctl restart mysql

2. Create a Replication User

Log into MySQL:

sudo mysql -u root -p

Create the user and grant replication privileges:

CREATE USER 'replica'@'192.168.1.101' IDENTIFIED WITH mysql_native_password BY 'StrongPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.1.101';
FLUSH PRIVILEGES;

3. Get Binary Log Info

Still in the MySQL prompt, run:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note down:

• File: e.g., mysql-bin.000001
• Position: e.g., 154

Leave this terminal open to keep the lock active. You can use another terminal to proceed on the replica server.

🧪 Step 4: Configure the Replica Server

1. Edit MySQL Configuration

Open MySQL config file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Set:

bind-address = 192.168.1.101
server-id = 2
relay-log = mysql-relay-bin

Restart MySQL:

sudo systemctl restart mysql

2. Set Up Replication on Replica

Log into MySQL:

sudo mysql -u root -p

Run the following command, replacing values:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '192.168.1.100',
  SOURCE_USER = 'replica',
  SOURCE_PASSWORD = 'StrongPass123!',
  SOURCE_LOG_FILE = 'mysql-bin.000001',
  SOURCE_LOG_POS = 154;

Start replication:

START REPLICA;

Verify:

SHOW REPLICA STATUS\G

Look for:

• Replica_IO_Running: Yes
• Replica_SQL_Running: Yes

If both are “Yes”, replication is working!

Now go back to the master server’s terminal and unlock the tables:

sqlCopyEditUNLOCK TABLES;

✅ Step 5: Test Replication

1. On the master, create a test DB or table:

CREATE DATABASE test_replication;

2. On the replica, check if it appears:

SHOW DATABASES;

You should see test_replication.

🔒 Step 6: Firewall and Security (Optional but Recommended)

Allow MySQL port only between servers:

On master:

sudo ufw allow from 192.168.1.101 to any port 3306

On replica:

sudo ufw allow from 192.168.1.100 to any port 3306

Enable firewall if not already:

sudo ufw enable

🛠 Troubleshooting Common Errors

🧩 Tips and Best Practices

• Use SSH tunnels or SSL for secure replication over public networks
• Use GTIDs (Global Transaction Identifiers) for better failover and multi-source replication
• Automate backups and monitor replication status regularly
• Avoid DDL (schema changes) during replication as they may cause issues

📦 Optional: Use GTID-Based Replication (Advanced)

Replace the earlier replication setup with:

gtid_mode = ON
enforce-gtid-consistency = ON

Then use:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='replica',
  SOURCE_PASSWORD='StrongPass123!',
  SOURCE_AUTO_POSITION=1;

📘 Conclusion

Setting up MySQL 8 replication on Ubuntu is a crucial step for building scalable and fault-tolerant database systems. With just a few configuration changes and a dedicated replication user, you can achieve real-time data redundancy across multiple servers.

This setup gives you a strong foundation for advanced features like read scaling, failover, and zero-downtime backups.