Showing posts with label load balancing. Show all posts
Showing posts with label load balancing. Show all posts

Thursday, February 23, 2012

Master Slave, Master Master Replication

I just struggled 3 days for doing this. Just writing it down in case I forget it soon.
It will be helpful for other people also, who will get a good head start into this field.

These are the steps for creating Master Slave replication......

1. Make data base of Master and Slave identical by running the dump of Master on Slave.
2. Master configurations in my.ini
server-id = 1
auto_increment_offset=1
# total number of master servers
auto_increment_increment=2
# local slave replication options
log-bin=master1-bin
log-slave-updates
3. Slave Configuration in my.ini
server-id = 2
auto_increment_offset=2
# total number of master servers
auto_increment_increment=2
# local slave replication options
log-bin=master2-bin
log-slave-updates
5. Restart both severs.
6. Create a new user on Master and Grant him replication permissions.
CREATE USER 'mike'@'masterhostname' IDENTIFIED BY 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'user1'@'%' IDENTIFIED BY 'pass';
7. Run this command on Master
show master status \G;
It will show something like this...
*************************** 1. row ***************************
File: master1-bin.000001
Position: 1739
Binlog_Do_DB:
Binlog_Ignore_DB:
Note down File name and Position of log file...
8. Run this query on Slave
CHANGE MASTER TO MASTER_HOST='masterhostname', MASTER_USER='user1', MASTER_PASSWORD='pass',
MASTER_PORT=3307, MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG
_POS=1739; [remember to change port number accordingly]

9. Run this query on Slave
start slave;
10. Now run any query on Master and you will see that change on Slave.
11. Make sure network connections is stablished and all required ports are open between two
server.
12. Repeat the above process if you want to add more slaves.
13. Same process can be used for creating Master-Master replication also. You will have to run
CHANGE MASTER query, with right parameters on Master which is slave to another Master.