How to Setup MariaDB (Master-Slave) Replication in CentOS/RHEL 7 and Debian 8/9

Even when some IT people hear the phrase “Database replication“, they often associate it with the need of having multiple copies of the same information to avoid data loss in the event of hardware failure or data corruption. While that is true at some extent, there is much more to database replication than the common concept of backing up a database and data availability.

MariaDB Master-Slave Replication in CentOS
MariaDB Master-Slave Replication in CentOS/RHEL 7 and Debian 8/9

Among other benefits of database replication in a master-slave setup we can mention:

  1. Backups can be done on the slave server without affecting (and being affected by) the write operations in the master.
  2. Resource-intensive operations (such as data analysis) can be performed on the slave without influencing the performance of the master.

In this article we will explain how to set up master-slave replication in MariaDB 10.1. As opposed to classic replication, MariaDB introduced the concept of Global Transaction IDs (GTIDs) in v10.0, which allows to change a slave to connect to and replicate from a different master easily. On top of that, the state of the slave is recorded in a crash-safe way (updates to the state are done in the same transaction as the updates to the data).

If you’re looking for MySQL replication under CentOS/RHEL 6, follow this guide Setup MySQL (Master-Slave) Replication on CentOS/RHEL 6

Installing MariaDB 10.1 in CentOS/RHEL 7 and Debian 8/9

Our testing environment consists of the following machines (both are CentOS 7):

Master: 192.168.0.18
Slave: 192.168.0.19

To install the latest version of MariaDB, we will need to add their repositories to our servers. If you’re using older version of MariaDB say 5.5, consider upgrading to latest 10.1 version using below article.

  1. Upgrde MariaDB 5.5 to MariaDB 10.1

In CentOS/RHEL

Create a file named MariaDB.repo inside /etc/yum.repos.d with the following contents on both Master and Slave systems:

# MariaDB 10.1 CentOS repository list - created 2016-01-23 14:16 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Save the file and install MariaDB on both servers using yum:

# yum update && yum install MariaDB-server MariaDB-client

In Debian/Ubuntu

Add the key to authenticate packages and the MariaDB repository:

# apt-get install software-properties-common
# apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
# add-apt-repository 'deb [arch=amd64,i386] http://mirror.edatel.net.co/mariadb/repo/10.1/debian jessie main'

Note: Replace distribution name and codename in the above highlighted line.

Install using apt-get command:

# apt-get update
# apt-get install mariadb-server
Check MariaDB Version on Master Slave-Server
Check MariaDB Version on Master Slave-Server

Once you have installed MariaDB, run the mysql_secure_installation procedure on both master and slave, let’s set up a sample test database in the master machine.

Setting Up a Sample MySQL Database on Master

We will now set up in the master server the Employees database from https://github.com/datacharmer/test_db (which provides a dataset of 4 million records spread over six tables) in two simple steps:

Clone the repository and use it to import the sample database to your MariaDB installation:

# git clone https://github.com/datacharmer/test_db
# cd test_db
# mysql < employees.sql

Configuring MySQL Server on Master

To configure the master, follow these steps:

STEP 1: Edit the /etc/my.cnf file. Under the [mysqld] section, add the following four lines:

log-bin
server_id=1
replicate-do-db=employees
bind-address=192.168.0.18

and restart MariaDB:

# systemctl restart mariadb

STEP 2: Log on to the MariaDB server as root, create the user slave and assign the necessary grants:

MariaDB [(none)]> CREATE USER 'slave'@'localhost' IDENTIFIED BY 'SlavePassword';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]> SHOW MASTER STATUS;

