MySQL Master/Master Config

This is a HOWTO for setting up a Master/Master MySQL configuration. This can provide a level of fault tolerance with a hot standby, load balancing, or even high availability fault tolerance can be achived with the addition of keepalive or something similar.


#Master 1/Slave 2 ip: 192.168.1.2 (ServerA)
#Master 2/Slave 1 ip : 192.168.1.3 (ServerB)

#Step 1
#On Master 1 (ServerA), make changes in my.cnf:
-----------------
[mysqld]
datadir=/d2/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
bind-address = 192.168.1.2 #enable tcp access
server-id=1 #server id

log-bin=/d2/mysql/db1-bin-log #Where to store the bin logs for replication TO ServerB
log-bin-index=/d2/mysql/db1-bin-log.index

binlog-do-db=redmine1 #DB to replicate
binlog-ignore-db=mysql #DB's not to replicate
binlog-ignore-db=test

master-host = 192.168.1.3 #Set Master info for ServerA
master-user = replication
master-password = *****************
master-port = 3306

relay-log=/d2/mysql/db1-relay-log #where to store the relay logs for replication FROM ServerB
relay-log-index=/d2/mysql/db1-relay-log.index

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

#Step 2 (granting access to replcation users on both boxes)
#On master 1 (ServerA), create a replication slave account on master1 for master2:
mysql -u root -p
mysql> grant replication slave on *.* to 'replication'@'192.168.1.3' identified by '**************';

#Create a replication slave account on master2(ServerB) for master1:
mysql -u root -p
mysql> grant replication slave on *.* to 'replication'@192.168.1.2 identified by '****************';

#Step 3
#Now edit my.cnf on Slave1 or Master2 (ServerB):
--------------------
[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
bind-address = 192.168.1.3
server-id=2

log-bin=/var/lib/mysql/db2-bin-log
log-bin-index=/var/lib/mysql/db2-bin-log.index

binlog-do-db=redmine1
binlog-ignore-db=mysql
binlog-ignore-db=test

master-host = 192.168.1.2
master-user = replication
master-password = *****************
master-port = 3306

relay-log=/var/lib/mysql/db2-relay-log
relay-log-index=/var/lib/mysql/db2-relay-log.index

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

#Step 4
#Restart mysqld on both servers.

sudo /etc/init.d/mysqld restart

#Step 5
#Start slave 1 and slave 2 (both servers)

mysql -u root -p
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event #Make sure this isn't blank
Master_Host: 192.168.1.2
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db1-bin-log.000014
Read_Master_Log_Pos: 404
Relay_Log_File: db2-relay-log.000029
Relay_Log_Pos: 543
Relay_Master_Log_File: db1-bin-log.000014
Slave_IO_Running: Yes #Make sure this is yes
Slave_SQL_Running: Yes #Make sure this is 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: 404
Relay_Log_Space: 543
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)

ERROR:
No query specified

#Step 6
#Check on master status (both boxes):
mysql> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| db2-bin-log.000001 | 1214 | redmine1 | mysql,test |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)