관리자 글쓰기
[Mysql] MySQL Replication 설정
2016. 9. 30. 11:25 - 미나리다


mysql 리플리케이션 설정 


   

물리적으로 다른서버에 데이터를 복사(부하를 줄이기위해서)

mysql버전이다를경우 slave버전이 더 높아야된다

Master (읽고쓰기 권한)

Slave  (쓰기불가 읽기전용)

http://gywn.net/2011/12/mysql-replication-1/  ;;참고 설명잘되어있음

http://kit2013.tistory.com/157 ;;참고



1.환경설정

2.DB 유저 생성

3.DB 데이터 동기화(셋 중 택 1)

– DB Data File Copy

– MySQL Dump (All Lock)

– Export/Import (Single Transaction)

4.리플리케이션 시작



             구성 

DB1------------------------DB2

master                          slave



1. Master server (DB1)

my.cnf수정 


#vim /etc/my.cnf

    #log setting    

    log-bin = mysql-bin             // 로그파일명

    max_binlog_size = 100M      // 로그파일크기

    expire_logs_days = 7          // 로그보존주기

 

    #Replication for master server    

    server-id = 1                      // 서버 식별자(유니크) 마스터와 슬레이브서로달라야한다.

    binlog_do_db = db명          // 리플리케이션DB명(생략시엔 전체DB를 리플리케이션함)

    binlog_do_db = db명         // 여러 개의 DB일경우, 계속 추가


// Debian/Ubuntu 같은 OS는 ski-networking이 "bind-address to 127.0.0.1"로 표기

 이 주소에 Slave의 주소나 HostName을 넣어야한다.



리플리케이션할 사용자추가 및 데이터 백업

#mysql -p 

 mysql> GRANT REPLICATION SLAVE ON *.*  TO ‘username’@’%’ IDENTIFIED BY ‘password’;
 
 mysql> FLUSH PRIVILEGES;
 
 mysql> FLUSH TABLES WITH READ LOCK;    // DB Write 금지

# mysqldump -u root -p --all-databases --master-data=2 >BackupData.sql  

※ 리플리케이션대상 DB를 백업함(모든db를백업, --master-data=2 : 덤프시점의 binary log파일도 같이 dump) 용량이 클경우 data파일을 복사하면된다. ftp나 scp를 이용해 slave서버로 백업한내용을 전송 



bin로그에 Position 확인

#mysql -p

 mysql> SHOW MASTER STATUS;

 

 +——————–+———+—————-+———————-+—————————————-+————————————————-+

 | File             |Position| Binlog_Do_DB | Binlog_Ignore_DB|

 +——————–+———+—————-+———————-+—————————————-+————————————————-+

 | mysql-bin.000009 |   204  |              |                 |

 +——————–+———+—————-+———————-+—————————————-+————————————————-+

 

 1 row in set (0.00 sec)

 

 mysql> UNLOCK TABLES;                           // DB Write 금지해제 





master 정보확인

mysql> show master status\G

*************************** 1. row ***************************
            File: mysql-bin.000113                       <<bin파일 로그확인
        Position: 231083                                    << 로그파일을 읽을 위치 확인 
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)


mysql 재시작 



2. Slave server (DB2)

my.cnf수정 


#vim /etc/my.cnf

server-id=2

binlog_do_db = db명  <<특정db만 리플리케이션 하고자하는경우 추가 생략시 전체db리플리케이션


relay-log = slave-relay-bin
log-slave-updates
slave-skip-errors = all <<모든에러를 무시하고 싱크를 맞춘다 (코드번호로 지정할수도있음)

read_onyl << 해당옵션이 있어야 슬레이브서버에서 읽기만 된다 슬레이브에 데이터가 들어가면안되니 꼭 이옵션을 주어야함



       master로부터 백업한 파일 dump파일을 넣음

 # mysql -u root -p --force < BackupData.sql



        Master와 접속설정


#mysql -uroot -ppassword 


sql > CHANGE MASTER TO

MASTER_HOST='마스터서버ip',

MASTER_USER='DB2' , 

MASTER_PASSWORD='password',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.0000009',

MASTER_LOG_POS='204'

(1) 옵션 

MASTER_HOST='마스터서버ip',

MASTER_USER='DB2' ,   <<위에서 생성해준 리플리케이션 사용자명

MASTER_PASSWORD='password',   <<위에서 생성해준 리플리케이션 사용자 패스워드

MASTER_PORT=3306,   <<마스터서버 포트

MASTER_LOG_FILE='mysql-bin.000113',  <<MasterDB의 Status에 기록된 파일명

MASTER_LOG_POS='231083' << master status에서 본 POS 번호 




     Slave START 및 에러확인

 sql > start slave;



sql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event   << Slave의 I/O thread가 master변경 event를 감지하고있다는뜻  

                  Master_Host: 192.168.100.111  
                  Master_User: DB2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000113
          Read_Master_Log_Pos: 112963
               Relay_Log_File: slave-relay-bin.000006
                Relay_Log_Pos: 113109
        Relay_Master_Log_File: mysql-bin.000113
             Slave_IO_Running: Yes        <<yes여야됨
            Slave_SQL_Running: Yes       <<yes여야됨
        Seconds_Behind_Master: 0        <<0이면 정상적으로 리플리케이션 되고있는것이다
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)





master server 에서 확인


 mysql> show slave hosts;


하면 슬레이브서버 가나옴


my.cnf에 report-host=hostname 을 넣어주면 master서버에서 show slave hosts;가 작동