Our Blog

Latest news and updates from flathost

STEP FOR MYSQL MASTER TO MASTER REPLICATION

Posted by shazim on 16 05 2019. 0 Comments

  • 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;

 

 

Leave a Reply

Your email address will not be published.