How to Setup Database Replication in Mysql on Ubuntu

We all know how critical are database servers if a database server goes down or corrupts due to some we should always have a backup present.

This guide will help you set up database replication of MySQL database from a Master server to a Slave server.

Prerequisites

Important: If this is a new server (i.e if no databases already present) you can directly proceed to step 1. If not, create the databases present on the MASTER server on the SLAVE server. Using the following steps.

On Master server:

To back up all the MySQL databases:

$ sudo mysqldump -u root -p --all-databases > all_databases.sql

OR
Backup all MySQL databases to separate files

for DB in $(mysql -e 'show databases' -s --skip-column-names); do
    mysqldump $DB > "$DB.sql";
done

After taking backup login to the slave server and restore these backed up database dumps using following steps.

On Slave server:

Create and restore databases one by one using these steps:

The first command will create a database named database_name and then it will import the dump database_name.sql into it:

$ sudo mysql -u root -p -e "create database database_name";
mysql -u root -p database_name < database_name.sql

After this process is done, we can move on to step 1 for database replication:

For this setup we will be using Ubuntu 18.04 with following server IP addresses, you can replace these IPs with your configuration accordingly:

Master Server: 10.0.2.1
Slave Server: 10.0.2.2

Step 1: Install MySQL on Master and Slave Servers

First, update the repositories using following apt command on both the Master and Slave nodes:

$sudo apt update

Second, install MySQL on both nodes, execute the following command.

$sudo apt install mysql-server mysql-client

Next, on master node, open the mysql config file.

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

On the Master node, scroll and locate the bind-address attribute as shown below.

bind-address = 127.0.0.1

Replace this bind-address with your Master node IP. In our case we will replace as shown below.

bind-address   = 10.0.2.1

Next, we need to specify a value for the server-id attribute in the [mysqld] section. The number we choose should not match any other server-id number in our fleet. We will assign the value 1.

server-id  = 1

Next, at the end of the configuration file, copy and paste the lines below.

log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

Save and close the configuration file and restart MySQL service for the changes to take effect on Master node.

$ sudo systemctl restart mysql

To check if MySQL server is running as expected, execute the following command.

$ sudo systemctl status mysql

Step 2: Create a new user for Database Replication on Master Server

In this step, we are going to create a replication user in the master node. Log in to the MySQL server on Master node as shown below.

$ sudo mysql -u root -p

Next, execute the following queries to create a replica user and grant access to the replication slave. Ensure to use your SLAVE IP address.

mysql> CREATE USER 'replica_user'@'10.0.2.2' IDENTIFIED BY 'replica_password';
mysql> GRANT REPLICATION SLAVE ON . TO 'replica_user '@'10.0.2.2';

Next, execute the following command.

mysql> SHOW MASTER STATUS\G

We will get output as shown below:

Note the mysql-bin.000016 value and Position ID 15634. These values will be required while setting up the Slave server.

Step 3: Setup the MySQL Slave Server

Login to the slave server and as we did with the Master server, open the MySQL configuration file.

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

As we did with the master server, edit the following lines and replace this bind-address with your Slave node IP. In our case we will replace as shown below.

bind-address           = 10.0.2.2

Like before, specify a value for the server-id attribute in the [mysqld] section. This time select a different value than the one we used for master node. We will use the value 2.

server-id		= 2

Again, at the end of the configuration file, copy and paste the lines below.

log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

Save and close the configuration file and restart MySQL service for the changes to take effect on Slave node.

$ sudo systemctl restart mysql

Next, Log in to the MySQL server on Slave node as shown below.

$ sudo mysql -u root -p

In next step, we will need to make some configuration that will allow the slave server to connect to the master server. But first, stop the slave threads as shown below.

mysql> STOP SLAVE;

Next, we allow the slave server to replicate the Master server, execute the following command. Note: Use the MASTER_HOST IP as your master node IP, MASTER_USER and MASTER_PASSWORD as configured in master node accordingly. Also, MASTER_LOG_FILE and MASTER_LOG_POS values change according to the values we got in the Master MySQL server.

mysql> CHANGE MASTER TO MASTER_HOST ='10.0.2.1', MASTER_USER ='replica_user', MASTER_PASSWORD ='replica_password', MASTER_LOG_FILE = 'mysql-bin.000016', MASTER_LOG_POS = 15634;

Next, start the thread that we had stopped earlier.

mysql> START SLAVE;

This completes our database replication process. To verify the replication follow the below steps.

Step 4: Verify the MySQL Database Replication

To verify the database replication is set up correctly, we will create a new database on the master server and check on the slave server if it's replicated.

Log into MySQL server in the Master node.

$ sudo mysql -u root -p

Create a test database called test_database

mysql> CREATE DATABASE test_database;

Next, log in to MySQL server in Slave node.

$ sudo mysql -u root -p

Now list the databases by executing the following query.

mysql> SHOW DATABASES;

That's it! You will notice that the database that you created on the Master server is replicated on the Slave server. And the Master-Slave database replication is working as expected. So even if the Master node fails for any reason your data will be replicated and safe on the Slave server.