Databases are arguably one of the main component of any modern application. And one of the most popular implementation of SQL databases is Mysql or the new replacement MariaDB. As a system administrator, you probably have installed and configured a database before. This tutorial will walk though setting up the master-slave replication of MariaDB.
Replication is used to create multiple copies of our database and these copies then can either be used as another database to run our queries on, queries that might otherwise affect performance of master server like running some heavy analytics queries or we can just use them for data redundancy purposes or for both. We can automate the whole process i.e. data replication occurs automatically from master to slave. Backups are be done without affecting the write operations of the master
The IP addresses for the both the machines on this tutorial:
Master – 192.168.1.110 Hostname- master.dbserver.com
Slave – 192.168.1.120 Hostname – slave.dbserver.com
If you haven’t installed MariaDB on both server, you can install by typing:
For CentOS/Red Hat:
sudo yum install mariadb mariadb-server
For Ubuntu/Debian:
sudo apt update && sudo apt install mariadb mariadb-server
Step 1- Master Server Configuration
We are going to take a database named ‘myDB’ in MariaDB, that will be replicated to our slave server. To start the process, we will edit the files ‘/etc/my.cnf’ , it’s the configuration file for mariadb,
$ vi /etc/my.cnf
then look for section with [mysqld] and then enter the following details,
[mysqld]
log-bin
server_id=1
replicate-do-db=important
bind-address=192.168.1.110
Save and exit the file. Once done, restart the mariadb services,
$ systemctl restart mariadb
Next, we will login to our mariadb instance on master server,
$ mysql –u root –p
Now will create a new user for slave named ‘slaveuser’ and assign it necessary privileges by running the following command:
STOP SLAVE;
GRANT REPLICATION SLAVE ON *.* TO ‘slaveuser’@’%’ IDENTIFIED BY ‘slavepassword’;
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Note:- We need values from MASTER_LOG_FILE and MASTER_LOG_POS from out of ‘show master status’ for configuring replication, so make sure that you have those.
Once these commands run successfully, exit from the session by typing ‘exit’.
Step2 – Create a backup of the database and move it slave
Now we need to create backup of our database ‘myDB’, which can be done using ‘mysqldump’ command,
$ mysqldump –u root –p myDB > myDB_backup.sql
Once the backup is complete, we need to log back into the mariadb and unlock our tables,
$ mysql –u root –p
$ UNLOCK TABLES;
Exit the session.
Now we will move the database backup to our slave server which has a IP address of 192.168.1.120,
$ scp myDB_backup.sql [email protected]:~/
This completes our configuration on Master server, we will now move onto configuring our slave server.
Step 3 Configuring Slave server
We will again start with editing ‘/etc/my.cnf’ file and look for section [mysqld]. Then enter the following details,
[mysqld]
server-id = 2
replicate-do-db=important
[…]
We will now restore our database to mariadb, by running
$ mysql -u root -p < ~/myDB_backup.sql
When the process completes, we will provide the privileges to ‘slaveuser’ on db ‘important’ by logging into mariadb on slave server,
$ mysql –u root –p
GRANT ALL PRIVILEGES ON important.* TO ‘slaveuser’@’localhost’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
Next restart mariadb for implementing the changes.
$ systemctl restart mariadb
Step 4 Start the replication
Remember, we need MASTER_LOG_FILE and MASTER_LOG_POS variables which we got from running ‘SHOW MASTER STATUS’ on mariadb on master server. Now login to mariadb on slave server and we will tell our slave server where to look for the master by running the following commands,
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST=’192.168.1.110′, MASTER_USER=’slaveuser’, MASTER_PASSWORD=’slavepassword’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=460;
SLAVE START;
SHOW SLAVE STATUS\G;
Note:- Change details of your master as necessary.
Step 5 Testing the replication
We will now create a new tables in our database on master to make sure if the replication is working or not. So, login to mariadb on master server,
$ mysql –u root –p
select the database ‘myDB’,
use myDB;
and create a table named test in the db,
create table test (c int);
then insert some value into it,
insert into test (c) value (1);
To check the added value,
select * from test;
You will find that your db has a table has the value you inserted.
Now let’s login to our slave database to make sure if our data replication is working,
$ mysql –u root –p
$ use important;
$ select * from test;
You should see that the output shows the same value that we inserted on the master server, hence our replication is working fine without any issues.
If you want to learn important MySQL/MariaDB commands, click here. Additional commands on this tutorial.
– masterkenneth