MySQL Replication (Master / Slave) Linux



<서버 환경>
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

mysql> start slave;




핑백

덧글

  • 2131 2010/03/17 20:59 # 삭제 답글

    show master status; 하면 empty라고 나오는데 어떡해야 하나요?
  • 멋진쟈니 2010/03/25 09:13 #

    /etc/my.cnf 환경설정 파일에 log-bin=mysql-bin 항목에 주석(#) 없애고 mysql 재시작해보세요~
댓글 입력 영역



통계 위젯 (블랙)

08
90
353737

이 이글루를 링크한 사람 (화이트)

2

google

구글_이글루스_사이드