How to configure MariaDB (Master-Slave) Replication in CentOS/RHEL 7

To configure MariaDB (Master-Slave) Replication in CentOS/RHEL 7

The term Database replication is often associated with the need of having multiple copies of the same data to avoid data loss in the circumstances of data corruption. Though it is true to a certain level, there is much more related to database replication than the general concept of backing up a database and also data availability. Procedures to setup MariaDB Replication in CentOS/RHEL 7 and Debian 8 is explained in this article in detail.

Testing environment

Master: 192.168.5.88
Slave : 192.168.5.89

To set up Master Server

Install the mariadb package on the Master server.

[root@linuxhelp ~]# yum install mariadb-server mariadb -y
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: centos.excellmedia.net
 * extras: centos.excellmedia.net
 * updates: centos.excellmedia.net
Resolving Dependencies
-->  Running transaction check
.
.
.
perl-Data-Dumper.x86_64 0:2.145-3.el7           perl-IO-Compress.noarch 0:2.061-2.el7    
  perl-Net-Daemon.noarch 0:0.48-5.el7              perl-PlRPC.noarch 0:0.2020-14.el7              

Dependency Updated:
  mariadb-libs.x86_64 1:5.5.47-1.el7_2                                                                                                      

Complete!

Now restart and enable the services after installing the packages.

[root@linuxhelp ~]# systemctl restart mariadb
[root@linuxhelp ~]# systemctl enable mariadb
ln -s ' /usr/lib/systemd/system/mariadb.service'  ' /etc/systemd/system/multi-user.target.wants/mariadb.service' 

Set the root password, so we can able to log in to the database as a root user. Run the script “ mysql_secure_installtion” and follow the instructions to set the root password as follows.

[root@linuxhelp ~]# mysql_secure_installation 
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we' ll need the current
password for the root user. If you' ve just installed MariaDB, and
you haven' t set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from ' localhost' . This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MariaDB comes with a database named ' test'  that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...

All done! If you' ve completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Allow the port on the firewall then only the master and slave server will be able to communicate via the MariaDB default port number.

[root@linuxhelp ~]# firewall-cmd --permanent --add-port=3306/tcp
success
[root@linuxhelp ~]# firewall-cmd --reload
Success

Edit the configuration file. Here, we are going to tell MariaDB daemon to act as a master server.

[root@linuxhelp ~]# vim /etc/my.cnf

Add the following line into the [mysqld] section.

[mysqld]
Server_id=1
log-basename=master
log-bin
binlog-format=row
binlog-do-db=linuxhelp

Here, we have mentioned “ linuxhelp” for the database which we are going to replicate from master to slave server. Any modifications made inside the linuxhelp database will be updated in the slave server also.

Now restart the service.

[root@linuxhelp ~]# systemctl restart mariadb

Log into the database to create the slave user and password.

[root@linuxhelp ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with   or g.
Your MariaDB connection id is 2
Server version: 5.5.47-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type ' help '  or ' h'  for help. Type ' c'  to clear the current input statement.

MariaDB [(none)]>  STOP SLAVE 
Query OK, 0 rows affected, 1 warning (0.11 sec)

MariaDB [(none)]>  GRANT REPLICATION SLAVE ON *.* TO ' slave' @' %'  IDENTIFIED BY ' 123'  
Query OK, 0 rows affected (0.06 sec)

MariaDB [(none)]>  FLUSH PRIVILEGES 
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]>  FLUSH TABLES WITH READ LOCK 
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]>  SHOW MASTER STATUS 
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 460 | linuxhelp | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>  exit
Bye

Here, note down the file name (mariadb-bin.000001)and the position number (460). Use this values while we are configuring the Mariadb slave server.

Create backup of the master server by using the following command. After running the command you will find a new file called “ masterdatabase.sql” under your current directory.

[root@linuxhelp ~]# mysqldump --all-databases --user=root --password --master-data >  masterdatabase.sql
Enter password:
[root@linuxhelp ~]# ls
anaconda-ks.cfg Desktop Documents Downloads initial-setup-ks.cfg masterdatabase.sql Music Pictures Public Templates Videos

Again log into the database to unlock the tables after creating the backup.

