MYSQL Clustering

MYSQL Master-Master Replication

Step 1:

Master 1/Slave 2 ip:

Master 2/Slave 1 ip :  

Make sure both the server binlog_format is set to mixed  in my.cnf 

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=Customdb  # input the database which should be replicated
binlog-do-db=portal # input the database which should be replicated
binlog-do-db=documentlibrary  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test

Must be  Unique  on the  all the servers
server-id=1

Step 3:

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

mysql> grant replication slave on *.* to 'replication'@ \
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 =
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:
                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 | portal        |                  |
+------------------------+----------+--------------+------------------+
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 =
master-user = replication
master-password = slave
master-port = 3306

log-bin
binlog-do-db=Customdb  # input the database which should be replicated
binlog-do-db=portal # input the database which should be replicated
binlog-do-db=documentlibrary  # input the database which should be replicated
binlog-ignore-db=mysql     # input the database that should be ignored for replication
binlog-ignore-db=test

Step 8:

Create a replication slave account on master2 for master1:

mysql> grant replication slave on *.* to 'replication'@ 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=Customdb  # input the database which should be replicated
binlog-do-db=portal # input the database which should be replicated
binlog-do-db=documentlibrary  # 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
#information for becoming slave.
master-host =
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:
                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.