How to Configure Master and Master Replication setup in Centos 7


How to Configure Master and Master Replication setup in Centos 7
How to Configure Master and Master Replication setup in Centos 7







We will learn how to Make Master-Master Replication setup for the Mysql Database because of
it will not update Master if there are any changes done on slave server.
Read this Article to setup Master-Slave replication.
This article will help you to set up Master-Master replication between MySQL servers. In this setup if any changes made on either server will update on an other one.
  1. Master A :- 192.168.31.115    mysq1.bhandari.com
  2. Master B :- 192.168.31.85         mysql2.bhandari.com
  3. Database :- Bhandari (your database name)

Install Mariadb Package in Both Servers  :----

yum install maridb-server mysql -y
service mariadb restart
bin/systemctl restart mariadb.service
Master A Server :- 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
auto-increment-offset = 1
auto-increment-increment = 2
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.000007 |  629 | 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 A 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 Master B Database Server

scp testing.db root@192.168.31.85:/root/

Now Master B Database Server Configuration (192.168.31.85).

Go to Master B Database Server :--

vim /etc/my.cnf
server-id = 2
auto-increment-offset = 2
auto-increment-increment = 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

Restart Mysql Servive :--

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.000007', 
MASTER_LOG_POS=629;
slave start;
show slave status\G



Save this for Master A from Master Server B :--
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Now Go to Master A Database Server :--
mysql -uroot -p
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.12 sec)
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.31.85', MASTER_USER='surender', MASTER_PASSWORD='xyz@123', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
Go to Master A Database Server :--
mysql -uroot -p
create database bhandari;
use bhandari;
create table linux (c int);
INSERT INTO linux (c) VALUES (1);
SELECT * FROM linux;
Verify the Master B Database Server :--
mysql -uroot -p
use bhandari;
SELECT * FROM linux;
Now Create A Table in Master B Server :--
create table centos (c int);
INSERT INTO centos (c) VALUES (1);
SELECT * FROM centos;
Verify the Master A Database Server :--
use bhandari;
SELECT * FROM linux;



How to Configure Master and Master Replication setup in Centos 7 How to Configure Master and Master Replication setup in Centos 7 Reviewed by Unknown on August 26, 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!