How to MySQL Replication Master-Slave in Centos 7

We'll do a very simple mysql replication. A master will send information to a slave. So, we will need two ip addresses:
  1. Master :- 192.168.31.115    mysq1.bhandari.com
  2. Slave :- 192.168.31.85         mysql2.bhandari.com
  3. Database :- Bhandari (your database name)

Install Mariadb Package  :----
yum install maridb-server mysql -y   --------install package in both database server

service mariadb restart
or
/bin/systemctl restart mariadb.service

Set Root password then Login Mysql Server:--
mysql -uroot -p

Note :- In this tutorial don't create the database bhandari (your database)

Give the Master Database Server Side :---  Entries under [mysqld] section and don’t forget to replace bhandari with database name that you would like to replicate on Slave,

vim /etc/my.cnf

server-id = 1
binlog-do-db=bhandari
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 Service :--

/bin/systemctl restart mariadb.service


Then login Mysql Server :--
mysql -uroot -p
GRANT REPLICATION SLAVE ON *.* TO 'surender'@'%' IDENTIFIED BY 'xyz@123';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note :- Replace the  mysql-bin.000005 |  467 | bhandari of your mysql replication

This is the position from which the slave database will start replicating. If we make any new changes in the same sql shell, the database will automatically unlock. Therefore, we should open the new tab/window and continue with the next steps there.

Login the Master Database Server to another tab  :--

Take dump   -------------------------------
mysqldump -u root -p --all-databases --master-data > /root/testing.db

Go to first tab which is already  login :--
UNLOCK TABLES;
QUIT;


Note :- Send the Dump file to Slave Database Server

scp testing.db root@192.168.31.85:/root/

Now Slave Database Server Configuration (192.168.31.85).

Go to Slave Database Server :--
vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

server-id = 2
replicate-do-db=bhandari
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

systemctl restart mariadb.service
mysql -u root -p < /root/testing.db
mysql -uroot -p
slave stop;
CHANGE MASTER TO MASTER_HOST='192.168.31.115', MASTER_USER='surender', MASTER_PASSWORD='xyz@123', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=467;
slave start;
show slave status\G


Go to Master Database Server :--
mysql -uroot -p
create database bhandari;
use bhandari;
create table bhandari (c int);
INSERT INTO bhandari (c) VALUES (1);
SELECT * FROM bhandari;

Verify the Slave Database Server
mysql -uroot -p
use bhandari;
SELECT * FROM bhandari;

… That’s about it
How to MySQL Replication Master-Slave in Centos 7 How to MySQL Replication Master-Slave in Centos 7 Reviewed by Unknown on August 25, 2018 Rating: 5

No comments:

Scribe

http://feeds.feedburner.com/LinuxAndAws
Powered by Blogger.
X

Get Updates On

Linux Tutorial

AWS Tutorial

Devops Tutorial

We are going to send you our resources for free. To collect your copy at first, join our mailing list. So don't miss any updates, stay connected!