MySQL Master–Replica Setup (Ubuntu 20.04 + 22.04)

At a glance#

• Purpose: Configure asynchronous MySQL replication from a primary server to a replica.
• Applies to: MySQL 8.0 on Ubuntu 20.04 and 22.04.
• Risk: High - involves locking the primary and restarting the database.
• Time: 1-2 hours.

Overview#

This document provides a complete guide to setting up MySQL Master–Replica (asynchronous replication) between two Ubuntu servers.

It includes installation, configuration, verification, and maintenance steps.

Server Details#

Database replicated: rep_test

Step 1 — Install MySQL Server#

On Both Servers#

sudo apt update
sudo apt install mysql-server -y

Enable and start the service:

sudo systemctl enable mysql
sudo systemctl start mysql
sudo systemctl status mysql

Step 2 — Secure MySQL Installation#

Run the built-in security script:

sudo mysql_secure_installation

Answer the prompts (recommended):

• Validate password component → Y
• Remove anonymous users → Y
• Disallow root remote login → Y
• Remove test database → Y
• Reload privilege tables → Y

Step 3 — Configure the Master (198.51.100.140)#

Edit MySQL config file:

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

Find and update:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_do_db = rep_test
bind-address = 0.0.0.0

Save and restart MySQL:

sudo systemctl restart mysql

Create replication user#

Log into MySQL:

mysql -u root -p

Then run:

CREATE USER 'replica_user'@'198.51.100.141' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'198.51.100.141';
FLUSH PRIVILEGES;

Lock tables and check binary log#

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Example output:

👉 Keep this session open (do not unlock yet).

Step 4 — Backup Master Database#

In a new terminal:

mysqldump -u root -p --databases rep_test > /tmp/rep_test.sql

Transfer the dump to the replica:

scp /tmp/rep_test.sql root@198.51.100.141:/tmp/

Return to the master session and unlock tables:

UNLOCK TABLES;
EXIT;

Step 5 — Configure the Replica (198.51.100.141)#

Edit MySQL config:

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

Modify:

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin

Save and restart MySQL:

sudo systemctl restart mysql

Import the data from the master#

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

Step 6 — Set Up Replication Link#

Log into MySQL on the replica:

mysql -u root -p

Configure replication (replace with your actual values from SHOW MASTER STATUS):

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='198.51.100.140',
  SOURCE_USER='replica_user',
  SOURCE_PASSWORD='StrongPassword123!',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=1234;

Start the replica:

START REPLICA;

Check replication status:

SHOW REPLICA STATUS\G

✅ Look for:

• Replica_IO_Running: Yes
• Replica_SQL_Running: Yes

Step 7 — Verify Replication#

On master:

USE rep_test;
CREATE TABLE test_replica (id INT PRIMARY KEY, msg VARCHAR(50));
INSERT INTO test_replica VALUES (1, 'Replication successful!');

On replica:

SELECT * FROM rep_test.test_replica;

You should see:

+----+----------------------+
| id | msg                  |
+----+----------------------+
|  1 | Replication successful! |
+----+----------------------+

Step 8 — Common Maintenance Commands#

Step 9 — Troubleshooting#

Step 10 — Security Best Practices#

• Use strong passwords for replication users.
• Limit replication user access by IP.
• Rotate credentials periodically.
• Enable SSL replication if over WAN.
• Keep OS and MySQL updated.

Reference Info#

Final Result#

You now have a fully functional MySQL Master–Replica setup between:

• Master: 198.51.100.140
• Replica: 198.51.100.141
• Database: rep_test

Overview#

This section documents the replication of a real-world sample database (employees) from the MySQL Master to the Replica.

It demonstrates that both schema and large-scale data changes (millions of rows) replicate successfully across the setup.

Server Details#

| Role | Hostname | IP Address | OS | MySQL Version |

|------|-----------|-------------|----------------|

| Master | mysql-master | 198.51.100.140 | Ubuntu 20.04 | 8.0.42 |

| Replica | mysql-slave | 198.51.100.141 | Ubuntu 22.04 | 8.0.43 |

Replication type: GTID-based Asynchronous Replication

Replication mode: Global (all databases replicate)

Step 1 — Download Employees Sample Database#

Run on master:

cd /tmp
wget https://github.com/datacharmer/test_db/archive/refs/heads/master.zip
unzip master.zip
cd test_db-master

This downloads the official MySQL Employees sample dataset, which includes:

• 300,000 employees
• 4 million salary records
• Department and title history

Step 2 — Import Database on Master#

Run:

mysql -u root -p < employees.sql

This will:

• Create the employees database automatically
• Load all related tables and sample data

Step 3 — Verify Database on Master#

mysql -u root -p -e "SHOW DATABASES;"

Expected:

+--------------------+
| Database           |
+--------------------+
| employees          |
| mysql              |
| performance_schema |
| rep_test           |
| sys                |
+--------------------+

Check table list:

mysql -u root -p -e "SHOW TABLES IN employees;"

Step 4 — Monitor Replication on Replica#

While the import runs (it may take a few minutes), monitor replication status on the replica:

watch -n 5 "mysql -u root -p -e 'SHOW REPLICA STATUS\G' | grep Seconds_Behind_Source"

You’ll see:

Seconds_Behind_Source: 15
Seconds_Behind_Source: 5
Seconds_Behind_Source: 0

When it reaches 0, replication is fully caught up.

Step 5 — Verify Database on Replica#

After replication catches up, confirm the database appeared automatically on the replica:

mysql -u root -p -e "SHOW DATABASES;"

Expected:

+--------------------+
| Database           |
+--------------------+
| employees          |
| mysql              |
| performance_schema |
| rep_test           |
| sys                |
+--------------------+

Then check data consistency:

mysql -u root -p -e "SELECT COUNT(*) FROM employees.employees;"

✅ Expected count: 300024

(same on both master and replica)

Step 6 — Explore the Data#

Example queries#

mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 10;"
mysql -u root -p -e "USE employees; SELECT * FROM departments;"
mysql -u root -p -e "USE employees; SELECT COUNT(*) FROM salaries;"

Example output#

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 10001  | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
| 10002  | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
| 10003  | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
+--------+------------+------------+-----------+--------+------------+

Step 7 — Data Verification Summary#

Result#

• The Employees database (millions of rows) replicated fully from master to replica.
• Replication includes schema (CREATE DATABASE, CREATE TABLE) and data (inserts/updates).
• Confirmed that global replication mode correctly syncs all databases.

Final Verification Snapshot#

Notes#

• MySQL now replicates all databases (no binlog_do_db filtering).
• Schema and data replication are ensured via binlog_format = MIXED.
• GTID-based replication allows recovery without reconfiguring file/position.