Skip to main content

How to Set up MariaDB Master-Slave Replication on Debian 8

MariaDB Master-Slave Replication

In this tutorial, I will show you how to set up a simple MariaDB master-slave replication. By simple I mean there’s only one master and one slave and only one instance of replication from a master to a slave. This tutorial is demonstrated on a Debian 8 server with MariaDB 10.1 as the master and on Raspbian Jessie with MariaDB 10.1 as the slave.

MariaDB Master-Slave Replication fundamentals

In a master-slave replication setup, data changes happen on the master server, while slave server automatically replicate the changes from master server. You can change the data on slave server, but the changes will not be replicated to master server.

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

Setting up replication can be done in 4 steps:

  1. configure the master
  2. configure the slave
  3. dump databases on the master and import them into the slave
  4. Connect the slave to the master

So let’s get our hands dirty.

Step1: Configure the Master

Edit my.cnf option file

Open my.cnf configuration file, aka option file, on the master server.

sudo nano /etc/my.cnf

Add these 5 lines in [mysqld] section.

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

The first option log-bin enables binary logging. Binary log is stored under /var/log/mysql/ directory. master-bin is binary log’s base name.

The second option log-bin-index gives the name of the binary log index file.

The third option binlog-format specifies the format of binary logging. Format can be statement-based, row-based and mixed. Mixed logging is a combination of statement and row-based logging and is recommended for replication.  In mixed logging, statement-based logging is used by default, but when MariaDB determines a statement may not be safe for statement-based replication, it will use the row-based format instead.

Server ID is used to distinguish servers from each other.

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

Once you added these 5 options, restart MariaDB server for the changes to take effect.

sudo systemctl restart mariadb

Your my.cnf option file may have an option bind-address = If this is the case, then your MariaDB server listens only on localhost. Since later on the slave must remotely connect to the master, you need to change this option to allow remote login. Comment out this option, save the file and restart MariaDB service.

Add a replication user on the master server

The slave server will use this user to remotely log into master server and request binary logs from master server.

First log into MariaDB server.

mysql -u root -p

Then create a user and grant replication privilege (replication slave) to this user.

CREATE USER replication_user;

set password for replication_user = password("your-password");

grant replication slave on *.* to replication_user identified by 'your-password';

flush privileges;

Step2: Configure the Slave

Open my.cnf option file on the slave server.

sudo nano /etc/my.cnf

Add these 4 options in [mysqld] section.

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

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. Use systemctl status mariadb to check the status.

Step3: Dump Databases On the Master

Log into the master MariaDB server and issue this command to prevent any further changes to databases.

flush tables with read lock;


show master status;
MariaDB Master-Slave Replication

Do NOT exit yet. 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.

mysqldump -u root -p database_name > database_name.sql

Use scp to copy this dump file to your slave server and then import it into the slave MariaDB server. After this is done, you can unlock tables on master server with

 unlock tables;

Step4: Connect the Slave to the Master

Now in the MariaDB slave server, run this command.

MariaDB [(none)]> change master to 
   -> master_host='master IP address',
   -> master_user='replication_user',
   -> master_password='replication_user_password', 
   -> master_port=3306,
   -> master_log_file='mariadb-bin.000012', 
   -> master_log_pos=6622841,
   -> master_connect_retry=10,
   -> master_use_gtid=current_pos;

master_log_file and master_log_pos can be obtained from the above show master status command. The last line master_user_gtid=current_pos; enables GTID (Global Transaction ID) in MariaDB replication. GTID is a feature available starting with MariaDB 10.0.2.

Then start slave.

MariaDB [(none)]> start slave;

Check slave status.

MariaDB [(none)]> show slave 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 No, 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 immediately replicated to the slave database server.

To stop replication:

MariaDB [(none)]> stop slave;

Global Transaction ID

MySQL v5.6 introduced GTID to address some limitations in replication. GTID is available and enabled by default starting from MariaDB 10.0.2. A unique GTID is assigned to each event group logged into binary log. MariaDB server 10.0.2+ can enable and disable GTID without shutting down the database server. This is not the case with MySQL’s implementation of GTID.

Conclusion Remarks

MariaDB master slave replication can help with high availability and scalability, but it can not prevent data loss. A careless drop database command on the master will be replicated to the slave. So you still need to back up your database regularly.

One benefit of MariaDB master-slave replication is that you can use mysqldump to backup the database on the slave server without affecting the performance of the master server or affecting the write operations on the master.

Rate this tutorial
[Total: 0 Average: 0]