Category: Database Systems

Simple Master-Master replication on MariaDB

Lets set up a simple Master-Master database replication on MariaDB. I have already posted an article on the setting up of Master-Slave database replication. You can test it on Virtual Box or Vmware labs for your own analysis and benchmark. Lets called both MariaDB Master servers as Master1 and Master2.


ice_logo-5dcea9e47b780ff52f75c3c3304d54827f56211e
Photo credits – Mariadb.org

1. Create the 2 Labs (test it on Centos7) and edit your /etc/hosts so that each server can ping each other via the hostname. On Master1, edit your /etc/my.cnf file and on the top enter the following parameter:


[mysqld]
server-id=1
log-bin=mysql-bin

2. On master1 SQL prompt, create a user for replication slave followed by flush privileges.

MariaDB [(none)]> grant replication slave on *.* to [email protected]'%' identified by 'replipassword';
MariaDB [(none)]> flush privileges;

3. Flush the tables for read-only access.


MariaDB [(none)]> flush tables with read lock;

4. At this point, if you do a show master status, you need to have a file with the mysql-bin incrementing number as well a  position number.

MariaDB [(none)]> show master status\G
File: mysql-bin.000001
Position: 612
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

5. Now, move on to the Master2 server, I assume you have already start MariaDB, setting up mysql_secure_installation, and edit the /etc/hosts file etc..

6. On Master2 edit your /etc/my.cnf file and on top enter the following parameter, after which you can restart the MariaDB service.


[mysqld]
server-id=2

7. Now on the Master1 server, do a dump of the database MySQL and import it to Master2, so that we make sure it starts and synchronize with the same conf. So, I did a dump of the MySQL database on master1 and SCP it to master2

[[email protected] yum.repos.d]# mysqldump -u root -p --database mysql > mysql.mysql
[[email protected] home]# scp mysql.mysql [email protected]:/home

8. On Master2 import the MySQL database


[[email protected] home]# mysql mysql -u root -p <  mysql.mysql 

9. Now, we will temporarily run Master2 as the slave.

MariaDB [(none)]> change master to master_host='master1', master_user='repli', master_password='replipassword', master_log_file='mysql-bin.000001', master_log_pos=612;
MariaDB [(none)]> start slave;

10. At this point, you would notice that the Slave is running correctly by verifying with a show slave status\G. Do check the Slave_IO_Running, Master_User, Log_Pos and the Slave_SQL_Running parameters.

11. Now that Master1 is the master and Master2 is the slave, we will perform the reverse order to reach our goal. At this level, you can unlock the tables which we did at step3. On master1 fire this command.

unlock table

12. On Master2 (which is actually a slave), re-edit my.cnf file and under the [mysqld] and server-id add the following parameter. It should look like this


[mysqld]
server-id=2
log-bin=mysql-bin

13. On Master2 restart MariaDB with the command systemctl restart mariadb and logging to Mysql and do a show master status. The result should be similar to this.

MariaDB [(none)]> show master status\G
File: mysql-bin.000001
Position: 313
Binlog_Do_DB:
Binlog_Ignore_DB: 

14. On Master1, a show slave status will prompt you an empty set. Now since we have dumped the MySQL database to Master2, we can now just run Master1 as the slave, i.e to repeat the process as in step 9. Launch this command on Master1 to create it as a slave. The File and Position should be taken from the Master2.

MariaDB [(none)]> change master to master_host='master2', master_user='repli', master_password='replipassword', master_log_file='mysql-bin.000001', master_log_pos=313;
MariaDB [(none)]> start slave;

15.  Test by creating database and tables on both servers, it will replicate on each other.

Tips:

    • Each time you edit my.cnf file, you need to restart MariaDB so that the conf take effect.
    • After having created the Master-Slave replication at step 10, you can check on both Master1/2 with a netstat -ntpla | egrep -i established.*mysql. You would notice the established connection and the port it’s listening.
    • The command host with the IP established will also confirm its from the specific host.
    • Further testing on Master1 can also be done with command as mysql -u root -p -e “show processlist” which will give you an indication of the state of the server.
  • The value of File and Position of Master1 when using the command show master status should correspond with the values of Master_Log_File and Read_Master_Log_Pos of Master2 with the command show slave status and vice versa.

Simple Master-Slave replication on MariaDB

This article will explain all the necessary configurations to set up a simple Master-Slave replication on MariaDB. I have tested it on CentOS7. More articles on MariaDB optimization, Master-Master replication, and Galera cluster are coming up soon. Well, I was introduced to MariaDB in the year 2014 by Joffrey Michaie of SkySQL at Flying Dodo video conference room, Bagatelle, Mauritius during a LUGM [Linux User Group of Mauritius] meet up. You can still view out the video on the LUGM Youtube Channel.


