[MariaDB] Create a master/slave configuration in MariaDB

Hi all, in this tutorial I am going to create a master/slave configuration in MariaDB.

MariaDB installation

To install MariaDB on Linux just follow this tutorial.

How to install MariaDB on Linux

Master configuration

Now, it is necessary to configure the master for replication.

First, set the following parameters in the configuration file (in this case /etc/mysql/mariadb.conf.d/50-server.cnf):

bind-address = 0.0.0.0
server-id    = 1
log_bin      = /var/log/mysql/mysql-bin.log
binlog_do_db = test

• bind-address: the MariaDB server listen on this address. Setting it to 0.0.0.0 enables the server to listen on all addresses. It is not secure, but for the purpose of this tutorial is sufficient
• server-id: ID of the server. In any configuration IDs are unique and identify a specific server
• log_bin: enables the logging of every change of the database, in this way every change can be send to the replicas
• binlog_do_db: identifies specific databases whose changes have to be logged. Multiple entries of this parameter can be listed, one for each database to be tracked

Next, the replication user needs to be created:

GRANT REPLICATION SLAVE ON *.* TO 'user_replica'@'%' IDENTIFIED BY 'user_replica';

This user will be used by the slave to remote access to the master server.

Dump database to replicate – optional

If there is a database already populated on the master that needs to be replicated, dump it and save the state of the master in order to establish an exact point in time to start replication.

First, block all write operations on database test in master server and print the status:

USE test;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      313 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The File and Position values are important to start replication.

Then, dump the test database:

mysqldump -u root -p test > /tmp/test_dump.sql

And then remove the lock on test database:

USE test;
UNLOCK TABLES;

Now, copy the dump on the slave server (using scp, for example) and import using mysql:

##on master server
scp /tmp/test_dump.sql slave_server_name:/tmp/test_dump.sql
##on slave server
mysql -u root -p < /tmp/test_dump.sql

Slave configuration

Now, it is necessary to configure the slave for replication.

The following parameters have to be added to the server configuration file (located at the same location of the master server):

server-id    = 2
relay-log    = /var/log/mysql/mysql-relay-bin.log
log_bin      = /var/log/mysql/mysql-bin.log
binlog_do_db = test

Next, connect to the master:

CHANGE MASTER TO MASTER_HOST='MASTER_SERVER_IP', MASTER_USER='user_replica',
-> MASTER_PASSWORD='user_replica', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=313;

• MASTER_HOST: IP address of the master server
• MASTER_USER: the user dedicated to replica created before
• MASTER_PASSWORD: the password of the user dedicated to replica
• MASTER_LOG_FILE: the log file where the master was writing changes when the test database was dumped
• MASTER_LOG_POS: position inside the MASTER_LOG_FILE where the master was writing changes when the test database was dumped

Lastly, just start the slave replication:

START SLAVE;

To check the replication status, just type the following command in the slave.

SHOW SLAVE STATUS;

Among the output, the most important lines are the following:

Slave_IO_State: Waiting for master to send event 
Slave_SQL_Running_State: Slave has read all relay log;  waiting for the slave I/O thread to update it
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If you need to stop replication, just type:

STOP SLAVE;

That’s all! See you in the next tutorial!