MySQL is one of the most popular database engines in the market. It is an open source RDBMS, which was first launched on May 23, 1995. MySQL is a central component of the LAMP open-source web application stack. The list of applications that use MySQL is endless. Just to name a few: TYPO3, MODx, Joomla, WordPress, Simple Machines Forum, phpBB, MyBB, and Drupal. MySQL is also used by several large scale platforms, including Google, Facebook, Twitter, Flick, and YouTube.

Database replication is commonly used for data recovery, for improving performance, and for redundancy. In today’s post, we will show you how to set up DB replication with MySQL.

PREREQUISITES:

• CentOS 7 Master server
• CentOS 7 Slave server
• Root Access to both servers

MARIADB INSTALLATION:

MariaDB needs to be installed on each of the servers- Master and Slave. The installation is done using the following command- yum install mariadb-server mariadb -y

The installation will take some time. This needs to be completed on both servers. Once the installation is complete, you can start the service using the following command systemctl start mariadb.service

You can secure the installation using the following command- mysql_secure_installation

This completes initial setup and installation of MariaDB on both servers.

CONFIGURE MASTER:

Next, in the master server open the file my.cnf, located at /etc/my.cnf and edit it with the following lines-

server_id=1
log-bin
replicate-do-db=hostseo

Replace the name “hostseo” with the database name you want to replicate. From Master, you can load MariaDB to configure the replication. This can be done using the following command- mysql -u root -p.

On the console, you can initiate the slave replication using the following commands-

grant replication slave on *.* to ‘slave_user’@’%’ identified by ‘hostseopassword’;

In the command replace slave_user with the actual slave username and hostseopassword with its password. Once this is changed, you can check the status of the Master server using the following command- show master status

This output contains log file name and log position. For instance, in this example the log file name is mariadb-bin.000001 and the log position is 475. Make a note of this separately, since it will be needed for configuring the slave.

You can now exit this server using the exit command over the console. We’re now ready to configure the slave.

CONFIGURE SLAVE:

Login to the slave server and edit the my.cnf file with the following two lines.

server_id=2
replicate-do-db=hostseo

Replace hostseo slave using the follwing command-

with your DB name. Next restart MariaDB for the changes to get reflected. It can be restarted using the following command- systemctl restart mariadb.service

To start the configuration you can load the slave using the command- mysql -u root -p. First, stop the slave using the following command– stop slave

Configure thechange master to master_host=’192.250.230.79′ , master_user=’slave_user’ , master_password=’hostseopassword’ , master_log_file=’mariadb-bin.000001′ , master_log_pos=475;

Replace the IP address, slave user name, password, master_log_file and master_log_pos based on your configuration.

Once this is done start the slave using the following command- start slave.

The replication status can be checked using the following command – SHOW SLAVE STATUS. This will provide you the complete output displaying the replication status.

With this, we have completed our Master and Slave configuration. Next, we will proceed towards testing our changes.

TESTING:

First, log on to the Master server and enter command- mysql -u root -p. Once logged in create a database and a table for testing purpose. Use the following commands to complete the database setup-

create database hostseo;
use hostseo;
create table staff (c int);
insert into staff (c) values (3);

You can view the contents of the table using the command-  select * from staff;

Next log into slave server again using the command – mysql -u root -p. Use the below commands to test this-

use hostseo
select * from staff;

This will give the same output as shown on the Master server.

With this, we have completed the replication setup of our MySQL database!