STEP FOR MYSQL MASTER TO MASTER REPLICATION

• First, install MariaDB on both the centos 7 based servers.

• Command:

#sudo yum install mariadb-server

#sudo systemctl start mariadb

#sudo systemctl enable mariadb

#sudo systemctl status mariadb

#sudo mysql_secure_installation(press enter for the root password for the first time when it asks and then provide your own password to mysql)

• machines to explain the procedure.

Master1  IP : 192.168.1.25

Master2  IP : 192.168.1.26

• Configuration Procedure:

• Master1

• In master1 terminal, check the status of MariaDB

#sudo systemctl status mariadb

• Open my.cnf file and add the following statement to the file. Save and exit from the file. 

#vi /etc/my.cnf

server-id=10

log-bin=mysql-bin

• Restart the MariaDB service by running the following command.

#sudo systemctl restart mariadb

• After login to mysql we are going to create user

#mysql –u root –p

#MariaDB [Linux]> create user ‘reply’@’%’ identified by ‘12345’;(here reply is a user and 12345 is password)

#MariaDB [Linux]> grant replication slave on *.* to ‘reply’@’%’ identified by ‘12345’;

#MariaDB [Linux]> flush privileges;

#MariaDB [Linux]> flush tables with read lock;

#MariaDB [Linux]> show master status;   (it will show you the master log file and master position)

#MariaDB [Linux]>exit

• Take the MySQL dump from master1 terminal and execute the following command

#mysqldump mysql –u root -p> mysql-db.sql

#rsync -Pavzxl mysql-db.sql [email protected]:/root/

• Master2

• In master 2 terminal, check the status of MariaDB

#sudo systemctl status mariadb

• Open the my.cnf file and add the following statement to the file. Save and exit from the file.

#vi /etc/my.cnf

server-id=20

log-bin=mysql-bin

• Restart the MariaDB service by running the following command. 

#systemctl restart mariadb.service

• Now inject the MySQL dump file into master2 database.

#mysql mysql -u root -p < mysql-db.sql

• After dumping, open the MySQL shell and do the replication configuration as shown below.

#mysql –u root –p

#MariaDB [Linux]> stop slave;

#MariaDB [Linux]> change master to master_host=’192.168.1.25′, master_user=’reply’, master_password=’12345′, master_log_file=’mysql-bin.000002′, master_log_pos=566;  (check the master log file and master log position of master1)

#MariaDB [Linux]> start slave;

#MariaDB [Linux]> show processlist;

• Check the slave status by using the command as shown below. 

#MariaDB [Linux]> show slave status;

#MariaDB [Linux]> exit

• Restart the MariaDB service by running the following command. 

#systemctl restart mariadb.service

• Go to the master and check the master status using the command as shown below

# mysql –u root –p

#MariaDB [Linux]> show master status;   (it will show you the master log file and master position)

Master1

• In the master terminal, check the established connection using netstat command.

# netstat -natp | egrep -i established.*mysql

• Open the MySQL and check the process list and also configure the replication settings

#mysql –u root –p

#MariaDB [Linux]> unlock tables;

#MariaDB [Linux]> show processlist;

#MariaDB [Linux]> stop slave;

#MariaDB [Linux]> change master to master_host=’192.168.1.26′,

master_user=’reply’,master_password=’12345′, master_log_file=’mysql-bin.000004′, master_log_pos=245;

#MariaDB [Linux]> start slave;

#MariaDB [Linux]> show slave status;

• Replication configuration is done. now Checking the Replication process

Master1

• I have created one database named as Linux

          #MariaDB [(none)]> create database Linux;

#MariaDB [(none)]> use Linux;

• Create a table for the newly created database.

# MariaDB [Linux]> create table Distribution(Distro varchar(25) NOT NULL);

• Insert some values into newly created table.

# MariaDB [Linux]> insert into Distribution values(‘REDHAT’);

• The table and the database has been created successfully. List the table from the database.

# MariaDB [Linux]> select * from Distribution;

Master2

• In the master 2, login to MySQL and list the databases. 

# MariaDB [(none)]> show databases;

• Now the database which is created in master1 is replicated into master2. Use the database and list the table.

# MariaDB [(none)]>  use Linux ;

 # MariaDB [Linux]> show tables;

• Select and list the table from the selected database. 

# MariaDB [Linux]> select * from Distribution;

• Insert some values into that table and list the table’s values the updated values is shown. Now the replication between master-master is working successfully. 

# MariaDB [Linux]> insert into Distribution values(‘Ubuntu’);

# MariaDB [Linux]> select * from Distribution;

Master1

• Again goto master1 terminal, now select and list the tables to check the replication process. If the updated value from the master 2 is shown, then the replication process is done.

#mysql –u root –p

# MariaDB [Linux]> select * from Distribution;

To know more visit Ideastack.

Last Edited: 06/05/2020