HostSEO Blog

Stories and News from IT Industry, Reviews & Tips | Technology Blog


HOW TO SET UP HIGH AVAILABILITY DATABASE REPLICATION WITH MYSQL IN CENTOS 7

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

Installation

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

Start

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.

Root

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

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!

Subscribe Now

10,000 successful online businessmen like to have our content directly delivered to their inbox. Subscribe to our newsletter!

Archive Calendar

SatSunMonTueWedThuFri
1234567
891011121314
15161718192021
22232425262728
293031 

Born in 2004 ... Trusted By Clients n' Experts

SEO Stars

They never made me feel silly for asking questions. Help me understand how to attract more people and improve my search engine ranking.

Read More

Emily Schneller Manager at Sabre Inc
SEO Stars

Took advantage of Hostseo's superb tech support and I must say, it is a very perfect one. It is very fast, servers reliability is incredible.

Read More

Leena Mäkinen Creative producer
SEO Stars

We're operating a worldwide network of servers with high quality standards requirements, we’ve choose hostseo to be our perfect partner.

Read More

Ziff Davis CEO at Mashable
SEO Stars

It’s very comfortable to know I can rely about all technical issues on Hostseo and mostly that my website and emails are safe and secured here.

Read More

Isaac H. Entrepreneur
SEO Stars

With hostseo as a hosting partner we are more flexible and save money due to the better packages with great pricing, free SEO n' free SSL too!

Read More

Madeline E. Internet Professional