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 update

Install MySQL on the Slave server using the same commands:

sudo apt-get update

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 TO

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.