MySQL Master-Master Replication on CentOS


In this post i want to describe step installation of MySQL Master-Master Replication.

in this case for example:

Master 1 IP Address : 192.168.1.100
Master 2 IP Address: 192.168.2.200
Database to replicate: db1, db2, and db3

1. We need to install MySQL and dependencies on both server (master1 and master2)

#yum install mysql mysql-server

set mysql password because default password is null

#mysqladmin -u root password new_mysql_password

2. Create a user with replication privileges on both server:

On master 1:

#mysql -u root -p
Enter Password:
mysql> CREATE USER 'slave_user'@'192.168.2.200' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.2.200' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

Reload your privileges;
mysql> FLUSH PRIVILEGES;

Do the same with master 2 with master 1 hosts:
#mysql -u root -p
Enter Password:
mysql> CREATE USER 'slave_user'@'192.168.1.100' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.1.100' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

Reload your privileges:
mysql> FLUSH PRIVILEGES;

3. Configure MySQL nodes

On master 1 make changes my.cnf configuration:

Comment this line:
#bind-address = 127.0.01

or change the ip address without comment the line:
bind-address = 0.0.0.0

add this line below on master 1 (server-id=1):


server-id = 1

master-host = 192.168.2.200
master-user = slave_user
master-password = password
master-connect-retry = 30
slave-net-timeout = 30
replicate-do-db = db1
replicate-do-db = db2
replicate-do-db = db3

log-bin = /var/log/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/master-log-bin.index
binlog-do-db = db1
binlog-do-db = db2
binlog-do-db = db3

relay-log = /var/lib/mysqld-relay-bin
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

Do the same with master 2 (server-id=2):

server-id = 2

master-host = 192.168.1.100
master-user = slave_user
master-password = password
master-connect-retry = 30
slave-net-timeout = 30
replicate-do-db = db1
replicate-do-db = db2
replicate-do-db = db3

log-bin = /var/log/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/master-log-bin.index

binlog-do-db = db1
binlog-do-db = db2
binlog-do-db = db3

relay-log = /var/lib/mysqld-relay-bin
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

now restart your mysql service

#service mysqld restart
Stopping MySQL:                                            [  OK  ]
Starting MySQL:                                             [  OK  ]

4. Slaves Configuration:

Enter your MySQL shell:
#mysql -u root -p

Unlock your db tables with read lock on both server:
mysql>USE db1;
mysql>UNLOCK TABLES;
mysql>FLUSH TABLES WITH READ LOCK;

Setup slave replication on master 1:
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.2.200', MASTER_USER = 'slave_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS=98;

Setup slave replication on master 2:
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.1.100', MASTER_USER = 'slave_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS=158;

Now, start your replication on both server:
mysql> SLAVES START;

Check your slave states and your configuration:
mysql>SHOW SLAVE STATUS\G

If your configuration running well, output must be like this for example:

mysql> show slave status\G
************************ 1. row *************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.200
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: slave-relay.000001
Relay_Log_Pos: 198
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1,db2,db3
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1581
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Check your master replication:
mysql>SHOW MASTER STATUS;
+------------------+----------+----------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+----------------------+-------------------+
| mysql-bin.000006 | 98 | radius,smbind,Syslog | |
+------------------+----------+----------------------+-------------------+
1 row in set (0.00 sec)

Load database from master:
mysql> LOAD DATA FROM MASTER;

That’s all…your MySQL database will be replicate on both side of servers. to make sure your configuration running with no errors please check your log when you do the start or stop slaves command.

#tail -f /var/log/messages

Download this guide in PDF

