Quick Guide: Configure MySQL Master-Slave Replication on Ubuntu 22.04

MySQL master-slave replication enables automatic data copying from one MySQL server (master) to one or more slave servers, providing scalability, data availability, and disaster recovery capabilities. This guide provides a quick setup process to configure MySQL master-slave replication on Ubuntu 22.04.

Prerequisites

Before starting, ensure you have:

1. Two Ubuntu 22.04 servers (one master, one slave)
2. MySQL Server installed on both systems
3. Root or sudo privileges on both servers
4. IP addresses of both servers noted

Step 1: Install MySQL on both Master and Slave Servers if not already installed

Install MySQL server

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql.service

Backdoor admin login

sudo mysql --defaults-file=/etc/mysql/debian.cnf

Set a root password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '[root_password]';
FLUSH PRIVILEGES;

Restart MySQL server

sudo systemctl restart mysql

Check status

sudo systemctl status mysql

Login with root user

mysql -u root -p

Step 2: Configure Firewall Settings

Install ufw

sudo apt-get -f install ufw

On the master server, allow connections from the slave server

sudo ufw allow from [slave_server_ip] to any port 3306

Replace [slave_server_ip] with your slave server’s IP address.

Step 3: Configure the Master Server

Open the MySQL configuration file on the master server

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

Make the following changes:

bind-address = [master_server_ip]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydatabase

Restart the MySQL master server

sudo systemctl restart mysql

Step 4: Create Replication User

On the master server, create a dedicated replication user

CREATE USER '[user_name]'@'[slave_server_ip]' IDENTIFIED WITH mysql_native_password BY '[password]';

Check replication user

SELECT user, host, plugin
FROM mysql.user
WHERE plugin = 'mysql_native_password';

Grant replication privileges

GRANT REPLICATION SLAVE ON . TO '[user_name]'@'[slave_server_ip]';

Apply privileges

FLUSH PRIVILEGES;

Step 5: Get Master Status

Lock tables

FLUSH TABLES WITH READ LOCK;

Get the current master status

SHOW MASTER STATUS;

Step 6: Create Database Snapshot (If Existing Data)

Export your database using mysqldump

sudo mysqldump -u root -p --all-databases --master-data --single-transaction > backup.sql

Unlock the databases to make them writable again

UNLOCK TABLES;

Send the snapshot to the slave server

scp /home/[user]]/primary-backup.sql [user]@[slave_server_ip]:/home/[user]/Documents

Step 7: Import the Master Database Snapshot into the Slave Database

SSH and log In as the root user on slave server**

mysql -u root -p

Create the database you want to replicate from the master server

CREATE DATABASE mydatabase;
EXIT;

Import the database snapshot

sudo mysql -u root -p mydatabase < /home/[user]/Documents/primary-backup.sql

Step 8: Configure the Slave Server

On the slave server, open the MySQL configuration file

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

Configure the following settings:

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydatabase
relay-log = /var/log/mysql/mysql-relay-bin.log
read_only = 1

The server-id must be different from the master server.

Restart the MySQL slave server

sudo systemctl restart mysql

Step 9: Start Replication

On the slave server, configure the replication settings

Log In as the root user on slave server

mysql -u root -p

Configure the master connection details

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='[master_server_ip]',
SOURCE_USER='[replica_user]',
SOURCE_PASSWORD='[password]',
SOURCE_LOG_FILE='mysql-bin.000003',
SOURCE_LOG_POS=157;

Replace master_server_ip with the public IP address of the master server.
replica_user is the user account you created for replication.
The password is the one you set for that replication user.
For SOURCE_LOG_FILE and SOURCE_LOG_POS, specify the file name and position obtained in Step 5.

Start the replication process

START REPLICA;

Check the replication status

SHOW REPLICA STATUS\G

When the replica’s replication metadata tables are corrupted, missing, or out of sync with the relay-log files.

Check error logs if replication fails:

sudo tail -f /var/log/mysql/error.log

Stop the replica

STOP REPLICA;

Reset the replica’s metadata

RESET REPLICA ALL;

Reconfigure the master connection details

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='[master_server_ip]',
SOURCE_USER='[replica_user]',
SOURCE_PASSWORD='[password]',
SOURCE_LOG_FILE='mysql-bin.000003',
SOURCE_LOG_POS=157;

Start the replication process again

START REPLICA;

Check the replication status

SHOW REPLICA STATUS\G

Step 10: Test the Replication

Insert data in master server

INSERT INTO employees VALUES('7','Sunny','64000','2');

Check the data on the master server

select * from employees;

Check the data on the slave server

select * from employees;

This setup provides a robust MySQL master-slave replication configuration that enhances data availability and enables load distribution for read operations across multiple servers.

Also Read: Deploy Cloud Native PostgreSQL on Kubernetes