How to Set up MariaDB Master-Slave Replication on Debian 8
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
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 = 01
replicate-do-db = database_name
Binary log is comprised of log files and and index file. 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.
Hint: 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
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. Later on the slave must remotely connect to the master, you need to comment out this option to allow remote login. Save the file and restart MariaDB service for the changes to take effect.
sudo systemctl restart mariadb
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. This will enable the relay log and replication.
server-id = 02
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. Run 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;
Then
show master status;
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
You can now unlock tables on master server by running the following command at the MariaDB monitor.
unlock tables;
Use scp to copy this dump file to your slave server. Then import it into the slave server.
mysql -u root -p database_name.sql < database_name
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 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;
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
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.
