08704 866 855

MySQL Master Master Replication

Posted On: 8th February 2012 under linux

MySQL master master replication is a great way to replicate your database on 2 different load balanced or active/standby servers. This is a great solution for high availability databases.

When you have master master replication in place you can read and write to 2 or more MySQL databases and have the changes replicated throughout the set. Unlike master slave replication where you can only update the master.

Master master replication is more or less identical to setting up a master slave pair, except you then follow the same procedure in reverse.

First off you need to create 2 database instances with identical data sets. The method you will use to do this varies depending on the MySQL engine you have chosen to use. In our case we are working with the MyISAM engine so locking the tables with read lock and running mysqlhotcopy is fine for our purposes.

Once you have 2 identical copies on 2 different machines and its not possible that either one can be updated you can begin to configure the replication.

First off you need to configure each server with an id:

On server number 1 edit the /etc/my.cnf file and add the server id in the [mysqld] section

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1

log-bin
binlog-do-db=theserve  #this is the database that you want to replicate
binlog-ignore-db=mysql # this is a database that you don't want to replicate

server-id=1

On server number 2 do the same but change the ID and add the master information

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1

server-id=2
master-host = *ip.of.server1*
master-user = replication
master-password = slave
master-port = 3306
log-bin
binlog-do-db=theserve  # this is the database that you want to replicate
binlog-ignore-db=mysql # this is a database that you don't want to replicate

Now you need to grant replication slave on each of the servers

Server 1 mysql cli:

mysql> grant replication slave on *.* to 'replication'@*ip.of.server2* identified by 'slave';

Server 2 mysql cli:

mysql> grant replication slave on *.* to 'replication'@*ip.of.server1* identified by 'slave';

Restart both MySQL instances.

Now you want to start the slave on Server 2:

mysql> start slave;
mysql> show slave status;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.x
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqld-bin.000038
        Read_Master_Log_Pos: 119997
             Relay_Log_File: mysqld-relay-bin.000089
              Relay_Log_Pos: 90664
      Relay_Master_Log_File: mysqld-bin.000038
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        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: 119997
            Relay_Log_Space: 90664
            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)

Make sure that both the Slave_IO_Running and Slave_SQL_Running say Yes. If they both say yes then you have succesfully setup Slave replication from Server 1 to Server 2.

Now all we need to do is set Server 1 as a slave of Server 2.

First edit the /etc/my.cnf file of Server 1 and add the master info to the [mysqld] section as follows:

master-host = *ip.of.server2*
master-user = replication
master-password = slave
master-port = 3306

Now restart mysql on Server 1 and login to the mysql cli and start the slave:

mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.223
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqld-bin.000001
        Read_Master_Log_Pos: 344
             Relay_Log_File: mysqld-relay-bin.000180
              Relay_Log_Pos: 236
      Relay_Master_Log_File: mysqld-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        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: 344
            Relay_Log_Space: 236
            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)

If you see that Slave_IO and Slave_SQL both say yes then you have now configured master master replication and can write to either database and it will replicate accross.

pay with credit cardscreditCardspay with paypal