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"

0 comments: