Atlantic.Net Blog

How to Setup MariaDB Master Slave Replication on Ubuntu 18.04

Hitesh Jethva
by Atlantic.Net (64posts) under VPS Hosting
0 Comments

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.

Prerequisites

  • Two fresh Ubuntu 18.04 VPS on the Atlantic.Net Cloud Platform.
  • A static root password configured on both servers.

Step 1 – Create an Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server.  Create a new server, choosing Ubuntu 18.04 as the operating system with at least 2GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged into your Ubuntu 18.04 server, run the following command to update your base system with the latest available packages.

apt-get update -y

Step 2 – 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 3 – 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 4 – 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 5 – 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.

Get A Free To Use Cloud VPS

Free Tier Includes:
G2.1GB Cloud VPS Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


Looking for a Hosting Solution?

We Provide Cloud, Dedicated, & Colocation.

  • Seven Global Data Center Locations.
  • Flexible Private, Public, & Hybrid Hosting.
  • 24x7x365 Security, Support, & Monitoring.
Contact Us Now! Med Tech Award FTC
SOC Audit HIPAA Audit HITECH Audit

Recent Posts

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G2.1GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


New York, NY

100 Delawanna Ave, Suite 1

Clifton, NJ 07014

United States

San Francisco, CA

2820 Northwestern Pkwy,

Santa Clara, CA 95051

United States

Dallas, TX

2323 Bryan Street,

Dallas, Texas 75201

United States

Ashburn, VA

1807 Michael Faraday Ct,

Reston, VA 20190

United States

Orlando, FL

440 W Kennedy Blvd, Suite 3

Orlando, FL 32810

United States

Toronto, Canada

20 Pullman Ct, Scarborough,

Ontario M1X 1E4

Canada

London, UK

14 Liverpool Road, Slough,

Berkshire SL1 4QZ

United Kingdom

Resources