How to configure two node MariaDB Master and Slave Replication
How to configure two node MariaDB Master and Slave Replication
MariaDB has more features and improved performance than MySQL. In this tutorial we are going to show how to configure Mariadb Master with two slaves replications. Master node is configured in Centos 7 and slave node one is configured in centos7 and slave node two is configured in Ubuntu 16.04 machine.
Prerequisite
Before starting this replication concept you should install MariaDB package into all the three machines.
Master configuration
To start the configuration process check if the MariaDB has been installed in the machine. Once MariaDB package is installed in master node check the status of MariaDB by using the following command.
[root@linuxhelp1 ~]# systemctl status mariadb
Add the MariaDB service into firewall if it is necessary. After that reload the firewall service.
[root@linuxhelp1 ~]# firewall-cmd --permanent --add-service=mysql [root@linuxhelp1 ~]# firewall-cmd --reload
Now the firewall has been reloaded. Open the configuration file of MariaDB and add the following lines.
[root@linuxhelp1 ~]# vim /etc/my.cnf
[mysqld]
server_id=10
log-basename=master
log-bin
binlog-format=row
binlog-do-db=games
After entering the configuration settings restart the MariaDB service.
[root@linuxhelp1 ~]# systemctl restart mariadb
Open the MariaDB shell and do the following MariaDB master replication configuration. Create a database named games and stop the slave service.
[root@linuxhelp1 ~]# mysql Welcome to the MariaDB monitor. Commands end with or g. Your MariaDB connection id is 2 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type ' help ' or ' h' for help. Type ' c' to clear the current input statement. MariaDB [(none)]> create database games Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)]> stop slave Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)]> create user ' reply' @' %' identified by " p@ssw0rd" Query OK, 0 rows affected (0.13 sec) MariaDB [(none)]> grant replication slave on *.* to ' reply' @' %' identified by " p@ssw0rd" Query OK, 0 rows affected (0.12 sec) MariaDB [(none)]> flush privileges Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> FLUSH TABLES WITH READ LOCK Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW MASTER STATUS +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000001 | 572 | replication | | +--------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> q Bye
Now take the whole database backup from master node.
[root@linuxhelp1 ~]# mysqldump --all-databases --master-data > reply.sql
Transfer the database backup into two slave machines by running the command.
[root@linuxhelp1 ~]# rsync -Paxvzl reply.sql root@192.168.5.140:/root/Desktop/
[root@linuxhelp1 ~]# rsync -Paxvzl reply.sql user1@192.168.5.152:/home/user1/Desktop
Open the MariaDB shell and unlock the tables.
[root@linuxhelp1 ~]# mysql MariaDB [(none)]> UNLOCK TABLES Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> q Bye
The master node has been configured successfully.
Slave1 Configuration.
Once MariaDB package is installed in slave1 node check the status of MariaDB by running the command.
[root@linuxhelp1 ~]# systemctl status mariadb
Add the MariaDB service into firewall if it is necessary. After that reload the firewall service.
[root@linuxhelp1 ~]# firewall-cmd --permanent --add-service=mysql
[root@linuxhelp1 ~]# firewall-cmd --reload
Open the configuration file of MariaDB and add the following lines.
[root@linuxhelp1 ~]# vim /etc/my.cnf
[mysqld]
server-id = 20
replicate-do-db=games
Now insert the database backup file into slave database.
[root@linuxhelp1 ~]# mysql < reply.sql
After that restart the MariaDB service.
[root@linuxhelp1 ~]# systemctl restart mariadb.service
Open the MariaDB shell and do the following slave configuration.
[root@linuxhelp1 ~]# mysql Welcome to the MariaDB monitor. Commands end with or g. Your MariaDB connection id is 2 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, 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.160' , master_user=' reply' , master_password=' p@ssw0rd' , master_log_file=' mariadb-bin.000001' , master_log_pos=762 Query OK, 0 rows affected (0.13 sec) MariaDB [(none)]> slave start Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave statusG MariaDB [(none)]> q Bye
The slave 1 node has been configured successfully.
Slave2 configuration
Once MariaDB package is installed in slave2 node. Check the status of MariaDB.
[root@linuxhelp1 ~]# systemctl status mysql
Add the mariadb service into firewall if it is necessary.
root@linuxhelp~# ufw add mysql
Open the configuration file of mariadb and add the following statements.
root@linuxhelp~# vim /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = 192.168.5.152
server-id = 30
replicate-do-db=games
log_bin = /var/log/mysql/mysql-bin.log
Now insert the database backup file into slave database.
[root@linuxhelp1 ~]# mysql < reply.sql
Now restart the MariaDB service.
[root@linuxhelp1 ~]# systemctl restart mysql
Open the MariaDB shell and do the following slave configuration.
[root@linuxhelp1 ~]# mysql Welcome to the MariaDB monitor. Commands end with or g. Your MariaDB connection id is 2 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, 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.160' , master_user=' reply' , master_password=' p@ssw0rd' , master_log_file=' mariadb-bin.000001' , master_log_pos=762 Query OK, 0 rows affected (0.13 sec) MariaDB [(none)]> slave start Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave statusG MariaDB [(none)]> q Bye
The slave 2 node has been successfully configured as well.
To Check Replication status
In master node, do the following steps to check the replication status.
[root@linuxhelp1 ~]# mysql MariaDB [(none)]> use games MariaDB [games]> create table sports(categories varchar(100)) MariaDB [games]> show tables +-----------------------+ | Tables_in_games | +-----------------------+ | sports | +-----------------------+ MariaDB [games> insert into sports values(' cricket’ ) Query OK, 1 row affected (0.05 sec)
Now goto Slave1 and do the following steps to start the replication process.
[root@linuxhelp1 ~]# mysql MariaDB [(none)]> use games MariaDB [games]> show tables +-----------------------+ | Tables_in_games | +-----------------------+ | sports | +-----------------------+ MariaDB [games]> select * from sports +------+--------+------+ | categories | +------+--------+------+ | Cricket | +------+--------+------+
Goto Slave2 and do the following steps as same.
[root@linuxhelp1 ~]# mysql MariaDB [(none)]> use games MariaDB [games]> show tables +-----------------------+ | Tables_in_games | +-----------------------+ | sports | +-----------------------+ MariaDB [games]> select * from sports +------+--------+------+ | categories | +------+--------+------+ | Cricket | +------+--------+------+
The Replication process is complete with a master node and two slave nodes.
Wasn' t that an easy configuration process. MariaDB is also widely used in Mozilla Corporation, ArchLinux, OpenSuse, Wikipedia, OLX, SlashGear, etc.