Monday, February 21, 2011

Master-Master Replication

Master-Master Replication
Master1 - 1.1.1.1
Master3 - 2.2.2.2


Master1
Edit /etc/my.cnf

#Primary Master server
server-id=1
auto_increment_offset=1
# total number of master servers
auto_increment_increment=2
# local slave replication options
# remote master replication options
master-host=2.2.2.2
master-port=3306
master-user=repl
master-password=replpass
master-connect-retry=10

Master3
#Secondry Master server
server-id=3
auto_increment_offset=1
# total number of master servers
auto_increment_increment=2
# local slave replication options
# remote master replication options
master-host=2.2.2.2
master-port=3306
master-user=repl
master-password=replpass
master-connect-retry=10


Loggin to Master1:

GRANT ALL ON *.* TO  root@'2.2.2.2' IDENTIFIED BY 'email#secure';
GRANT ALL ON *.* TO  repl@'2.2.2.2' IDENTIFIED BY 'replpass';
GRANT ALL ON *.* TO  repl@'%' IDENTIFIED BY 'replpass';

mysql> SHOW MASTER STATUS\G
mysql> FLUSH TABLES WITH READ LOCK;

[root@localhost]mysqldump -A -uroot -p password > master1.sql


On Master3

[root@localhost]mysql -uroot -ppassword  < master1.sql

CHANGE MASTER TO MASTER_HOST='1.1.1.1',MASTER_PORT=3666, MASTER_USER='repl', MASTER_PASSWORD='replpass',  MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=1190;

mysql> START SLAVE;
mysql> SHOW MASTER STATUS\G


On Master1

CHANGE MASTER TO MASTER_HOST='2.2.2.2',MASTER_PORT=3666, MASTER_USER='repl', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=220776001;
mysql> START SLAVE;

Master-Master Replication done.

Do add slave for master, use same post from this blog.
"http://shriikant.blogspot.com/2009/10/mysql-master-slave-replication.html"

1 comments:

Renu said...

Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
Devops Training in electronic city