What is MariaDB? “MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Being a fork of a leading open source software system, it is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle. ” – Wikipedia. 


ice_logo-5dcea9e47b780ff52f75c3c3304d54827f56211e
Photo Credits – Mariadb.com

I have set up 2 Centos 7 Labs; one as Master and the other as Slave.

You will need to install MariaDB on both servers to start with.

Make sure port 3306 is opened to enable replication.


Since the test is based on 2 Virtual Box labs, I have temporarily disabled IPtables.

At the time I am writing this article, the latest version is 10.1.8-stable.

You can easily download MariaDB through the repository configuration tool

1. After having installed a fresh MariaDB on both virtual machines – Master / Slave, you will need to configure the root access to MySQL by using the following command. It will prompt you to enter a password which is a blank password by default. Then, you will have to enter a password for root which will prompt you to remove anonymous users, disallow root login, remove the test database and access to it and reloading privilege tables. Just press ‘Y’ by following the command below:


mysql_secure_installation

2. After setting up the root user of MySQL, you can test it with a fake or no password to ensure the root password has been set up effectively. Next step, is to create a database with some tables in it or simply import a database. I created a table in the database called ’employees’ on the Master.

create database employees;
use employees;
create table profile (name char(30), age int(2), address varchar(40)) ;

3.  So, a database has been created on the Master. We will now edit the /etc/my.cnf on the master and under the [mysqld] enter the following:


server-id=1
log-bin = mysql-bin

4. Next step, is to create a user for replication purpose to the slave server (The command need to be launched on the master server). The IP 192.168.1.8 is the slave server. Once the grant replication is launched the user repliuser will be created automatically. Afterward, launch the command flush privileges so that the server noticed these changes to load the grant tables into memory.

MariaDB [(none)]>

grant replication slave on *.* to [email protected]'192.168.1.8' identified by 'replipassword';

MariaDB [(none)]>

flush privileges;

5. At this point, if you launch a show master status; on the master server you will have a prompt “Empty set“. To enable the master, we need to restart MariaDB. As we want to replicate this master database on the slave server, we need to stop mysql to continue writing on the table.

MariaDB [(none)]>

flush tables with read lock;
systemctl restart mariadb

6. Connect back to MariaDB and launched a show master status\G You will need to find a result similar to this screenshot.

Screenshot from 2015-11-03 20:18:21

7. We now have a file name and a position. These two values will be used to set up the slave database. Do a MySQL dump on the master database and import it to the slave server (I copied it from master to slave on the /home folder).

On the Master server:

[[email protected] ~]#


mysqldump -u root -p employees  > employees.sql

[[email protected]aster ~]#

scp employees.sql [email protected]:/home

8. Now, we log in to the slave server. Create database employees just as in the master server and import the database employees to the slave database server.

[[email protected] home]#

mysql -u root -p employees < employees.sql

9. Edit the /etc/my.cnf in the slave server and enter the following. Note the value of the server-id should be greater than that of the master server, otherwise, it may not work. Then restart MariaDB on the slave server.

server-id = 2
systemctl restart mariadb

10. Right now, the slave server cannot identify the master-slave to synchronize the database for replication.

On the master server:

unlock tables;

On the slave server:

MariaDB [(none)]>


change master to master_host='master', master_user='repliuser', master_password='replipassword', master_log_file='mysql-bin.000001', master_log_pos=313;

11. You can now start the slave replication. Afterward, you can type a show slave status\G;

start slave;
Screenshot from 2015-11-03 21:04:07
show slave status\G indicating replication on the slave server

Tips:

    • To find the list of services for which specific ports can be opened, use the command firewall-cmd –get-services. Hence, you can add the MySQL service with the command firewall-cmd –permanent –add-service-mysql. After adding the MySQL service you will need to reload the firewall service with the command firewall-cmd reload. To verify if certain rules have been loaded in the firewall use the command firewall-cmd –list-all
    • To check which database you have, you can fire the command show schemas;


  • At step 9, If you want to replicate a specific database on the slave server, you can use the following parameter in the /etc/my.cnf in the slave under [mysqld] use replicate-wild-do-table=employees.% where the .% means all tables under the database employees.
  • Since you will be on Virtual Box or Vmware, you may need to edit your /etc/hosts so that each server recognized each other as the Master_User and Slave_User respectively. In this case, in the /etc/hosts for the master enter the IP of the slave server followed by the hostname of the slave and vice versa. Test it with a ping. In my case, if I ping master from the slave, it will answer promptly.