[MySQL] MySQL Replication 구성하기

[MySQL] MySQL Replication 구성하기

---

Replication : 복제

DB 이중화 방식 중 하나로 MySQL에서는 Replication(복제)이라는 기능을 제공하여 Master(#1)와 Slave(#2)간의 데이터 복제를 가능하게 한다.

MySQL은 bin-log라고 하는 binary log에 변경된 정보를 기록하며, Slave(#2)가 Master(#1) 측에서 변경된 정보를 기록한 binary log를 읽어 본인의 DB에 저장하여 복제가 이루어진다.

그럼 MySQL Replication을 구성하기 위한 Master와 Slave에서 설정방법에 대해 알아보자.

1. Master Configuration

---

1-1. Replication User 생성하기

Slave(#2)가 Master(#1)에 접근하여 변경된 정보를 가져오는 것이기 때문에 Master(#1)에서는 Replication을 위한 전용 계정을 만들어 주어야 한다. root 혹은 기타 계정을 사용할 수 있지만 보안상 문제로 인해 좋지 않은 방법이다.

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY 'slavepass';

기본 mysql의 user테이블을 보면 계정의 여러가지 권한이 있는데 Replication을 위한 권한만 주기위하여 slave 권한만 부여하였다.

1-2. my.cnf 설정

[mysqld]
log-bin=mysql-bin
server-id=1

server-id는 1부터 2^32까지 아무 숫자나 기입해도 되지만 Master는 기본적으로 1번으로 하는것이 좋다.

1-3. MySQL 재기동

1-4. SHOW MASTER STATUS

[mysql]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
 
[mysql]> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000005
        Position: 327
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

show master status에 나오는 log file에 대한 정보를 가지고 slave가 master를 바라보며 정보를 가져오기 때문에 

file과 position의 정보를 따로 적어두어야 한다.

1-5. Database dump

mysqldump -uroot -p --all-databases > dump.sql

설정에 따라 database를 전체 백업할지, 특정 테이블만 백업할지 정할 수 있다.

2. Slave Configuration

---

2-1. my.cnf 설정

[mysqld]
server-id=2
replicate-do-db='ga'
skip-slave-start

server-id는 Master의 설정된 id값과 다른 1부터 2^32까지 아무 숫자나 기입해도 된다.

replicate-do-db를 통해 replication 하기 위한 database명을 입력해준다.

skip-slave-start의 설정은 MySQL재기동 시 slave가 자동으로 시작되는 것을 막기 위한 설정이다.

2-2. insert Database dump

mysql -u root -p --force < dump.sql

Master에서 dump로 받은 sql문을 통해 복제할 database를 넣어준다.

2-3. Change Master to

Slave에서 Master의 Replication을 위한 Master연결 정보를 입력한다.

CHANGE MASTER TO
MASTER_HOST='Master server host or IP',
MASTER_USER='replication user',
MASTER_PASSWORD='replication user password',
MASTER_LOG_FILE='Master log file name',
MASTER_LOG_POS=position

2번째 라인) Master 서버의 host 혹은 IP주소

3번째 라인) Master에서 replication을 위해 만든 계정

4번째 라인) replication 계정의 패스워드

5번째 라인) Master에서 'show master status' 명령어를 통해 따로 적어두었던 log file name

6번째 라인) log file position

위의 정보 이외에도 Slave에서 'show slave status' 명령어를 통해 나오는 설정값을 추가로 지정할 수 있다.

예를 들어 망 분리로 인한 외부 port가 3306을 사용하지 않을 경우 master_port값으로 master의 정보를 추가할 수 있다.

2-4. MySQL 재기동

이제는 Replicaiont을 위한 Master(#1)와 Slave(#2)의 설정을 완료하였다.

2-5. start slave

slave의 자동 시작을 막아두었기때문에 위의 명령어로 slave를 시작한다.

3. Check Replication

---

3-1. [Master] show processlist\G

[mysql] > show processlist\G
*************************** 1. row ***************************
      Id: 4
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: init
    Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
      Id: 7
    User: root
    Host: 192.168.2.22:56539
      db: mysql
 Command: Sleep
    Time: 32
   State:
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 8
    User: repluser
    Host: 192.168.2.82:47784
      db: NULL
 Command: Binlog Dump
    Time: 12
   State: Master has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)
 

3번의 row를 보면 repleuser라는 계정이 29번째 라인) Master가 보낸 binlog를 slave하였고, 업데이트를 기다리고 있다는 것을 확인 할 수 있다.

3-2. [Slave] show slave status\G

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.81
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 514
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 724
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ga
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: 514
Relay_Log_Space: 1026
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative    
1 row in set (0.00 sec)

현재 slave의 상태를 보여주는 명령어로 3번째 라인) Master에서 보내주기를 기다린다는 메세지를 통해 Replication이 정상적으로 연결된 것을 알 수 있다. 비 정상적인 연결 설정 혹은 에러는 13,14번째 라인) 에서 Yes가 아닌 No라는 메세지가 나온다면 정상적인 연결이 되지 않았다는 것이다.

MariaDB [(none)]> show processlist\G
*************************** 1. row ***************************
      Id: 3
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: init
    Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
      Id: 6
    User: system user
    Host:
      db: NULL
 Command: Connect
    Time: 395
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 7
    User: system user
    Host:
      db: NULL
 Command: Connect
    Time: 370
   State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
Progress: 0.000

다음으로 Slave에서도 'show processlist\G'를 통해 Slave에서 Replication이 정상적으로 설정되었는지 확인할 수 있다.

역시 3번째 row에서 update를 기다린다는 메세지를 통해 확인할 수 있다.

4. 이 밖의 설정

---

Replication은 slave측에서 

start slave;

stop slave;

명령어로 slave를 start, stop 할 수 있으며,

slave의 설정을 초기화 하기 위해서는 

reset slave all;

명령어를 통해 slave설정을 초기화 할 수 있다.