18 thoughts on “MySQL Master-Master Replication on CentOS

  1. vidhiasagarar July 23, 2011 at 2:52 PM Reply

    Hi There is a mistake in the mysql quere
    CHANGE MASTER TO MASTER_HOST = ‘192.168.1.100’, MASTER_HOST = ‘slave_user’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.000001’, MASTER_LOG_POS=158;

    it should be:-
    CHANGE MASTER TO MASTER_HOST = ‘192.168.1.100’, MASTER_USER = ‘slave_user’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.000001’, MASTER_LOG_POS=158;

  2. gembuls July 24, 2011 at 2:46 PM Reply

    Hi Vidhiasagarar,

    Yes you’re right! Thank you for correcting, Seems like I mistyped in this section. thanks for coming

  3. Tapu February 24, 2012 at 4:35 PM Reply

    How are you?

  4. Ngộ Thật Đấy December 31, 2012 at 11:48 PM Reply

    I have 3 servers, please help me to configure.

  5. laxman January 14, 2013 at 6:45 PM Reply

    Hi i am getting following error
    mysql> MASTER_PASSWORD = ‘slave’, MASTER_LOG_FILE = ‘mysqld-bin.000001’, MASTER_LOG_POS=98;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘MASTER_PASSWORD = ‘slave’, MASTER_LOG_FILE = ‘mysqld-bin.000001′, MASTER_LOG_POS’ at line 1

    • gembuls January 14, 2013 at 9:42 PM Reply

      You need to write with correct syntax on one line command:
      mysql> CHANGE MASTER TO MASTER_HOST = ‘192.168.2.200’, MASTER_USER = ‘slave_user’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.000001’, MASTER_LOG_POS=98;

  6. laxman January 17, 2013 at 12:17 AM Reply

    server1 ip =192.168.1.27
    server2 ip =192.168.1.98

    on server 1 i issued below commnad and try to start slave but i m getting error.
    ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

    mysql> CHANGE MASTER TO MASTER_HOST = ’192.168.1.98′, MASTER_USER = ‘slave_user’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.000001′, MASTER_LOG_POS=98;

    • gembuls January 28, 2013 at 8:15 PM Reply

      please review your MySQL master-slave config on my.cnf files

  7. Shital Prajapati February 27, 2013 at 7:24 PM Reply

    in my.conf i have to add three lines for relay-log…
    but in my path /var/lib/mysql/ i dont find anything
    what should i do to get those log file????
    and i am also getting error in /var/log/mysqld.log

    130227 17:47:12 [Warning] Neither –relay-log nor –relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use ‘–relay-log=mysqld-relay-bin’ to avoid this problem.

    plz help me out and reply soon

  8. gembuls March 1, 2013 at 2:37 PM Reply

    Hi Shital,
    are you sure if /var/lib/mysql is the correct path for ‘relay-log’ files?
    from that log, it describes that mysql can not reach mysql-relay-bin files

    Try to restart MySQL services and look at output from this command:
    # tail -f /var/log/mysql/mysql-bin.log
    # tail -f /var/log/mysqld.log

  9. Shital Prajapati March 1, 2013 at 5:10 PM Reply

    I tried your all steps again.
    now i m getting this error

    mysql> CHANGE MASTER TO MASTER_HOST = ‘192.168.0.81’, MASTER_USER = ‘slave_user1’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.000001’, MASTER_LOG_POS=158;
    ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

    on the second master configuration i m getting this problem.
    plz help me…

  10. Nờ July 21, 2013 at 4:56 PM Reply

    How i can replicat ALL my database not just db1 db2 db3? Thanks U!

    • gembuls July 22, 2013 at 2:57 PM Reply

      if you have implemented this replication scenario it will replicate the database that you define on my.cnf file.

  11. Shiva March 13, 2014 at 11:59 AM Reply

    Dear Friends,

    I have small doubt..db1,db2,db3 these three databases are belongs to two severs on only on server…( I felt two server). If i am wrong please correct me.

    Thanks & Regards,

    Shivakumar

    • gembuls March 13, 2014 at 1:38 PM Reply

      Hi shiva,

      In this topology, There are three databases are belongs on two database servers (master1 & master2). if you put each of database on physical machine the answer is two, and if you put on virtual machine the answer is can be on one physical machine.

  12. Murad October 18, 2015 at 6:36 PM Reply

    I configured master 2 as described above but getting error during mysql start

    any advise

    151010 05:13:05 mysqld ended

    151010 05:13:06 mysqld started
    151010 5:13:06 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use ‘CHANGE MASTER’ instead.
    /usr/libexec/mysqld: File ‘/var/log/mysql/master-log-bin.index’ not found (Errcode: 13)
    151010 5:13:06 [ERROR] Aborting

    151010 5:13:06 [Note] /usr/libexec/mysqld: Shutdown complete

    151010 05:13:06 mysqld ended

    My /etc/my.cnf

    server-id = 2

    master-host = 192.168.30.201
    master-user = slave_user
    master-password = password
    master-connect-retry = 30
    slave-net-timeout = 30
    replicate-do-db = stitel

    log-bin = /var/log/mysql/mysql-bin.log
    log-bin-index = /var/log/mysql/master-log-bin.index

    binlog-do-db = stitel

    relay-log = /var/lib/mysqld-relay-bin
    relay-log = /var/lib/mysql/slave-relay.log
    relay-log-index = /var/lib/mysql/slave-relay-log.index

    • Arif Romansyah October 19, 2015 at 11:47 AM Reply

      Based on log, mysqld services was unable to start


      151010 5:13:06 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use ‘CHANGE MASTER’ instead.
      /usr/libexec/mysqld: File ‘/var/log/mysql/master-log-bin.index’ not found (Errcode: 13)

      Please fix on this line first:
      master cnf:
      log-bin-index = /var/log/mysql/master-log-bin.index
      slave cnf:
      relay-log-index = /var/lib/mysql/slave-relay-log.index

  13. Murad January 24, 2016 at 5:03 PM Reply

    Hi Arif
    Thanks for your awesome tutorial I have got a simple quarries we have got 4 linux mysql DB.. Can I configure it as master-master-master-master for all 4 Mysql DB?
    What about following parameter do I need it in mysql config file
    [mysqld]
    auto_increment_increment = 10
    auto_increment_offset = 2
    Any help will be appreciated!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: