MySQL Master Master ReplicationMySQL 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.