Setup MySQL Master-Slave Replication Easily on Ubuntu 18.04

Introduction

Before we begin talking about how to install MySQL Master-Slave Replication on Ubuntu 18.04 – What is MySQL Master-Slave Replication?

MySQL Master-Slave Replication is a technique for maintaining consistent copies of a database across multiple servers. The master server handles write operations and logs them, while the slave server replicates these changes.

To set up replication, configure the master server with a unique server ID and enable binary logging. On the slave server, set a unique server ID and specify the master server's details, including the binary log file and position.

Start the master and slave servers, and they'll establish a connection. The master logs changes to its binary log, and the slave reads and applies these changes. If the connection is interrupted, the slave reconnects and resumes replication from where it left off.

In this tutorial, you will show how to set up MySQL Master/Slave replication on Ubuntu 18.04, and the same instructions apply for MariaDB. We will also address a few FAQs on MySQL Master-Slave Replication.

Advantages of MySQL Master-Slave Replication

1. Improved High Availability: Master-Slave Replication ensures database availability even if the master server fails.
2. Load Balancing: Distribute read queries across multiple slaves, reducing the load on the master server.
3. Scalability: Add more slaves to handle increased read traffic and achieve better performance.
4. Data Redundancy: Copies of data on the slave servers serve as backups, providing data protection against failures.
5. Geographic Distribution: Replicate data to servers in different locations, enabling faster access and disaster recovery capabilities.

Prerequisites for MySQL Master-Slave Replication

This example assumes that you have two Ubuntu 18.04 servers that are connected to each other through a private network. With the public IP addresses in hand, you can securely restrict the traffic on port 3306 to only come from trusted sources, in case your hosting provider does not offer private IP addresses.

Discover what IPs have been assigned to the servers in this example.

Master IP: 192.168.121.190
Slave IP:  192.168.121.236

Install MySQL

The standard MySQL 5.7 version is available in the Ubuntu 18.04 repository. It is recommended to install the same version of MySQL on both servers to prevent any complications.

On the Master server, install MySQL:

sudo apt-get update
sudo apt-get install mysql-server

Using the same commands, install MySQL on the Slave server:

sudo apt-get update
sudo apt-get install mysql-server

Configure the Master Server

Setting up the master MySQL server is the first step. We'll then make the following modifications:

• Configure the MySQL server to listen on the private IP address.
• Create a unique server ID.
• Activate binary logging.

To accomplish this, open the MySQL configuration file and uncomment or set the following:

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

bind-address           = 192.168.121.190
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log

To make changes effective, restart the MySQL service once you have finished.

sudo systemctl restart mysql

Create a new replication user by entering the following command as the root user to connect to the MySQL server:

sudo mysql

Run the following SQL statements in the MySQL prompt to create the replica user and grant them the REPLICATION SLAVE privilege:

CREATE USER 'replica'@'192.168.121.236' IDENTIFIED BY 'replica_password';

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.121.236';

Run the command below to print the binary filename and its position while you're still in the MySQL prompt.

SHOW MASTER STATUS\G

Output

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 629
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Note the filename mysql-bin.000001 and position 629. These values are necessary for configuring the slave server and will likely differ on your server.

Configure the Slave Server

We will modify the slave server similarly to how we modified the master server:

• Make the MySQL server listen on a private IP address.
• Create a unique server ID.
• Turn on binary logging.

Edit the following lines in the MySQL configuration file after opening it:

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

bind-address           = 192.168.121.236
server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log

Launch the MySQL service again:

sudo systemctl restart mysql

Configure the parameters for the slave server to connect to the master server next. To access the MySQL shell, log in as:

sudo mysql

Stop the slave threads first:

STOP SLAVE;

To configure the slave to replicate the master and execute the following query:

CHANGE MASTER TO
MASTER_HOST='192.168.121.190',
MASTER_USER='replica',
MASTER_PASSWORD='replica_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=629;

Ensure your IP address, username, and password are correct. The log file's name and position should match those on the master server.

Start the slave threads after you complete.

START SLAVE;

Test the Configuration

You should now have a working Master/Slave replication configuration.

On the master server, we'll create a new database to ensure everything works as intended.

sudo mysql

CREATE DATABASE replicatest;

Sign in to the MySQL slave shell:

sudo mysql

To list all databases, execute the following command:

SHOW DATABASES;

The database you created on the master server gets replicated on the slave, as you can see:

Output

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replicatest        |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

FAQs to SetUp MySQL Master-Slave Replication on Ubuntu 18.04

Conclusion

We hope this detailed tutorial helped you create a MySQL Master/Slave replication.

If you have any questions or concerns, please leave them in the comments section. We will be glad to answer them.