Mysql 동기화 부분 처리
<서버 환경>
OS : CentOS 5.3
Master : mysql-5.1.30 ; 192.168.0.1
Slave : mysql-5.1.40 ; 192.168.0.2
** mysql replication 시 Slave는 Master 버전보다 같거나 높아야 됨!
>> 각 서버별 session 2개씩 열어놓고 작업을...
하나는 리눅스 Shell, 다른 하나는 DB session
<환경설정>
(1) Master
# vi /etc/my.cnf
server-id = 1
log-bin=mysql-bin
binlog-do-db=post <= post db만 선택
** binlog-do-db 를 아래로 여러개 추가 가능!!
>저장 후 mysqld_safe & 데몬 실행
(2) Slave
# vi /etc/my.cnf
server-id = 2
log-bin=mysql-bin
binlog_format=mixed
''replicate-do-db='복제할 DB명' ; 전체 DB 설정시 이 항목 삭제.
>저장 후 mysqld_safe & 데몬 실행
<Master>
>>DB session 부분
mysql> GRANT REPLICATION SLAVE ON *.* TO '''''repl'@'192.168.0.2''' IDENTIFIED BY '비번';
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 244 | post | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
** 위 세션 종료 하지말고 그대로 둘 것!!!
>>Shell 부분
Master 서버의 DB를 dump 뜬다.
# /usr/local/mysql/bin/mysqldump -uroot -p --all-databases --lock-all-tables > all_db(덤프 DB명).sql
** 모든 DB가 아니라 원하는 DB만 dump 시 --all-databases 대신에 선택DB명!
(예, --databases db1 db2 db3)
또는
tar로 묶어 slave 서버로 전송 (난 tar로 묶어 전송했음...success ^^;)
# tar cf db1.tar
# tar cf db2.tar
:
# scp db1.tar db2.tar mysql@192.168.0.2:/tmp
tmp# mv db?.tar /home/sqldata/
home/sqldata# tar xf db1.tar db2.tar
<Slave>
>>DB session 부분
mysql> stop slave; << stop slave first 메시지 나올 때 실행
mysql>CHANGE MASTER TO
'' ->MASTER_HOST='192.168.0.1',
'' ->MASTER_USER='repl',
->MASTER_PORT=3306,
'' ->MASTER_PASSWORD='패스워드',
' ->MASTER_LOG_FILE='mysql-bin.000008'', 마스터 상태에서 확인한 Log 파일명.
->MASTER_LOG_POS=244; 마스터 상태에서 확인한 포지션.
mysql>
**위 세션 종료하지 말 것!
>>Shell 부분
Master 서버에서 dump 뜬 DB 복구
# /usr/local/mysql/bin/mysql -uroot -p post < 덤프DB명.sql
또는 tar로 묶은 파일을 db 생성없이 풀어줌
** DB session에서 slave 시작 시켜줌
mysql> start slave;
** Master에서 DB session에서 read lock 걸어준 것 풀어줌
mysql> unlock tables;
<< replication 상태 확인 >>
<Master>
mysql> show master status;
+------------------+----------+---------------------------------------------------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------------------------------------------------------------------+------------------+
| mysql-bin.000140 | 25280602 | post | |
+------------------+----------+---------------------------------------------------------------------------+------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+--------+--------+---------------------+--------+-------------+-------+----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+--------+---------------------+--------+-------------+-------+----------------------------------------------------------------+------------------+
| 420561 | repl | 192.168.0.2:49566 | NULL | Binlog Dump | 37590 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
<Slave>
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000140
Read_Master_Log_Pos: 25280602
Relay_Log_File: post-web-relay-bin.000002
Relay_Log_Pos: 9036298
Relay_Master_Log_File: mysql-bin.000140
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: post
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 25280602
Relay_Log_Space: 9036458
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
mysql> show processlist\g
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| 7 | system user | | NULL | Connect | 37580 | Waiting for master to send event | NULL |
| 8 | system user | | NULL | Connect | 1 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 11 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
>> Slave에서 master.info 와 relay-log.info 로 실시간 동기화 확인 가능^^
** 정상 작동 중에 Slave 서버에서 table 삭제한 경우,
Master 서버에서...
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
>> log 파일 및 position 값 확인
Slave 서버에...Master 서버 환경 재설정
mysql> stop slave;
mysql> CHANGE MASTER TO
'' ->MASTER_HOST='192.168.0.1',
'' ->MASTER_USER='repl',
->MASTER_PORT=3306,
'' ->MASTER_PASSWORD='패스워드',
' ->MASTER_LOG_FILE='mysql-bin.000008'',
->MASTER_LOG_POS=1166;
191