How to Set Up MariaDB Master-Slave Replication on Ubuntu 18.04, 20.04

This tutorial will be showing you how to set up MariaDB master-slave replication on Ubuntu 18.04/20.04 server. MariaDB is a mature, stable, open-source relational database forked from MySQL. Replication is a feature in MariaDB that allows data on one server to be mirrored on another server. There’re several types of replication in MariaDB, including:

• Master-slave replication
• Master-master replication
• Multi-source replication
• Circular replication

Master-slave replication is a type of replication in which data is replicated one-way only. A server replicating data from another server is called a slave, while the other server is called the master. Data changes happen on the master server, while the slave server automatically replicates the changes from master server. You can change the data on slave server, but the changes will not be replicated to master server.

Master-slave replication can be used for several purposes.

• Scalability: Write operations can be performed on the master, while read operations can be spread across two or more slave servers, improving performance.
• High availability: Increase data redundancy for improving fault tolerance.
• Dedicated Backup: You can have multiple slave servers and one of them can be used for backup and backup only. No queries are sent to this dedicated backup slave server. The workload on the master and remaining slaves will not be interrupted when a backup is created.

How Master-Slave Replication Works in MariaDB

MariaDB replication is based on the binary log (binlog). The purpose of binary log is to allow replication, backup and restore databases. You must enable binary logging on the master server in order for replication to work. Binary logging is not required on the slave server, but it’s recommended.

For replication to work, the master and slave server must have the same data at first. Then changes to the data (aka transactions) happen on the master server. The master assigns every transaction a global transaction ID (GTID) and writes the transaction to its binary log. A slave will periodically check the binary log on the master. If there are new transactions, the slave will write the new transaction to its own relay log and execute the transaction.

The master-slave replication in MariaDB is called asynchronous replication, which means that the master executes transactions immediately and does not wait for any of the slaves to replicate the transaction, so it does not affect write performance. In asynchronous replication, there’s no need for a permanent connection between the slaves and the master.

The opposite of asynchronous replication is synchronous replication, which is often used in multi-master cluster. With synchronous replication, a transaction is executed after its changes have been replicated to every node in the cluster.

Setting up master-slave replication can be done in 5 steps:

1. Enable binary log and replication on the master
2. Enable relay log and replication on the slave
3. Dump databases on the master and import them into the slave
4. (optional) Enable TLS encryption
5. Connect the slave to the master

Prerequisites For MariaDB Master-Slave Replication on Ubuntu 18.04, 20.04

Ideally, the master and slaves should use the same MariaDB version. Replication between different MariaDB versions may cause problems. If you run MariaDB database server on Ubuntu 18.04, then you should either install MariaDB from Ubuntu repository on all the servers, or install the latest MariaDB version from MariaDB.org repository on all the servers.

Note: There are two ways you can log into MariaDB monitor. First, there’s the old-fashioned method:

mysql -u root -p

The second is prefixing the mysql command with sudo, which allows you to log in without having to provide the MariaDB root password.

sudo mysql -u root

I configured my MariaDB server to use the second method, so use your preferred method. With that out of the way, let’s do it.

Step 1: Enable Binary Log and Replication on the Master

By default, binary log is disabled. To enable it, open the MariaDB main configuration file. Normally, it’s called my.cnf. But on Ubuntu, the file is named 50-server.cnf.

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

Find the Logging and Replication section in [mysqld] unit and Add these 5 lines.

log-bin         = /var/log/mysql/master-bin
log-bin-index   = /var/log/mysql/master-bin.index
binlog_format   = mixed
server-id       = 01
replicate-do-db = database_name

Binary log is comprised of log files and and index file. The first line enables binary logging. Binary log is stored under /var/log/mysql/ directory. master-bin is binary log’s base name. log-bin-index in the second line gives the name of the binary log index file.

The binlog-format in the third line specifies the format of binary logs. Format can be statement-based, row-based and mixed. Mixed logging is a combination of statement and row-based logging. It’s the default and is recommended for replication. In mixed logging, statement-based logging is used whenever possible, but if MariaDB determines a statement may not be safe for statement-based replication, it will use the row-based format instead.

Note:  1) The MariaDB package from Ubuntu repository by default uses STATEMENT format for binary log. You can obtain the currently used format by logging into MariaDB monitor and run show variables like "binlog_format";.  2) While the binlog events are stored in binary format, mysqlbinlog can display them as text.

Server ID is used to distinguish servers from each other. All servers in a replication group must have unique server IDs.

replicate-do-db option is used to specify which database will be replicated to slave server. Obviously you need to replace database_name with your actual database name. If you want to replicate multiple databases, then you need to add multiple replicate-do-db entries like below:

replicate-do-db = db1
replicate-do-db = db2

It’s not recommended to replicate the system databases (mysql, information_schema, performance_schema).

Next, find the following line in [mysqld] unit.

bind-address = 127.0.0.1

This above line makes MariaDB server listen only on localhost only. Later on, the slave must remotely connect to the master, you need to comment out the above line to allow remote login. Save the file and restart MariaDB service for the changes to take effect.

sudo systemctl restart mariadb

or

sudo systemctl restart mysql

When the master sits on the public Internet, it’s recommended to restrict access to port 3306 (default MariaDB port). For example, you can use UFW to create an IP address whitelist, allowing only the slave’s IP addresses to connect to port 3306. For how to use UFW, please see the following article:

• Getting started with UFW firwall on Debian, Ubuntu, Linux Mint server

After that, we need to add an replication user on the master server. The slave server will use this user to remotely log into master server and request binary logs. Log into MariaDB monitor.

sudo mysql -u root

Then create a user and grant replication slave privilege to this user. Replace the red text with your preferred username and password.

create user 'replicant'@'%' identified by 'replicant_password';

grant replication slave on *.* to replicant;

If the slave is going to connect to the master over the public Internet, it’s a good practice to enforce TLS encryption.

grant replication slave on *.* to replicant require ssl;

Then flush the privilege table.

flush privileges;

Don’t exit MariaDB monitor now.

Step 2: Enable Relay Log and Replication on the Slave

Open the 50-server.cnf file on the slave.

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

Find the Logging and Replication section in [mysqld] unit and Add these 4 lines.

server-id       = 02
relay-log-index = /var/log/mysql/slave-relay-bin.index
relay-log       = /var/log/mysql/slave-relay-bin
replicate-do-db = database_name

The database_name should be the same as the the database name on the master server. In the above configuration, we didn’t enable binary logging, which is fine if the slave is only used for load balancing. You need to enable binary logging if the slave will also act as a master of another slave, or if the slave will be used for backup. To enable binary logging, add the following 3 lines.

log-bin       = /var/log/mysql/02-bin
log-bin-index = /var/log/mysql/02-bin.index
binlog_format = mixed

If the slave is going to act a master of another slave, add the following line as well.

log_slave_updates = ON

Save and close the file. Then restart the slave MariaDB server for the changes to take effect.

sudo systemctl restart mariadb

Sometimes MariaDB may fail to restart. Run systemctl status mariadb to check the status.

Step 3: Copy Database From the Master to the Slave

On the master MariaDB monitor, run the following command to prevent any further changes to databases.

flush tables with read lock;

Note: If the master server is on a live website, the above command can cause your website go offline. Create a clone of your website on another server to prevent downtime.

Then obtain the binary log coordinates with the following command. The coordinates are the name of the binary log file currently in use, and the position of the last written event.

show master status;

Do NOT exit MariaDB monitor yet, because exiting it now will release the lock. Record File and Position details. Now open another terminal window and SSH into your master server. Use mysqldump utility to dump the database to a .sql file.

sudo mysqldump -u root database_name > database_name.sql

Your can obtain the database name by running the following command at the MariaDB monitor.

show databases;

After the database is dumped to disk, you can unlock tables on master server by running the following command at the MariaDB monitor.

unlock tables;

Use the scp command or whatever method you prefer to copy this SQL file to your slave server. Then log into the slave MariaDB monitor.

sudo mysql -u root

Create a blank database with the same name.

create database database_name;

Exit MariaDB monitor.

exit;

Import the database into the slave MariaDB server with the following command.

sudo mysql -u root database_name < database_name.sql

To keep data consistent with the master, it is advisable to enable read-only mode on the slave. Replication will work as usual in read-only mode. Open the 50-server.cnf file on the slave.

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

Add the following line in [mysqld] unit to enable read-only mode.

read-only = 1

Users with SUPER privilege (like root) can still write to the database, so you should be careful when granting privileges to users. If you don’t want anyone to be able to change/delete the database, you can add the following line in [mysqld] unit.

innodb-read-only = 1

Save and close the file. Then restart MariaDB for the change to take effect.

sudo systemctl restart mariadb

Step4: Setting Up TLS Encryption

Note: If the slave and master are in a private network, your don’t have to do this step. Skip to step 5.

If the slave is going to connect to the master over the public Internet, it is necessary to enable TLS encryption to prevent traffic snooping. Your server may have a web server with TLS enabled, so you can use that TLS certificate for MariaDB as well. For example, I have enabled TLS in my Nginx web server with Let’s Encrypt. To enable TLS in MariaDB, open the 50-server.cnf file.

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

Find the Security Features section in [mysqld] unit. Add the following lines:

ssl-ca = /etc/letsencrypt/live/www.linuxbabe.com/chain.pem
ssl-cert = /etc/letsencrypt/live/www.linuxbabe.com/cert.pem
ssl-key = /etc/letsencrypt/live/www.linuxbabe.com/privkey.pem

Save and close the file. The mysql user needs permission to access the above SSL files, so you need to grant read permission with the following commands.

sudo setfacl -R -m "u:mysql:rx" /etc/letsencrypt/archive/

sudo setfacl -R -m "u:mysql:rx" /etc/letsencrypt/live/

Then restart MariaDB for the changes to take effect.

sudo systemctl restart mariadb

Your may be wondering why you don’t need to grant the www-data user read permission of the SSL files. That’s because Apache or Nginx has a master process running as root user. However, all MariaDB processes are running as mysql user.

After MariaDB restarts, log into MariaDB monitor and run the following command to check if SSL is successfully enabled.

show global variables like "have_ssl";

If the value is “Yes”, then SSL is enabled.

If the value is “DISABLED”, that means there are something wrong in your SSL configuration. Check the MariaDB error log (/var/log/mysql/error.log) to find the reason.

The MariaDB server binary from Debian/Ubuntu repository is statically linked with MariaDB’s bundled YaSSL library. The MariaDB binary from the MariaDB.org repository is dynamically linked with the system’s TLS library, usually OpenSSL. You can log into MariaDB monitor and run the following command to check which SSL library your MariaDB server is using.

show variables like "version_ssl_library";

If you would like to use TLS 1.3 in MariaDB, then you need to install MariaDB from MariaDB.org repository and install OpenSSL 1.1.1 on your Ubuntu system.

You can test TLS connection by logging in from the slave with the following command. --ssl option enforces secure connection.

mysql -h Master_IP_Address -u replicant -p --ssl

Once you are logged in, run

status;

In the output, you can see the SSL cipher in use.

Step 5: Connect the Slave to the Master

Now log into the slave MariaDB monitor and run the following command to create a connection profile.

MariaDB [(none)]> change master 'master01' to
    -> master_host='master_IP_address',
    -> master_user='replicant',
    -> master_password='replicant_password',
    -> master_port=3306,
    -> master_log_file='master-bin.000001',
    -> master_log_pos=62307428,
    -> master_connect_retry=10,
    -> master_use_gtid=slave_pos,
    -> master_ssl=1;

In the first line, the connection name is set to master01. The value of master_log_file and master_log_pos can be obtained from the show master status command on the master MariaDB server. master_use_gtid=slave_pos enables GTID (Global Transaction ID) in MariaDB replication. GTID is a feature available starting with MariaDB 10.0.2.

The last line enforces TLS encryption. If the master and slave are in a secure private network, then you don’t have to enforce TLS encryption, so you can remove the last line. Notice that the last line ends with a semicolon.

Then start this connection.

MariaDB [(none)]> start slave 'master01';

Check the status.

MariaDB [(none)]> show slave 'master01' status\G;

If you see no errors in the output, that means replication is running smoothly. You should see the following two “Yes” indicating everything is going well. If one of them is not “Yes”, then something is not right.

 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

Now if you make a change in your master database server, it will be replicated to the slave database server. The slave I/O thread connects to the master and requests binary log. If there are new transactions in the binary log, the slave I/O thread writes them to the relay log on the slave server. Then the slave SQL thread reads the relay log and executes new transactions in the database.

To stop replication, run:

MariaDB [(none)]> stop slave 'master01';

If you want the replication to restart from a clean state, you can reset the replication.

MariaDB [(none)]> reset slave 'master01';

By default, when MariaDB server restarts, it resumes all stopped replication tasks. To permanently delete a connection, first stop that connection and run the following statement.

MariaDB [(none)]> reset slave 'master01' all;

TroubleShooting

The first time you check the slave status, you might see the following error.

Last_SQL_Error: Error 'Duplicate entry

This usually happens when the slave connects to the master the first time. If an error occurs, the replication will stop. We can skip this duplicate entry error by adding the following two line in [mysqld] unit in the MariaDB server configuration file (50-server.conf).

slave-skip-errors=1062
skip-slave-start

Restart MariaDB. Then start the slave replication again.

MariaDB [(none)]> start slave 'master01';

Check the status.

MariaDB [(none)]> show slave 'master01' status\G;

After a few moments, the Seconds_Behind_Master in the status output will get to zero. After that, you can remove the two lines in [mysqld] and restart MariaDB.

Check if Replication is Working

First, change some data on the master, then run the following command on the master MariaDB monitor.

MariaDB [(none)]> show variables like 'gtid_binlog_pos';

Next, on the slave MariaDB monitor, run the following command.

MariaDB [(none)]> show variables like 'gtid_slave_pos';

If the two values are the same, then data changes on the master is replicated to the slave.

How to Backup Database on Slave Server

Note that replication is not a replacement for backup. Yes, there are multiple copies of the data on different servers, but if a DROP DATABASE command is accidentally run on the master server, all slave servers will drop the database. You can set up multiple slave slavers and dedicate one of them to be used for backup, so the workload on the master and remaining slaves will not be interrupted when a backup is created. For how to backup and restore MariaDB database, please see the following article:

• How to Back Up and Restore MariaDB Databases From the Command line

Before taking backups on the slave, be sure to stop replication.

Conclusion

I hope this tutorial helped you set up MariaDB master-slave replication on Ubuntu 18.04 or 20.04 server. As always, if you found this post useful, then subscribe to our free newsletter to get more tips and tricks. Take care 🙂