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 ４ steps:
- configure the master
- configure the slave
- dump databases on the master and import them into the slave
- Connect the slave to the master
So let’s get our hands dirty.
Step1: Configure the Master
Edit my.cnf option file
my.cnf configuration file, aka option file, on the master server.
sudo nano /etc/my.cnf
Add these 5 lines in
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
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 = 127.0.0.1. 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
my.cnf option file on the slave server.
sudo nano /etc/my.cnf
Add these ４ options in
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;
Do NOT exit yet. Record
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
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
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.
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.