Master-Master replication: Two database systems at two different locations can be configured to run in synchronization with each other using Mysql Master-Master replication. Any changes on DB1 at Loc1 can be updated at realtime on DB2 at Loc2 and vice versa using Mysql’s built in replication feature.
Following are steps used to setup a Master-Slave replication :-
1. Assumptions:
- CentOS 5.2
- Mysql 5.0
-
- DB1: DbGrmtech
- Loc1: 10.1.31.9
- DB2: DbGrmtech
- Loc2: 10.1.31.6
- Loc1 is master and Loc2 is slave
2. Create a dump file of DB1 using the command:
mysqldump –port 3306 -u GrmtechUser –password=GrmtechPass -h 10.1.31.9 –databases DbGrmtech –add-drop-table > dbdump.sql
3. Rsync the dbdump file to Loc2 using the command:
rsync -azpSv -e ‘ssh -p 99’ dbdump.sql 10.1.31.6:/home/user/domains/dbbackup/grmtech/dbdump.sql
4. Import the dbdump file in Loc2 using the command:
mysql -u GrmtechUser –force DbGrmtech –password=GrmtechPass -h 10.1.31.6 < dbdump.sql
5. Edit Loc1's /etc/my.cnf file and add the following lines under [mysqld]:
server-id = 1
log-bin = /var/log/mysql/bin.log
binlog-do-db=DbGrmtech
6. Give the command to restart mysql daemon:
service mysqld restart
7. Login to mysql at Loc2 and give the following command:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'crmuser'@'10.1.31.6' IDENTIFIED BY 'crmpass'
8. Edit Loc2's /etc/my.cnf file and add the following lines under [mysqld]:
server-id = 2
master-host = 10.1.31.9
master-port = 3306
master-user = crmuser
master-password = crmpass
replicate-wild-do-table=DbGrmtech.%
replicate-ignore-table = DbGrmtech.tracker
log-bin = /var/log/mysql/bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
9. Give the command to restart mysql daemon:
service mysqld restart
10. Login to mysql at Loc2 and give the following commands:
Show Slave Status;
If master host setting is not what u set at my.cnf, use this command at mysql prompt:
CHANGE MASTER TO MASTER_HOST='10.1.31.9', MASTER_USER='crmuser', MASTER_PASSWORD='crmpass', MASTER_LOG_FILE='bin.000001';
By doing the above settings the changes at DB1@Loc1 will be auto replicated to DB2@Loc2 but not vice versa.
Some commands handy in troubleshooting the entire setup:
At Mysql prompt
1. SHOW MASTER STATUS
2. SHOW SLAVE STATUS
3. START/STOP MASTER
4. START/STOP SLAVE
At command prompt
1. tail -50 /var/log/mysql/bin.000001: to see latest changes in DB1
Helpful links:
1. http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html