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