[root@linuxhelp ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with   or g.
Your MariaDB connection id is 4
Server version: 5.5.47-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type ' help '  or ' h'  for help. Type ' c'  to clear the current input statement.

MariaDB [(none)]>  UNLOCK TABLES 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>  exit
Bye

Copy the dump file to slave server via scp command.

[root@linuxhelp ~]# ls
anaconda-ks.cfg Desktop Documents Downloads initial-setup-ks.cfg masterdatabase.sql Music Pictures Public Templates Videos
[root@linuxhelp ~]# scp masterdatabase.sql root@192.168.5.89:/root/
root@192.168.5.89' s password:
masterdatabase.sql 100% 502KB 502.5KB/s 00:00

To configure Slave Server

Now install the mariadb package on the Master server.

[root@linuxhelp ~]# yum install mariadb-server mariadb -y
BDB2053 Freeing read locks for locker 0x6be1: 7419/139909315262272
BDB2053 Freeing read locks for locker 0x6be3: 7419/139909315262272
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirror.nbrc.ac.in
* extras: repo.virtualhosting.hk
* updates: mirror.nbrc.ac.in
Resolving Dependencies
-->  Running transaction check
--->  Package mariadb.x86_64 1:5.5.47-1.el7_2 will be installed
.
.
.
perl-Data-Dumper.x86_64 0:2.145-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7
perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7

Dependency Updated:
mariadb-libs.x86_64 1:5.5.47-1.el7_2

Complete!

Restart and enable the services after installing the packages.

[root@linuxhelp ~]# systemctl restart mariadb
[root@linuxhelp ~]# systemctl enable mariadb
ln -s ' /usr/lib/systemd/system/mariadb.service'  ' /etc/systemd/system/multi-user.target.wants/mariadb.service' 

Set the root password as we set in master server. Now run the script “ mysql_secure_installtion” and follow the instructions to set the root password as mentioned below.

[root@linuxhelp ~]# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we' ll need the current
password for the root user. If you' ve just installed MariaDB, and
you haven' t set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from ' localhost' . This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MariaDB comes with a database named ' test'  that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...

All done! If you' ve completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Allow the port on the firewall as we done in master server.

[root@linuxhelp ~]# firewall-cmd --permanent --add-port=3306/tcp
success
[root@linuxhelp ~]# firewall-cmd --reload
Success

Edit the configuration file

[root@linuxhelp ~]# vim /etc/my.cnf

Add the following lines to the my.cnf file.

[mysqld]
Server_id=2
Replicate-do-db=linuxhelp

Here, the database linuxhelp will be replicated from the master server.

Now import the master server database dump file.

[root@linuxhelp ~]# ls
anaconda-ks.cfg Documents initial-setup-ks.cfg Music Pictures sparkweb_0_9_0.tar.gz Videos
Desktop Downloads masterdatabase.sql openfire-4.0.2-1.i386.rpm Public Templates
[root@linuxhelp ~]# mysql -u root -p <  masterdatabase.sql 
Enter password:
[root@linuxhelp ~]# systemctl restart mariadb 

Log into the database and configure the slave server as follows to look for master log file name and position that we have noted before while configuring the master server.

[root@linuxhelp ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with   or g. 
Your MariaDB connection id is 2 
Server version: 5.5.47-MariaDB MariaDB Server 
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. 
Type ' help '  or ' h'  for help. Type ' c'  to clear the current input statement. 
MariaDB [(none)]>  STOP SLAVE 
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]>  CHANGE MASTER TO MASTER_HOST=' 192.168.5.88' , MASTER_USER=' slave' , MASTER_PASSWORD=' 123' , MASTER_LOG_FILE=' mariadb-bin.000001' , MASTER_LOG_POS=460 
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]>  SLAVE START 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>  SHOW SLAVE STATUSG 
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.5.88
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 460
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: linuxhelp
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 460
Relay_Log_Space: 827
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

To Test Database Replication

Go to the Master Server, create the database linuxhelp that we have configured for replication. Add some tables and values the same database will also be updated on the Slave Server.

Master Side

[root@linuxhelp ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with   or g.
Your MariaDB connection id is 7
Server version: 5.5.47-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type ' help '  or ' h'  for help. Type ' c'  to clear the current input statement.

MariaDB [(none)]>  create database linuxhelp 
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>  use linuxhelp
Database changed
MariaDB [linuxhelp]>  create table sample (a int) 
Query OK, 0 rows affected (0.14 sec)

MariaDB [linuxhelp]>  insert into sample (a) values (1) 
Query OK, 1 row affected (0.57 sec)

MariaDB [linuxhelp]>  show tables 
+---------------------+
| Tables_in_linuxhelp |
+---------------------+
| sample |
+---------------------+
1 row in set (0.00 sec)

MariaDB [linuxhelp]>  select * from sample 
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

Slave Side

[root@linuxhelp ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with   or g.
Your MariaDB connection id is 2
Server version: 5.5.47-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type ' help '  or ' h'  for help. Type ' c'  to clear the current input statement.

MariaDB [(none)]>  show databases 
+--------------------+
| Database |
+--------------------+
| information_schema |
| linuxhelp |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>  use linuxhelp 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [linuxhelp]>  show tables 
+---------------------+
| Tables_in_linuxhelp |
+---------------------+
| sample |
+---------------------+
1 row in set (0.00 sec)

MariaDB [linuxhelp]>  select * from sample 
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)