MariaDB is a drop in replacement for MySQL because it is a scalable, robust and reliable SQL server that comes rich set of enhancements.

Replication is the process of copying database automatically from master to slave server. If one server goes down, then you can still access the data from second server.

You can easily solve a number of problems with performance and backup using master slave replication. You can also split up the load of database queries across multiple database servers.

In this tutorial, we will learn how to configure MariaDB master slave replication between two Ubuntu 16.04 host.

Requirements

• Two servers running Ubuntu 16.04.
• Static IP address 192.168.15.237 configured on master server.
• Static IP address 192.168.15.100 configured on slave server.
• A non-root user with sudo privileges configure on both server.

Update System

Before starting, you will need to update the system with the latest version. You can do this by runnng the following command:

sudo apt-get update -y
sudo apt-get upgrade -y

Once your system is up to date, you can proceed to next step.

Installing MariaDB

First, you will need to install the latest version of MariaDB database server on both master and slave server.

You can install MariaDB server easily using the following command:

sudo apt-get install mariadb-server

Once MariaDb is installed, you will need to secure it first, because default MariaDB installation is not secure.
You can secure it by running the mysql_secure_installation script on both master and slave server.

sudo mysql_secure_installation

The above script will ask you to set a root password for your MariaDB installation, remove default test database and remove anonymous users.
Answer each questions carefully.

Once MariaDb installation is secured, start MariaDB service and let it to start automatically on every reboot by running the following command:

sudo systemctl start mariadb
sudo systemctl enable mariadb

Configuring MariaDB Master Server

First, you will need to modify my.cnf file located at /etc/mysql/ directory.

sudo nano /etc/mysql/my.cnf

Change the file as shown below:

[mysqld]
bind-address = 192.168.15.237
server_id=1
log-basename=master
log-bin
binlog-format=row
binlog-do-db=<databasename>

Note: replacewith the name of the database you want to replicate.

Save and close the file, then restart the MariaDb server to apply the changes.

`sudo systemctl restart mysql“

Next, you will need to setup the replication.

First, log in to mariadb server with the following command:

mysql -u root -p

The above command will prompt you for root password, so enter it and login.

Then, stop the slave with the following command:

MariaDB [(none)]> STOP SLAVE;

Next, create a new replication user with name slaveuser and password password with the following command:

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'password';

Next, flush privileges.

MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

Next, check master server status with the following command:

MariaDB [(none)]> SHOW MASTER STATUS;

Output:

MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      230 | database     |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> exit

Please note down the file mariadb-bin.000001 and position number 230 from the above output.

Next, you will need to backup all the database on master server and transfer them to slave server.

First, backup all the database with the following command:

sudo mysqldump --all-databases --user=root --password --master-data > backup.sql

After backup is completed, you will need to unlock the tables.

MariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> exit;

Next, copy backup.sql file to slave server with the following command:

sudo scp backup.sql root@192.168.15.100:/home/

Configuring MariaDb Slave Server

On the slave server, you will also need to change my.cnf file:

sudo nano /etc/mysql/my.cnf

Change the file as shown below:

[mysqld]
bind-address = 192.168.15.100
server-id = 2
replicate-do-db=<databasename>

Save the file, when you are done and restart the mariadb service.

systemctl restart mysql

Next, you will need to import the databases that you uploaded to the slave earlier.

sudo mysql -u root -p < backup.sql

Next, login to mysql shell:

mysql -u root -p

Next, stop the slave with the following command:

MariaDB [(none)]> STOP SLAVE;

Next, configure the slave to use the master we setup earlier.

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='<your master's ip>', MASTER_USER='slaveuser', MASTER_PASSWORD='securepassword', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=230;

Then, start the slave:

MariaDB [(none)]> SLAVE START;

Next, you can then view the status of the slave with the following command:

MariaDB [(none)]> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.15.237
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 230
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: <databasename>

Testing Replication

Once everything is setup properlly, it’s time to test replication between master and slave server.

First, go to the master server and login to mysql:

mysql -u root -p

Create a database with name hostpresto and add some tables and entries in it.
Note that the database name should be same as in my.cnf file.

MariaDB [(none)]> create database hostpresto;

MariaDB [(none)]> use hostpresto;
MariaDB [hostpresto]> create table test (c int);
MariaDB [hostpresto]> insert into test (c) values (1);
MariaDB [hostpresto]> select * from test;

+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Next, go to the slave server and check whether the above created entries have been replicated or not.
First, login to mysql:

mysql -u root -p

MariaDB [(none)]> use hostpresto;
MariaDB [hostpresto]> select * from test;

+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

That’s it. You should see that the tables created in the Master server have been automatically replicated to the Slave server.

Conclusion

Congratulations, you have successfully setup master slave replication between two Ubuntu 16.04 hosts. Now, you can easily setup replication of one or more databases.