How to Set Up MySQL Master-Master Replication
Abstract: The above output is showing that the current binary file is using mysql-bin.000001 and offset value is 847. Note down these values to use in Step 3. S
MySQL Master-Slave replication is to set up slave server to update immediately as soon as changes done in Master server. But 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.
Setup Details:Master-1: 192.168.1.15 Master-2: 192.168.1.16 Database: mydb
If you are using different – different versions of MySQL on either servers use this link to check compatibility.
Step 1. Set Up MySQL Master-1 Server- Edit MySQL configuration file and add the following lines under [mysqld] section.
# vim /etc/my.cnf
[mysqld] log-bin=mysql-bin binlog-do-db=mydb server-id=1
# service mysqld restart
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'secretpassword'; mysql> FLUSH PRIVILEGES;
mysql> use mydb; mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 332 | mydb | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
The above output is showing that the current binary file is using mysql-bin.000003 and offset value is 332. Note down these values to use on Master-2 server in next step.
# mysqldump -u root -p mydb > mydb.sql # scp mydb.sql 192.168.1.16:/opt/
mysql> UNLOCK TABLES;Step 2. Setup MySQL Master-2 Server
# vim /etc/my.cnf
[mysqld] log-bin=mysql-bin binlog-do-db=mydb server-id=1
server-id always be an non zero numeric value. These value will never be similar with other master or slave servers.
# service mysqld restart
# mysql -u root -p mydb < /opt/mydb.sql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'secretpassword'; mysql> FLUSH PRIVILEGES;
mysql > SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 847 | mydb | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
The above output is showing that the current binary file is using mysql-bin.000001 and offset value is 847. Note down these values to use in Step 3.
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.15', -> MASTER_USER='repl_user', -> MASTER_PASSWORD='secretpassword', -> MASTER_LOG_FILE='mysql-bin.000003', -> MASTER_LOG_POS=332;Step 3: Complete Setup on MySQL Master-1
Login to MySQL Master-1 server and execute following command.
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.16', MASTER_USER='repl_user', MASTER_PASSWORD='secretpassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=847;Step 4: Start SLAVE on Both Servers
Execute following command on both servers to start replication slave process.
mysql> SLAVE START;
MySQL Master-Master Replication has been configured successfully on your system and in working mode. To test if replication is working make changes on either server and check if changes are reflecting on other server.
Thanks for using this tutorial, If it’s helpful for you then please do not forgot to share it with your friends.