How to Configure MySQL (MariaDB) Master-Slave Replication on Debian 10

MySQL replication is a process of copying data from one database server (master) to one or more servers (slaves).

MySQL supports several replication topologies with Master/Slave topology being one of the most well-known topologies in which one database server acts as the master, while one or more servers act as slaves. By default, the replication is asynchronous where the master sends events that describe database modifications to its binary log and slaves request the events when they are ready.

In this guide, we will show you how to set up MariaDB Master/Slave replication with one master and one slave server on Debian 10. MariaDB is the default implementation of MySQL in Debian. The same steps apply for Oracle MySQL.

This type of replication topology is best suited for deploying of read replicas for read scaling, live databases backup for disaster recovery, and for analytics jobs.

Prerequisites #

We’re assuming that you have two servers running Debian 10, communicating with each other over a private network. If your hosting provider doesn’t support private IP addresses, you can use the public IP addresses and configure your firewall to allow traffic on port 3306 only from trusted sources.

The servers used in this example have the following IP addresses:

Master IP: 10.10.8.12
Slave IP:  10.10.8.164

Installing MariaDB #

The default The Debian 10 repositories includes MariaDB version 10.3. It is best to install the same MariaDB version
on both servers to avoid any potential issues.

Install MariaDB
on both the master and the slave by issuing the following commands:

sudo apt-get updatesudo apt-get install mariadb-server

Configuring the Master Server #

The first step is to set up the master server. We’ll make the following changes:

Open the MariaDB configuration file and uncomment or set the following lines:

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

master:/etc/mysql/mariadb.conf.d/50-server.cnf

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

Once done, save the file and restart the MySQL service for changes to take effect:

sudo systemctl restart mariadb

The next step is to create a new replication user. Log in to the MariaDB server as the root user:

sudo mysql

Run the following SQL queries to create a user named replica and grant the REPLICATION SLAVE privilege to the user:

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

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'10.10.8.164';

While still inside the MySQL prompt, execute the following command that will print the binary filename and position.

SHOW MASTER STATUSG

*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 328
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.001 sec)

Take note of file name, ‘mysql-bin.000001’ and Position ‘328’. These values are necessary when configuring the slave server and will probably be different on your server.

Configuring the Slave Server #

We’ll make the same changes on the slave server as those on the master:

• Set the MySQL server to listen on the private IP.
• Set a unique server ID.
• Enable binary logging.

Open the MariaDB configuration file and edit the following lines:

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

slave:/etc/mysql/mariadb.conf.d/50-server.cnf

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

Restart the MariaDB service:

sudo systemctl restart mariadb

The next step is to configure the parameters that the slave server will use to connect to the master server. Login to the MariaDB shell:

sudo mysql

Start by stopping the slave threads:

STOP SLAVE;

Run the following query to configure the Master/Slave replication:

CHANGE MASTER TOMASTER_HOST='10.10.8.12',MASTER_USER='replica',MASTER_PASSWORD='replica_password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=328;

Make sure you are using the correct IP address, user name, and password. The log file name and position must be the same as the values you obtained from the master server.

Once done, start the slave threads.

START SLAVE;

Test the Configuration #

At this point, you should have a working Master/Slave replication setup.

To verify that everything is setup up correctly, create a new database on the master server:

sudo mysql

CREATE DATABASE replicatest;

Login to the slave MySQL shell:

sudo mysql

Run the following command to list all databases
:

SHOW DATABASES;

You will notice that the database you created on the master server is replicated on the slave:

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

Conclusion #

In this tutorial, we have shown you create a MariaDB Master/Slave replication on Debian 10.

Feel free to leave a comment if you have any questions.