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.