Medha Cloud Logo

How to Setup MySQL (master-slave) Replication in CentOS 7

November 19, 2018

Hello friends,

Today we are going to discuss MySQL replication in CentOS.

We love your sheer enthusiasm for more questions and inquiries, and we also like to resolve your doubts, misconceptions, and deadlocks about tech subjects.

Especially, today's question How to setup MySQL replication in CentOS.

Also, I hope you have seen my previous document for ‘Server hardening on CentOS.’
Today, we have something new and exciting for you.
Let’s check, how to set up MySQL and what is the benefits?
Here in this tutorial, I am explaining how to set up MySQL (master-slave) replication on centos.
MySQL replication is a technique by which a master database will be automatically copied to one or more slave databases. Moreover, it helps in making data backup, recovery, and analysis a much easier prospect.

The following guide will walk you through a basic MySQL replication setup using just two servers, one master and one slave.
Further, this can help for some reasons including facilitating a backup for the data, a way to analyze it without utilizing the primary database.
Click here to know more about our database support service
Replication is a feature allowing the contents of one or more servers (called masters) to be mirrored on one or more servers (called slaves).

Before you begin, make sure you have the following:
• 2 servers, both running a fresh installation of CentOS 7
• Root access to both machines
Server Master IP: xx.xx.xx.xx
• Server Slave IP: xx.xx.xx.xx
Master and Slave are on the same LAN network.

Purpose of using Replication:

1. High Availability
2.Scale-out
3.Backup servers
4.Disaster Recovery
5.Reporting servers

Install MySQL server:

Step1: Frst install MySQL on the master server

yum install mysql-server mysql

Start the MySQL server.

service mysqld start

vi /etc/my.cnf

Configure MySQL server
Add the following lines:
server-id = 1
binlog-do-db=yourname
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin

Restart MySQL server

service mysqld restart

Step2: Create MySQL user for the slave server and configure it:

Login into MySQL as root user and create the slave user and grant privileges for replication.

Then, replace slave_userwith user and your_password with a password.

mysql -u root -p

mysql -u root

Mysql> use mysql;
Mysql> show tables;

Mysql> describe user;

You will find the user table, now let's see its fields:
Surprise! There is no field named 'password,' the password field is named ' authentication_string.' So, do this:

Mysql> update user set authentication_string=password('1111') where user='root';
Now, everything will be ok.

Mysql> FLUSH PRIVILEGES;
Mysql> quit;

service mysqld start

mysql -u root -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
mysql> quit;

Please write down the File (MySQL-bin.000003) and Position (11128001) numbers; we required these numbers later on the Slave server. Next, apply READ LOCK to databases to export all the database and master database information with mysqldump command.

sqldump -u root -p --all-databases --master-data > /root/dbdump.db

Once you’ve dumped all the databases, now again connect to MySQL as root user and unlock tables.

mysql -u root -p

Mysql> UNLOCK TABLES;
Mysql>quit;

Upload the database dump file on Slave Server (xx.xx.xx.xx) using SCP command.

scp /root/dbdump.db root@slave ip:/root/

That’s it we have successfully configured the Master server, let’s proceed to the Slave server.

Setup slave server:

Then login your slave server after that
If you don’t have MySQL installed, then install it using YUM command.

yum install mysql-server mysql

After that, once the installation starts with the configuration on the slave server,

vi /etc/my.cnf

service mysqld restart



Verifying MySQL Replication on Master and Slave Server
Then, goto master server

mysql -u root -p

mysql> create database svnraju;
mysql> use svnraju;
mysql> CREATE TABLE employee (c int);
mysql> INSERT INTO employee (c) VALUES (1);
mysql> SELECT * FROM employee;


Then goto slave server:

mysql -u root -p

mysql> use svnraju;
mysql> SELECT * FROM employee;

Finally, you have successfully installed and setup MySQL (master-slave).  We have made MySQL replication in CentOS.
I hope you’ve got the cuts and turns that you need to take while setting up MySQL.
So, try it yourself and if you come across with the confusion that makes you scratch your heads, just give us a ring, our database administrators will assist you.

In addition, if you need and support on Linux server and database support, Medhahosting is there for you.

Stay tuned, will back with another answer.

Connect with Medha Cloud

Microsoft 365 Migration Form

I would like to send my contact information to MedhaCloud so that MedhaCloud can share additional information about products, services, thought leadership and invitations to flagship events with me by email. *

By submitting this form, I acknowledge that someone from MedhaCloud will contact me via email or phone to discuss my request.

Related Article

View All
chevron-down linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram