MySQL Master Master Replication Tutorial

Source: http://www.howtoforge.com/mysql_master_master_replication

This tutorial describes how to set up MySQL master-master replication. We need to replicate MySQL servers to achieve high-availability (HA). In my case I need two masters that are synchronized with each other so that if one of them drops down, other could take over and no data is lost. Similarly when the first one goes up again, it will still be used as slave for the live one.

Here  is a basic step by step tutorial, that will cover the mysql master and slave replication and also will describe the mysql master and master replication.

Notions: we will call system 1 as master1 and slave2 and system2 as master2 and slave 1.

Step 1:

Install mysql on master 1 and slave 1. configure network services on both system, like 

Master 1/Slave 2 ip: 192.168.16.4

Master 2/Slave 1 ip : 192.168.16.5 

Step 2:

On master 1, make changes in my.cnf:

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

log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql              # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=1

[mysql.server]
user=mysql
basedir=/var/lib


[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 3:

On master 1, create a replication slave account in mysql.

mysql> grant replication slave on *.* to 'replication'@192.168.16.5 \
identified by 'slave';

and restart the mysql master1. 

Step 4:

Now edit my.cnf on Slave1 or Master2 :

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

server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 Step 5:

Restart mysql slave 1 and at

mysql> start slave;
mysql> show slave status\G;

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.16.4

                Master_User: replica

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: MASTERMYSQL01-bin.000009

        Read_Master_Log_Pos: 4

             Relay_Log_File: MASTERMYSQL02-relay-bin.000015

              Relay_Log_Pos: 3630

      Relay_Master_Log_File: MASTERMYSQL01-bin.000009

           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: 4

            Relay_Log_Space: 3630

            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: 1519187

1 row in set (0.00 sec)

Above highlighted rows must be indicate related log files and  Slave_IO_Running and   Slave_SQL_Running: must be to YES.

Step 6:

On master 1:

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|MysqlMYSQL01-bin.000008 |      410 | adam         |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.

Step 7:

On Master2/Slave 1, edit my.cnf and master entries into it:

 [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

log-bin                     #information for becoming master added
binlog-do-db=adam

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 8:

Create a replication slave account on master2 for master1:

mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave2';

Step 9:

Edit my.cnf on master1 for information of its master.

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

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test

server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306

[mysql.server]user=mysqlbasedir=/var/lib 

Step 10:

Restart both mysql master1 and master2.

On mysql master1:

mysql> start slave;

On mysql master2: 

mysql > show master status;

On mysql master 1:

mysql> show slave status\G;

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.16.5
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: Mysql1MYSQL02-bin.000008
        Read_Master_Log_Pos: 410
             Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
              Relay_Log_Pos: 445
      Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
           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: 410
            Relay_Log_Space: 445
            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: 103799
1 row in set (0.00 sec)

ERROR:
No query specified

Check for the hightlighted rows, make sure its running. Now you can create tables in the database and you will see changes in slave. Enjoy!!