Wednesday, May 28, 2008

MySQL Replication

MySQL DataBase Master-Master Replication


Required Packages

mysql
mysql-server
mysql-devel


Master1 server ip: 192.168.0.82

Master2 server ip: 192.168.0.83

Slave username: user

Slave password: user

Your data directory is: /var/lib/mysql/


In Master1 Database machine edit /etc/my.cnf :


# let's make it so auto increment columns behave by having different increments on both servers

auto_increment_increment=2

auto_increment_offset=1

# Replication Master Server

# binary logging is required for replication

log-bin=/var/log/master1-bin

binlog-ignore-db=mysql

binlog-ignore-db=test

# required unique id between 1 and 2^32 - 1

server-id = 1

#following is the slave settings so this server can connect to master2

master-host = 192.168.0.83

master-user = slaveuser

master-password = slavepw

master-port = 3306


Save and exit.



In Master2 Database machine edit /etc/my.cnf :


# let's make it so auto increment columns behave by having different increments on both servers

auto_increment_increment=2

auto_increment_offset=2

# Replication Master Server

# binary logging is required for replication

log-bin=/var/log/master2-bin

#Ignore database to replicate

binlog-ignore-db=mysql

binlog-ignore-db=test

# required unique id between 1 and 2^32 - 1

server-id = 2

#following is the slave settings so this server can connect to master1

master-host = 192.168.0.82

master-user = user

master-password = user

master-port = 3306


Save and exit.



Enter the following command on Master1 to create/grant user level access on the database to Master2.


mysql> grant replication slave on *.* to slaveuser@'192.168.0.83' identified by 'slavepw';


mysql>FLUSH PRIVILEGES;


Now, enter the following command on Master2 for create/grant user level access on the database to Master1.


mysql> grant replication slave on *.* to user@'192.168.0.82' identified by 'user';


mysql>FLUSH PRIVILEGES;


Now make slave both machines to each other.


On Master1 enter the following:

mysql> show master status;

+----------------------+----------+--------------+-----------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+----------------------+----------+--------------+-----------------------+

| mysql-bin-log.000017 | 289 | | mysql,test,mysql,test |

+----------------------+----------+--------------+-----------------------+

1 row in set (0.00 sec)


NOTE:This mysql-bin-log file used to read data by the slave servers to replicate Database. Copy file name and position i.e; (289).




Now make Master2 the slave of Master1.


Enter the following command on Master2:


mysql> stop slave;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.82', MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='mysql-bin-log.0000017', MASTER_LOG_POS=98;

mysql>start slave;

mysql>show slave status\G;

It will show you something inside the output:

Master_Log_File: mysql-bin-log.000017



Read_Master_Log_Pos: 289



Relay_Log_File: localhost-relay-bin.000026



Relay_Log_Pos: 239



Relay_Master_Log_File: mysql-bin-log.000017



|

|

Seconds_Behind_Master: 0

If it is showing Seconds_Behind_Master not “NULL” then this slave is working fine.



Then for making Master1 slave to Master2, we need Master2 machine's mysql-bin-log and position, for that run the following command for required information:


mysql> show master status;


+----------------------+----------+--------------+-----------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+----------------------+----------+--------------+-----------------------+

| mysql-bin-log.000002 | 574 | | mysql,test,mysql,test |

+----------------------+----------+--------------+-----------------------+


1 row in set (0.00 sec)


NOTE:This mysql-bin-log file used to read data by the slave servers to replicate database. Copy file name and position i.e; (574).




Now make Master1 the slave of Master2


Enter the following command on Master1:


mysql> stop slave;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.83', MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='mysql-bin-log.000002', MASTER_LOG_POS=98;

mysql> start slave;

mysql>show slave status\G;

It will show you something inside the output:


Master_Log_File: mysql-bin-log.000002

Read_Master_Log_Pos: 574

Relay_Log_File: mysqld-relay-bin.000003

Relay_Log_Pos: 239

Relay_Master_Log_File: mysql-bin-log.000002

|

|

Seconds_Behind_Master: 0


If it is showing Seconds_Behind_Master not “NULL” then this slave is working fine.


And now do some testing like create/delete database and tables on one machine and check on other, is it showing the changes???? if both are showing same data then that means your replication is working fine.