The last command (SHOW MASTER STATUS) returns the current position in the binary log (the exact coordinates that indicate at exactly which point the slave should start replicating from:

Configure MySQL Master for Replication
Configure MySQL Master for Replication

STEP 3: Exit the MariaDB prompt (with exit;) and use the following command to take a snapshot of the employees database. When you hit Enter, you will be prompted to type the password for root you set up earlier through mysql_secure_installation:

# mysqldump -u root -p employees > employees-dump.sql

After the dump is completed, connect to the database server again to unlock the tables and then exit:

MariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> exit;

STEP 4: Copy the dump to the slave:

# scp employees-dump.sql [email protected]:/root/ 

STEP 5: Run the mysql_upgrade procedure to upgrade the system tables (you will be prompted to enter the MariaDB root password):

# mysql_upgrade -u root -p

STEP 6: Allow the database service through the firewall:

# firewall-cmd --add-service=mysql
# firewall-cmd --add-service=mysql --permanent
# firewall-cmd --reload

Now let’s configure the slave.

Configuring MySQL Server on Slave

To configure the slave, follow these steps:

STEP 1: Create the account to perform the replication tasks. Connect to the local MariaDB server with:

# mysql -u root –p

and enter the password you set up earlier.

STEP 2: Once connected to the database server, create the user and an empty database, and grant permissions:

MariaDB [(none)]> CREATE DATABASE employees;
MariaDB [(none)]> GRANT ALL PRIVILEGES ON employees.* TO 'slave'@'localhost' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;

STEP 3: Exit the MariaDB prompt and load the dump created in the master server:

# mysql -u root -p employees < employees-dump.sql

STEP 4: Edit the /etc/my.cnf file to assign a server ID to the slave under the [mysqld] section. Note that it needs to be a different integer than 1, as we used 1 in the master:

server_id=2
replicate-do-db=employees

Restart the database server:

# systemctl restart mariadb

STEP 5: Run the mysql_upgrade procedure to upgrade the system tables (you will be prompted to enter the MariaDB root password):

# mysql_upgrade -u root -p

STEP 6: Once the dump has been imported to the slave, we are only a few steps to begin replicating. Log on to the database and run the following commands in the MariaDB prompt. Pay special attention to the MASTER_LOG_FILE and MASTER_LOG_POS variables, which should match the values returned by SHOW MASTER STATUS in STEP 2 of “Configuring the master” above.

MariaDB [(none)]> CHANGE MASTER TO
  MASTER_HOST='192.168.0.18',
  MASTER_USER='slave',
  MASTER_PASSWORD='SlavePassword',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master-bin.000001',
  MASTER_LOG_POS=314,
  MASTER_CONNECT_RETRY=10,
  MASTER_USE_GTID=current_pos;

STEP 7: Start the slave and check its status without exiting the MariaDB prompt:

MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;

Not that you need this now, but note that you can stop the slave with:

MariaDB [(none)]> STOP SLAVE;

if the SHOW SLAVE STATUS\G; command returns any errors. Use those errors to troubleshoot and then run START SLAVE; to test again.

Test MySQL/MariaDB Database Replication

Let’s add a record to the employees table in the master server:

MariaDB [(none)]> INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (500000, '1983-07-12', 'Dave', 'Null', 'M', '2014-12-12');

Then verify that this change was replicated in the slave:

MariaDB [(none)]> USE employees;
MariaDB [(none)]> SELECT * FROM employees WHERE emp_no=500000;
Check MariaDB MySQL Database Replication
Check MariaDB MySQL Database Replication

As you can see, replication is working correctly from master to slave.

Summary

In this article we have explained how to install the latest version of MariaDB in CentOS/RHEL 7 and Debian 8/9, and discussed how to set up master-slave replication with GTIDs. For more information, you may want to refer to the MariaDB Replication Guide, and don’t hesitate to contact us using the form below if you have questions or comments.

Hey TecMint readers,

Exciting news! Every month, our top blog commenters will have the chance to win fantastic rewards, like free Linux eBooks such as RHCE, RHCSA, LFCS, Learn Linux, and Awk, each worth $20!

Learn more about the contest and stand a chance to win by sharing your thoughts below!

Gabriel Cánepa
Gabriel Cánepa is a GNU/Linux sysadmin and web developer from Villa Mercedes, San Luis, Argentina. He works for a worldwide leading consumer product company and takes great pleasure in using FOSS tools to increase productivity in all areas of his daily work.

Each tutorial at TecMint is created by a team of experienced Linux system administrators so that it meets our high-quality standards.

Join the TecMint Weekly Newsletter (More Than 156,129 Linux Enthusiasts Have Subscribed)
Was this article helpful? Please add a comment or buy me a coffee to show your appreciation.

31 Comments

Leave a Reply
  1. I found it a bit daring to write this guide if you obviously don’t fully understand what you are doing. On the plus side, the commands are correct, but many are redundant or unnecessary as others pointed out as well.

    GTID is an alternative to the file and log position. And mixing them makes no sense since the latter will be obviously ignored. Just as an example.

    Reply
  2. In mariadb version 10.4 this does not work log-bin
    server_id = 1
    replicate-do-db = employees
    bind-address = 192.168.0.18

    of mysql_upgrade errors: unknown option ‘–log-bin’ Reply

  3. Slave:

    In step 2, below command is not required.
    MariaDB [(none)]> GRANT ALL PRIVILEGES ON employees.* TO 'slave'@'localhost' WITH GRANT OPTION;

    Reply
  4. Hi,

    When i tried to install MariaDb showed error message that /boot is full . Why?

    Are there specific requirement in partitioning side?

    Reply
  5. You write that on master: CREATE USER 'slave'@'localhost' IDENTIFIED BY 'SlavePassword';

    Shouldn’t this be: slave CREATE USER 'slave'@'192.168.0.19' IDENTIFIED BY 'SlavePassword';?

    Slave will not log in from localhost, but rather, from the slave server.

    Reply
  6. ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

    I got this error after i hit # mysql < employees.sql this command to import sample database.

    Reply
  7. Very nice post! I have one question. Why do you run mysql_upgrade? The documentation states that this command should be run after upgrading mariadb. So I am wondering why you use it when setting up replication on a fresh installation?

    Reply
  8. Hi Gabriel,

    Great post!!! I have a noob question:

    How can i recover my master from my slave? For example in a disaster recovery where i lost my master DB, only i have to do a dump in the slave and recover the db in master?

    Reply
  9. I have gone through so many guides, but this one is by far the best. Clear, concise and accurate.

    I will be implementing Zabbix/MariaDB in my production environment soon, planning on using pacemaker for Zabbix HA and master-slave replication for MariaDB. This will help immensely. Thank you so much!

    Reply
  10. Beautiful instructions. Worked like a charm on Ubuntu 14.04. Very simple and to the point. This is a rare commodity in Linux ‘How-To’s’
    Thank you VERY much!

    Reply
  11. Hello,
    i have problem with replication. When i add bind-address MY_IP _ADDRESS then my web apps on mysql not work… but when bind-address is 127.0.0.1, then apps work but not work replication….

    Reply

Got Something to Say? Join the Discussion...

Thank you for taking the time to share your thoughts with us. We appreciate your decision to leave a comment and value your contribution to the discussion. It's important to note that we moderate all comments in accordance with our comment policy to ensure a respectful and constructive conversation.

Rest assured that your email address will remain private and will not be published or shared with anyone. We prioritize the privacy and security of our users.