YouTube Icon

Code Playground.

How to Configure MySQL Master-Slave Replication on Ubuntu 18.04


How to Configure MySQL Master-Slave Replication on Ubuntu 18.04

MySQL replication is a cycle that permits information from one data set worker to be naturally duplicated to at least one workers. 

MySQL underpins various replication geographies with Master/Slave geography being one of the most notable geographies in which one information base worker goes about as the expert, while at least one workers go about as slaves. Of course, the replication is offbeat where the expert sends occasions that depict information base adjustments to its paired log and slaves demand the occasions when they are prepared. 

This instructional exercise covers a fundamental illustration of MySQL Master/Slave replication with one expert and one slave worker on Ubuntu 18.04. Similar advances apply for MariaDB. 

This kind of replication geography is most appropriate for sending of read reproductions for read scaling, live information bases reinforcement for catastrophe recuperation and for investigation occupations. 


This model expects you have two workers running Ubuntu 18.04, which can speak with one another over a private organization. In the event that your facilitating supplier doesn't offer private IP addresses, you can utilize the public IP addresses and design your firewall to permit traffic on port 3306 just from confided in sources. 

The workers in this model have the accompanying IPs: 

Master IP:
Slave IP:

Install MySQL

The default The Ubuntu 18.04 stores incorporates MySQL variant 5.7. To evade any issues, it is ideal to introduce a similar MySQL adaptation on the two workers. 

Introduce MySQL on the Master worker: 

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

Introduce MySQL on the Slave worker utilizing similar orders: 

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

Configure the Master Server

The initial step is to arrange the expert MySQL worker. We'll roll out the accompanying improvements: 

  • Set the MySQL worker to tune in on the private IP . 
  • Set a remarkable worker ID.. 
  • Empower the paired logging 

To do so open the MySQL setup record and uncomment or set the accompanying: 

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


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

When done, restart the MySQL administration for changes to produce results: 

sudo systemctl restart mysql 

The subsequent stage is to make another replication client. Sign in to the MySQL worker as the root client by composing: 

sudo mysql 

From inside the MySQL brief, run the accompanying SQL questions that will make the copy client and award the REPLICATION SLAVE advantage to the client: 

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

Ensure you change the IP with your slave IP address. You can name the client as you need. 

While still inside the MySQL brief, execute the accompanying order that will print the double filename and position. 

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

Observe document name, 'mysql-bin.000001' and Position '629'. You'll require these qualities while arranging the slave worker. These qualities will presumably be diverse on your worker. 

Configure the Slave Server

Like for the expert worker above, we'll roll out the accompanying improvements to the slave worker: 

Set the MySQL worker to tune in on the private IP 

Set an exceptional worker ID 

Empower the parallel logging 

Open the MySQL design record and alter the accompanying lines: 

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


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

Restart the MySQL administration: 

sudo systemctl restart mysql

The following stage is to design the boundaries that the slave worker will use to associate with the expert worker. Login to the MySQL shell: 

sudo mysql

To begin with, stop the slave strings: 


Run the accompanying inquiry that will set up the captive to repeat the expert: 


Ensure you are utilizing the right IP address, client name, and secret word. The log document name and position must be equivalent to the qualities you acquired from the expert worker. 

When done, start the slave strings. 


Test the Configuration

Now, you ought to have a working Master/Slave replication arrangement. 

To check that everything fills in true to form, we'll make another information base on the expert worker: 

sudo mysql
CREATE DATABASE replicatest;

Login to the slave MySQL shell: 

sudo mysql

Run the accompanying order to list all information bases : 


You will see that the information base you made on the expert worker is imitated on the slave: 

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


In this instructional exercise, we have indicated you make a MySQL Master/Slave replication. 

Don't hesitate to leave a remark in the event that you have any inquiries.