How to Configure MySQL Master-Slave Replication on Ubuntu 18.04

Updated on

•

6 min read

How to Configure MySQL Master-Slave Replication on Ubuntu 18.04

MySQL replication is a process that allows data from one database server to be automatically copied to one or more servers.

MySQL supports a number of replication topologies with Master/Slave topology being one of the most well-known topologies in which one database server acts as the master, while one or more servers act as slaves. By default, the replication is asynchronous where the master sends events that describe database modifications to its binary log and slaves request the events when they are ready.

This tutorial covers a basic example of MySQL Master/Slave replication with one master and one slave server on Ubuntu 18.04. The same steps apply for MariaDB.

This type of replication topology is best suited for deploying of read replicas for read scaling, live databases backup for disaster recovery and for analytics jobs.

Prerequisites

This example assumes you have two servers running Ubuntu 18.04, which can communicate with each other over a private network. If your hosting provider doesn’t offer private IP addresses, you can use the public IP addresses and configure your firewall to allow traffic on port 3306 only from trusted sources.

The servers in this example have the following IPs:

Master IP: 192.168.121.190
Slave IP:  192.168.121.236

Install MySQL

The default The Ubuntu 18.04 repositories includes MySQL version 5.7. To avoid any issues, it is best to install the same MySQL version on both servers.

Install MySQL on the Master server:

sudo apt-get updatesudo apt-get install mysql-server

Install MySQL on the Slave server using the same commands:

sudo apt-get updatesudo apt-get install mysql-server

Configure the Master Server

The first step is to configure the master MySQL server. We’ll make the following changes:

To do so open the MySQL configuration file and uncomment or set the following:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
master:/etc/mysql/mysql.conf.d/mysqld.cnf
bind-address           = 192.168.121.190
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log

Once done, restart the MySQL service for changes to take effect:

sudo systemctl restart mysql

The next step is to create a new replication user. Log in to the MySQL server as the root user by typing:

sudo mysql

From inside the MySQL prompt, run the following SQL queries that will create the replica user and grant the REPLICATION SLAVE privilege to the user:

CREATE USER 'replica'@'192.168.121.236' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.121.236';
Make sure you change the IP with your slave IP address. You can name the user as you want.

While still inside the MySQL prompt, execute the following command that will print the binary filename and position.

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

Take note of file name, ‘mysql-bin.000001’ and Position ‘629’. You’ll need these values when configuring the slave server. These values will probably be different on your server.

Configure the Slave Server

Like for the master server above, we’ll make the following changes to the slave server:

  • Set the MySQL server to listen on the private IP
  • Set a unique server ID
  • Enable the binary logging

Open the MySQL configuration file and edit the following lines:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
slave:/etc/mysql/mysql.conf.d/mysqld.cnf
bind-address           = 192.168.121.236
server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log

Restart the MySQL service:

sudo systemctl restart mysql

The next step is to configure the parameters that the slave server will use to connect to the master server. Login to the MySQL shell:

sudo mysql

First, stop the slave threads:

STOP SLAVE;

Run the following query that will set up the slave to replicate the master:

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

Make sure you are using the correct IP address, user name, and password. The log file name and position must be the same as the values you obtained from the master server.

Once done, start the slave threads.

START SLAVE;

Test the Configuration

At this point, you should have a working Master/Slave replication setup.

To verify that everything works as expected, we’ll create a new database on the master server:

sudo mysql
CREATE DATABASE replicatest;

Login to the slave MySQL shell:

sudo mysql

Run the following command to list all databases :

SHOW DATABASES;

You will notice that the database you created on the master server is replicated on the slave:

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

Conclusion

In this tutorial, we have shown you create a MySQL Master/Slave replication.

Feel free to leave a comment if you have any questions.

ubuntumysqlmariadb