MySQL master-master replication and auto-increment indexes

Hi Friends,

Recently I implemented mysql maste-master replication along with Heartbeat for high availability for our monitoring tool Zabbix.

So In this blog I am sharing master-master replication. I will discuss Hartbeat in my next blogs.

Please check below steps:

Step 1) Master 2 changes to /etc/my.cnf:

1.  [mysqld]

2.  datadir=/var/lib/mysql

3.  socket=/var/lib/mysql/mysql.sock

4.  old_passwords=1

5.   

6.  log-bin

7.  binlog-do-db=zabbix  # input the database which should be replicated

8.  binlog-ignore-db=mysql            # input the database that should be ignored for replication

9.  binlog-ignore-db=test

10.  

11. server-id=2

12. auto_increment_increment= 2 #For Auto-increment

13. auto_increment_offset   = 2

14.  

15. [mysql.server]

16. user=mysql

17. basedir=/var/lib

18.  

19.  

20. [mysqld_safe]

21. err-log=/var/log/mysqld.log

22. pid-file=/var/run/mysqld/mysqld.pid

Step 2) Master 1 changes to /etc/my.cnf:

1.  [mysqld]

2.  datadir=/var/lib/mysql

3.  socket=/var/lib/mysql/mysql.sock

4.  old_passwords=1

5.   

6.  log-bin

7.  binlog-do-db=zabbix  # input the database which should be replicated

8.  binlog-ignore-db=mysql            # input the database that should be ignored for replication

9.  binlog-ignore-db=test

10. server-id=1

11. auto_increment_increment= 2 #For auto-increments

12. auto_increment_offset   = 1

13.  

14. [mysql.server]

15. user=mysql

16. basedir=/var/lib

17.  

18. [mysqld_safe]

19. err-log=/var/log/mysqld.log

20. pid-file=/var/run/mysqld/mysqld.pid

Step 3) Restarting Mysql on both servers

#Service mysql restart

Step 4) Create a replication slave account on Master 2 for Master 1

mysql> grant replication slave on zabbix.* to 'zabbix'@’%’ identified by 'password';

And start master:

1.  mysql> start master;

Step 5) On master 2

1.  mysql> show master status;

2.  +------------------------+----------+--------------+------------------+

3.  | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |

4.  +------------------------+----------+--------------+------------------+

5.  |mysqld-bin.000012       |      106 | zabbix            |     mysql,test   |

6.  +------------------------+----------+--------------+------------------+

7.  1 row in set (0.00 sec)

Step 6) On master 1:

1.  mysql> stop slave;

2.  mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.5', MASTER_USER='zabbix', MASTER_PASSWORD='password', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000012', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;

3.  mysql> start slave;

Step 7) On master 1

8.  mysql> show master status;

9.  +------------------------+----------+--------------+------------------+

10. | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |

11. +------------------------+----------+--------------+------------------+

12. |mysqld-bin.000015       |      106 | zabbix            |     mysql,test   |

13. +------------------------+----------+--------------+------------------+

14. 1 row in set (0.00 sec)

Step 8) On master 2:

4.  mysql> stop slave;

5.  mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.4', MASTER_USER='zabbix', MASTER_PASSWORD='password', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000015', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;

6.  mysql> start slave;

Note: You could check slave status from masters by executing below command

mysql>show slave status\G;