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 the 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 a 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 the master 2 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=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 the 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 process list;
• 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 the netstat command.
# netstat -natp | egrep -i established.*mysql
• Open 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 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 the 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 master 2, login to MySQL and list the databases.
# MariaDB [(none)]> show databases;
• Now the database which is created in master1 is replicated in 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 are shown. Now the replication between master-master is working successfully.
# MariaDB [Linux]> insert into Distribution values (‘Ubuntu’);
# MariaDB [Linux]> select * from Distribution;
Master1
• Again go to the master1 terminal, now select and list the tables to check the replication process. If the updated value from master 2 is shown, then the replication process is done.
#mysql –u root –p
# MariaDB [Linux]> select * from Distribution;
Conclusion
Still confused how to step for MySQL master to master replication. Contact Ideastack now.
Frequently Asked Questions
Q1. How does master master replication work in MySQL?
Replication relies on three threads per master/slave connection: one on the master and two on the slaves. The slave server begins this thread when you issue START SLAVE, and it connects to the master and requests a copy of the master’s binary log.
Q2. What are the three replication strategies?
- Strategy 1: Log-Based Data Replication.
- Strategy 2: Full Table Data Replication.
- Strategy 3: Key-Based Incremental Data Replication.
Q3. What are the two types of replications?
There are two types of replication exist: direct and conceptual.