using mysql master-master replication

master-master replication은 DB 이중화에 유용하다.

0.
server1 : 192.168.0.1
server2 : 192.168.0.2

1.
server1, server2의 my.cnf의 내용을 맞춘다

server1 :

vi /etc/mysql/my.cnf 

#server1
server-id = 1
log_bin=/var/lib/mysql/mysql-bin.log
binlog_do_db = test1
binlog_do_db = test2
binlog-ignore-db = mysql
binlog-ignore-db = test

server2 :

vi /etc/mysql/my.cnf

#server2
server-id = 2
log_bin=/var/lib/mysql/mysql-bin.log
binlog_do_db = test1
binlog_do_db = test2
binlog-ignore-db = mysql
binlog-ignore-db = test

2.
my.cnf를 설정한후 두 서버 리스타트후 각각 서버에서 replication 계정을 활성화 해준다.

server1 :
server2 :

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED BY 'replication_password';

3.
각 서버 replication slave 를 stop하고 test1 => test2의 DB를 일치시킨다.

sever1 :

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000105 | 33527473 |              |                  |
+------------------+----------+--------------+------------------+

스냅샵을 뜨고

mysqldump -uroot -ppassword -B test1 test2 > snapshot.sql

server2 :

mysql -uroot -ppassword < snapshot.sql

server2에 복원한다.

STOP SLAVE;
CHANGE MASTER TO master_host='192.168.0.1', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='mysql-bin.000105', master_log_pos=33527473;
START SLAVE;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000073 | 00027473 |              |                  |
+------------------+----------+--------------+------------------+

server 1 :

CHANGE MASTER TO master_host='192.168.0.2', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='mysql-bin.000073', master_log_pos=00027473;
START SLAVE;

4.
두 서버의 slave status를 확인해서 에러 없는지 확인

SHOW SLAVE STATUS \G

5.
server3 : 192.168.0.3

서버를 하나 더 늘린다면 모든 커넥션을 server1로 옮겨두고 server1의 slave를 중지 시킨다

server3 :

vi /etc/mysql/my.cnf 

#server3
server-id = 3
log_bin=/var/lib/mysql/mysql-bin.log
binlog_do_db = test1
binlog_do_db = test2
binlog-ignore-db = mysql
binlog-ignore-db = test

server1 :

stop slave;

6.
server2의 내용을 server3로 이사한다
server2 :

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000137 | 33527001 |              |                  |
+------------------+----------+--------------+------------------+

스냅샵을 뜨고

mysqldump -uroot -ppassword -B test1 test2 > snapshot.sql

7.
server3 :

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED BY 'replication_password';

권한을 설정해준다.

mysql -uroot -ppassword < snapshot.sql

server3에 복원한다.

STOP SLAVE;
CHANGE MASTER TO master_host='192.168.0.2', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='mysql-bin.000137', master_log_pos=33527001;
START SLAVE;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000043 | 00000015 |              |                  |
+------------------+----------+--------------+------------------+

server 1 :

CHANGE MASTER TO master_host='192.168.0.3', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='mysql-bin.000043', master_log_pos=00000015;
START SLAVE;

8.
세 서버의 slave status를 확인해서 에러 없는지 확인

SHOW SLAVE STATUS \G

글쓴이

yupmin

Inside Deep Throat.

답글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다

This site uses Akismet to reduce spam. Learn how your comment data is processed.