How to set up a MariaDB replication

In this post we explain how to install MariaDB 10.6 on Ubuntu 22.04 and configure a replication using binary log shipping.

Introduction

What is a MariaDB replication?

A replication means that data is written from a source server to a replica server. The source server works the same way as a standalone MariaDB or MySQL server with one slight difference: The changes to databases (such as INSERT, UPDATE, ALTER TABLE, etc.) are logged in so-called binary logs. These logs are then shipped to the replica server(s) where the changes are committed once again. Another possibility is to use a replication based on GTID setup. In this tutorial we're focusing on the more widely used binary log replication.

What are the use cases?

Let's present a couple of examples.

For instance if you have a business-critical application, writing and modifying the databases 24/7, you might run into problems when taking a database backup. You could run into a mysqldump error 1412 (table definition has changed) for example. Taking the backup on the replica server allows to take a consistent backup from the data yet the application can continue to write into the source server.

Another example is if you want to speed up your application by scaling up and using multiple read-only database servers. A replication allows not just one but multiple replica servers. They can all be used as read-only database servers, taking off the load and MySQL connections from the source server.

Setting up a MariaDB replication

For this setup we're using two Ubuntu 22.04 machines in the same internal network:

Source Server: 192.168.55.10

Replica Server: 192.168.55.11

Obviously this also works over Internet with public IP addresses, but you should add firewall rules to restrict MySQL traffic to a minimum.

Install MariaDB

In this tutorial we use the MariaDB packages from the Ubuntu repositories. As an alternative, the MariaDB repositories can also be added as additional APT repositories, which offer the installation of newer MariaDB versions. The MariaDB version on the Ubuntu 22.04 repositories is 10.6.

On both servers install MariaDB:

root@source:~# apt-get install mariadb-server
root@replica:~# apt-get install mariadb-server

Make sure to enable MariaDB so the service will automatically start after a system boot:

root@source:~# systemctl enable mariadb
root@replica:~# systemctl enable mariadb

You should also run mysql_secure_installation and set a root password. Use the same password on both servers.

root@source:~# mysql_secure_installation
root@replica:~# mysql_secure_installation

Configuration on Source Server

We can let the installed configuration files (ending with .cnf) in /etc/mysql/ untouched. It's better to create a new config which is added and read at the end of a MariaDB start.

Use your favourite editor (vim, nano or whatever) to create the new config file:

root@source:~# vi /etc/mysql/mariadb.conf.d/99-replication.cnf

The following configuration options are important/necessary for a replication using log shipping:

• Listener on public interface (default would be localhost)
• Binary Log format set to ROW
• Binary Logs enabled
• Server ID set to a unique number in the replication

This results in the following basic configuration:

root@source:~# cat /etc/mysql/mariadb.conf.d/99-replication.cnf 
[mysqld]
bind-address    = 0.0.0.0
binlog_format = ROW
max_binlog_size = 128M
expire_logs_days = 7
log-error=/var/log/mysql/error.log
log-bin-trust-function-creators = true

# Replication
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log # Only needed on master
#read_only = ON # Only needed on slave

Configuration on Replica Server

Similar to the source server, we create a new cnf config for the replica, with slightly different settings:

root@replica:~# cat /etc/mysql/mariadb.conf.d/99-replication.cnf 
[mysqld]
bind-address    = 0.0.0.0
binlog_format = ROW
max_binlog_size = 128M
expire_logs_days = 7
log-error=/var/log/mysql/error.log
log-bin-trust-function-creators = true

# Replication
server_id = 2
#log_bin = /var/log/mysql/mysql-bin.log # Only needed on master
read_only = ON # Only needed on slave

Restart MariaDB

Restart MariaDB on both servers:

root@source:~# systemctl restart mariadb
root@replica:~# systemctl restart mariadb

Replication User/Privileges on Source Server

On the source server, prepare the replication privileges for a dedicated replication user (repl) with the IP address from the replica server. The repl user requires REPLICATION SLAVE privileges:

root@source:~# mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.55.11' IDENTIFIED BY 'very_secret_password';
Query OK, 0 rows affected (0.001 sec)

Start replication on the Replica Server

On the replica server, start the replication by defining the source server as MASTER, using the credentials of the replication user "repl":

root@replica:~# mysql
MariaDB [(none)]> CHANGE MASTER to MASTER_HOST='192.168.55.10', MASTER_USER='repl', MASTER_PASSWORD='very_secret_password';
Query OK, 0 rows affected (0.018 sec)

Then start the replication process (still on the replica server):

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.001 sec)

You can verify the replication status on the replica server:

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.55.10
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 537
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 836
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 537
               Relay_Log_Space: 1146
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 1
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

The output not only shows the current status of the replication (look at Slave_IO_State, Slave_IO_Running, Slave_SQL_Running) but also at which log position the source and the replica servers are currently at.

Test MariaDB replication

The replication can easily be tested by creating a new database on the source server. Let's verify the current databases on both source and replica servers:

root@source:~# mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+


root@replica:~# mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Now we create a new database "replicationtest" on the source server:

root@source:~# mysql -e "create database replicationtest"
root@source:~# mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replicationtest    |
| sys                |
+--------------------+

Almost immediately with a very small delay, the new database is now also created on the replica server:

root@replica:~# mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replicationtest    |
| sys                |
+--------------------+

Monitor MariaDB replication

As mentioned before, the SQL query SHOW SLAVE STATUS\G; shows all necessary information about the status of a MariaDB replication. This can also be integrated into systems and application monitoring tools such as Nagios or Icinga.

check_mysql_slavestatus is a monitoring plugin doing this job and supports both MySQL and MariaDB replications:

$ ./check_mysql_slavestatus.sh -S /run/mysqld/mysqld.sock -u nagios -p secret -w 60 -c 120
OK: Slave SQL running: Yes Slave IO running: Yes / master: 192.168.55.10 / slave is 0 seconds behind master | delay=0s