Tips 2013/03/13 15:47
http://blog.naver.com/ggaibi1004/10163013682
CentOS 6.2, MySQL 5.5.16에서 테스트했습니다.
먼저 Master-Slave 구조 설정 후에 Master-Master 구조 설정을 추가하였습니다.
#######################################################################
[ Master - Slave 설정 ]
#######################################################################
: Master에서만 트랜잭션이 가능하고 Master의 변경사항이 Slave로 단방향 동기화 구조
##################################
[ Master 설정 ]
##################################
1. vi /etc/my.cnf 설정
---------------------------
[mysqld]
# Seting for Replication(Master)
server-id = 1 <- 서버 고유아이디, Slave와 다르게 설정
log-bin = mysql-bin <- MySQL(오라클의 redo로그와 유사) 로그생성 설정
expire_logs_days = 7 <- 로그 보관주기 설정(일)
replicate-do-db = test_db <- Replication 대상 DB 지정, 이 옵션을 제거시 모든 DB 복제
---------------------------
* 참고(binlog_do_db/replicate-do-db 차이)
- binlog_do_db : Master 기준 어떤 DB를 Replication 허용할 것인지 설정(제외조건 binlog_ignore_db)
- replicate-do-db : Slave 기준 어떤 DB를 Replication 할 것인지 설정(제외조건 replicate-ignore-db)
2. mysql restart
service mysql restart
3. Master 접속 계정생성 및 Replication 접근 권한설정
---------------------------
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl'; <- repl/repl 계정변경가능
mysql> FLUSH PRIVILEGES;
---------------------------
4. 데이터 동기화(백업)
---------------------------
mysql> FLUSH TABLES WITH READ LOCK;
mysql> mysqldump -u root -p test_db > backup.sql
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> UNLOCK TABLES;
---------------------------
##################################
[ Slave 설정 ]
##################################
1. 데이터 동기화(복사)
---------------------------
mysql> mysql -u root -p test_db < backup.sql
---------------------------
2. vi /etc/my.cnf 설정
---------------------------
[mysqld]
# Seting for Replication(Slave)
server-id = 2
log-bin = mysql-bin
expire_logs_days = 7
replicate-do-db = test_db
---------------------------
3. mysql restart
service mysql restart
4. Master 접속설정
---------------------------
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.5', <- Master IP나 호스트명을 입력
MASTER_USER='repl', <- Replication 아이디
MASTER_PASSWORD='repl', <- Replication 패스워드
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001', <- Master의 SHOW MASTER STATUS; 결과화면의 로그파일명 입력
MASTER_LOG_POS=107; <- Master의 SHOW MASTER STATUS; 결과화면의 Position 입력
---------------------------
5. Slave 시작
---------------------------
#변경의 경우
#stop slave;
#reset slave;
#flush privileges;
start slave;
#######################################################################
[ Master - Master 설정추가 ]
#######################################################################
: 양쪽 노드가 다 Master이면서 Slave인 구조로 양방향 동기화 구조
##################################
[ Slave 설정 ]
##################################
: 위 Master-Slave 설정에서 서로 바꾸어 설정을 하면 됨
1. 접속 계정생성 및 Replication 접근 권한설정
---------------------------
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
mysql> FLUSH PRIVILEGES;
---------------------------
2. 로그파일과 Position확인
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 322 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
##################################
[ Master 설정 ]
##################################
3. Master 접속설정
---------------------------
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.19', <- Master IP나 호스트명을 입력
MASTER_USER='repl', <- Replication 아이디
MASTER_PASSWORD='repl', <- Replication 패스워드
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', <- Slave의 SHOW MASTER STATUS; 결과화면의 로그파일명 입력
MASTER_LOG_POS=322; <- Slave의 SHOW MASTER STATUS; 결과화면의 Position 입력
flush privileges;
start slave;
---------------------------
4. 동작확인
mysql> SHOW SLAVE STATUS\G
양쪽에서 위 명령으로 상태 확인시 아래와 같은 결과시 성공.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.5
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: JCSM-2-relay-bin.000006
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test_db
...중략
1 row in set (0.00 sec)
한쪽 mysql 중지시 아래와 같은 결과
*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 192.168.1.5
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 24447
Relay_Log_File: JCSM-2-relay-bin.000004
Relay_Log_Pos: 22473
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: test_db
....중략
Last_IO_Errno: 2003
Last_IO_Error: error reconnecting to master 'repl@192.168.1.5:3306' - retry-time: 60 retries: 86400
1 row in set (0.00 sec)
49