Set Up MySQL Master-Master Replication

Here we 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: 10.0.10.12
Master-2: 10.0.10.18
Database: empmaster

Step 1. Set Up MySQL Master-1 Server

  • Edit MySQL configuration file and add the following lines under [mysqld] section.
  • Restart MySQL server to changes take effect.
  • Create an mysql account on Master-1 server with REPLICATION SLAVE privileges to which replication client will connect to master.
  • Block write statement on all the tables, so no changes can be made after taking backup.
  • Check the current binary log file name (File) and current offset (Position) value using following command.
# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog-do-db=empmaster
server-id=1
# service mysqld restart
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'secretpassword';
mysql> FLUSH PRIVILEGES;
mysql> use empmaster;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      332 | empmaster         |                  |
+------------------+----------+--------------+------------------+
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 is332. Note down these values to use on Master-2 server in next step.

  • Take a backup of database and copy it to another mysql server.
  • After completing backup remove the READ LOCK from tables, So that changes can be made.
  • Edit mysql Master-2 configuration file and add following values under [mysqld] section.
# mysqldump -u root -p empmaster > empmaster.sql
# scp empmaster.sql 10.0.10.18:/opt/
mysql> UNLOCK TABLES;

Step 2. Setup MySQL Master-2 Server

# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog-do-db=empmaster
server-id=1

server-id always be an non zero numeric value. These value will never be similar with other master or slave servers.

  • Restart MySQL server, If you had already configured replication use –skip-slave-start in start to not to immediate connect to master server.
  • Restore database backup taken from master server.
  • Create an mysql account on Master-1 server with REPLICATION SLAVE privileges to which replication client will connect to master.
  • Check the current binary log file name (File) and current offset (Position) value using following command.
# service mysqld restart
# mysql -u root -p empmaster < /opt/empmaster.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 | empmaster         |                  |
+------------------+----------+--------------+------------------+
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 is847. Note down these values to use in Step 3.

  • Setup option values on slave server using following command.
mysql>  CHANGE MASTER TO MASTER_HOST='10.0.10.12',
    -> 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='10.0.10.18',
     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.

Related Posts

  • 60
    Some times MySQL replication creates problems and slave could not sync properly from master. It may cause with lots of reason. Here is a way to fix it.   Warning: After using this tutorial, All of your bin-log files will be deleted, So if you want, you may take a backup…
    Tags: slave, mysql, server, master, replication
  • 60
    Here we will help you to setup master-slave replication between MySQL servers. Setup Details: Master Server: 10.0.10.12 Slave  Server: 10.0.10.18 Database: empmaster 1. Setup MySQL Master Server Create an mysql account on Master server with REPLICATION SLAVE privileges to which replication client will connect to master. Block write statement on…
    Tags: mysql, slave, server, master, replication, file
  • 48
    Once replication has been started it should execute without requiring much regular administration. Depending on your replication environment, you will want to check the replication status of each slave periodically, daily, or even more frequently. Checking Replication Status: The most common task when managing a replication process is to ensure…
    Tags: slave, master, replication, mysql
  • 38
    To enable the query log, put this in /etc/my.cnf in the [mysqld] section log = /path/to/query.log #works for mysql < 5.1.29 Also, to enable it from MySQL console SET general_log = 1; Remember that this logfile can grow very big on a busy server. update: With mysql 5.1.29+ , the log option is deprecated. To specify…
    Tags: mysql, set, server, file
  • 38
    90% of the time, this error is due either to the MySQL Server not running, or else firewall configuration on the Windows server blocking access on port 3306 (or whatever port your MySQL instance is configured to use). Error: 2003 (CR_CONN_HOST_ERROR) Message: Can't connect to MySQL server on '%s' (%d)
    Tags: mysql, server

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: