How to Setup MariaDB Master Slave Replication on Ubuntu 18.04

MariaDB is a free, open-source relational database management system, and is one of the most popular options. Replication is the process of copying data from one master database server to another slave database server. In the master-slave topology, one server acts as the master and other servers act as slaves. In master-slave replication, data is replicated one-way only. If you change the data on the slave server, it will not be replicated to the master server. If the master server goes down, you can still recover your data from the slave server.

You can use MariaDB master-slave replication for several purposes; some of these are listed below:

• Scalability and High Availability
• Backups
• Analytics
• Load Balancing
• Increasing Database Performance
• Failover alleviating
• Data security

In this tutorial, we will show you how to set up MariaDB master-slave replication on Ubuntu 18.04 server.

Step 1 – Install MariaDB on Both Servers

First, you will need to install MariaDB on both servers. You can install it by running the following command:

apt-get install mariadb-server mariadb-client -y

After installing MariaDB, run the following command to secure the MariaDB installation:

mysql_secure_installation

Answer all the questions as shown below:

Enter current password for root (enter for none): Press Enter
Set root password? [Y/n] Y
New password:
Re-enter new password:
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

At this point, you have installed and secured both MariaDB servers.

Step 2 – Configure Master Server

Next, you will need to enable binary logging and replication on the master server. To do so, open the file /etc/mysql/mariadb.conf.d/50-server.cnf with your preferred text editor:

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Find the line bind-address and change the value to 0.0.0.0 to allow inbound connections.

bind-address            = 0.0.0.0

Next, add the following lines at the end of the file:

server-id = 1
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 file when you are finished. Then, restart the MariaDB service to implement the changes:

systemctl restart mariadb

Next, you will need to create a replication user. The slave server will use this user to log into the master server and request binary logs.

First, log in to MariaDB shell with the following command:

mysql -u root -p

Provide your root password when prompted, then create a user with the following command:

CREATE USER 'replication'@'%' identified by 'your-password';

Next, grant the replication slave privilege to this user with the following command:

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

Next, flush the privileges with the following command:

FLUSH PRIVILEGES;

Next, check the master server status with the following command:

show master status;

You should get the following output:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      313 |              |                  |
+------------------+----------+--------------+------------------+

Next, exit from the MariaDB shell with the following command:

EXIT;

Note: Please remember the File and Position details from the above output. You will need these values when configuring the slave server.

Step 3 – Configure Slave Server

Next, log in to the slave server and open the file /etc/mysql/mariadb.conf.d/50-server.cnf:

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Find the line bind-address and change the value to 0.0.0.0 as shown below:

bind-address            = 0.0.0.0

Next, add the following lines at the end of the file:

server-id = 2
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 file when you are finished. Then, restart the MariaDB service to implement the changes:

systemctl restart mariadb

Next, log in to the MariaDB shell with the following command:

mysql -u root -p

Provide your root password when prompted, then stop the slave threads as shown below:

stop slave;

Next, run the following command to set up the slave to replicate the master:

CHANGE MASTER TO MASTER_HOST = 'your-master-host-ip', MASTER_USER = 'replication', MASTER_PASSWORD = 'your-password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 313;

Next, start the slave threads and exit from the MariaDB shell as shown below:

start slave;
exit;

Note: Amend the above command to use your master host IP address and your password which you created in previous steps. Please use the log file name and position values that you obtained from the master server.

Step 4 – Test Database Replication

At this point, you have configured master-slave replication. Now, it’s time to test replication between master to slave.

On the master server, log in to the MariaDB shell with the following command:

mysql -u root -p

Provide your root password when prompted, then create a database with name mydb as shown below:

create database mydb;

Next, create a table with name “products” inside the mydb database:

use mydb;
CREATE TABLE products(product_id INT NOT NULL AUTO_INCREMENT,product_name VARCHAR(100) NOT NULL,product_manufacturer VARCHAR(40) NOT NULL,submission_date DATE,PRIMARY KEY ( product_id ));

Next, run the following command to check the table:

SHOW TABLES;

Output:

+----------------+
| Tables_in_mydb |
+----------------+
| products       |
+----------------+

On the slave server, log in to the MariaDB shell with the following command:

mysql -u root -p

Provide your root password when prompt then check the slave status:

SHOW SLAVE STATUS \G

You should see the following output:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: your-master-host-ip
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 721
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 945
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Next, run the following command to check whether the database is replicated:

show databases;

You should see the mydb database that you created on the master server:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
+--------------------+

Next, check the product table that you created on the master server:

MariaDB [(none)]> use mydb;
MariaDB [mydb]> show tables;

You should see the product table in the following output:

+----------------+
| Tables_in_mydb |
+----------------+
| products       |
+----------------+

Congratulations! Your MariaDB master-slave replication is working as expected. You can now easily recover your database in the event of a failure. Get started with MariaDB master-slave replication today with a VPS Hosting package from Atlantic.Net.