Creating mysql master master replication


In order to create Master Master sql replication, Install the mysql server on both of the server and follow the following instructions;

Server 1

 

  • Edit /etc/mysql/my.cnf
  • Move to the section where it contains [mysqld] and add the following lines
    • server-id=1
      replicate-same-server-id = 0
      auto-increment-increment = 2
      auto-increment-offset = 1master-host=192.168.0.189 (ip address of server 2)
      master-user=rep_user
      master-password=rep_passwd
      master-port=3306
      log-bin

 

  • Grant access to rep_user on database, by issuing following command
    •  grant replication slave on *.* to ‘rep_user’@’%’ identified by ‘rep_passwd’;
  •             Some useful commands  show master status;
                                                   start slave;
                                                   show master status\G;
                                                   show slave status\G;
                                                   show master logs; 

SERVER 2

 

  • Edit /etc/mysql/my.cnf
  • Move to the section where it contains [mysqld] and add the following lines
    • server-id=2
      replicate-same-server-id = 0
      auto-increment-increment = 2
      auto-increment-offset = 1master-host=192.168.0.190 (ip address of server 1)
      master-user=rep_user
      master-password=rep_passwd
      master-port=3306
      log-bin

 

  • Grant access to rep_user on database, by issuing following command
    •  grant replication slave on *.* to ‘rep_user’@’%’ identified by ‘rep_passwd’;

 

 

Restart sql server in both server and under mysql cli run command “start slave” if slave is not started by default.
It should now be running in master master replication.

Comments are closed.