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