[MySQL] Replication

들어가며

Replication이란?

• Master DB의 데이터를 Slave DB에 복제하는 방식을 말한다.
• DB에 과도한 부하가 발생할 경우 Master DB에는 추가/수정/삭제만 처리하고, Slave DB에서 읽기 작업을 처리하도록 하여 부하 분산 효과를 얻을 수 있다.
• Master DB 장애 발생으로 데이터 유실시 Slave DB에 데이터가 남아있어 복구하기 쉽다.

Master 서버 설정

my.cnf

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

DB Backup

• 아래 명령어로 생성된 dump.sql 파일은 Slave 서버 설정시 필요하므로 잘 관리한다.

mysqldump -u root -p --all-databases > dump.sql

Replication용 계정 생성

• username : im_master_repl
• password : 123456

CREATE USER 'im_master_repl'@'%' IDENTIFIED BY '123456';

Replication용 계정 권한 부여

GRANT REPLICATION SLAVE ON *.* TO 'im_master_repl'@'%';
FLUSH PRIVILEGES;

Master 정보 확인

• 아래 File, Position 정보를 Slave 설정시에 활용하므로 잘 확인해야함

mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 816
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

Slave 설정

my.cnf

• 다른 Master, Slave 서버의 server-id를 제외한 값으로 설정

[mysqld]
server-id=2

DB Restore

• 위 Master 설정 과정에서 만들어진 dump.sql 파일을 Slave 서버로 이동시킨 후 아래 명령어를 실행하여 Master와 동일한 상태로 만든다.

mysql -u root -p < dump.sql

Master 서버 정보 추가

• MASTER_HOST, MASTER_PORT : Master 서버의 주소, 포트값
• MASTER_USER, MASTER_PASSWORD : Master에서 생성한 레플리케이션용 계정 정보
• MASTER_LOG_FILE, MASTER_LOG_POS : Master 설정 과정 마지막에서의 File, Position 값

CHANGE MASTER TO
    MASTER_HOST = '192.168.56.12',
    MASTER_PORT = 13306,
    MASTER_USER = 'im_master_repl',
    MASTER_PASSWORD = '123456',
    MASTER_LOG_FILE = 'mysql-bin.000003',
    MASTER_LOG_POS = 816;

Slave 시작

-- Slave 종료 명령어는 STOP SLAVE
START SLAVE;

Slave 상태 확인

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.12
                  Master_User: im_master_repl
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1632
               Relay_Log_File: 94d17d89a2f8-relay-bin.000002
                Relay_Log_Pos: 1136
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 1632
              Relay_Log_Space: 1350
              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_UUID: fdbab12e-45b8-11ec-80c8-0242c0a88002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

테스트

Master에 접속하여 테이블 생성 & 데이터 추가

CREATE TABLE User
(
    userId VARCHAR(200),
    name   VARCHAR(200),
    PRIMARY KEY (userId)
);

INSERT INTO User (userId, name) VALUES ('1', 'john');
INSERT INTO User (userId, name) VALUES ('2', 'jane');

mysql> SELECT * FROM User;
+--------+------+
| userId | name |
+--------+------+
| 1      | john |
| 2      | jane |
+--------+------+

Slave에 접속하여 데이터 백업 확인

mysql> SELECT * FROM User;
+--------+------+
| userId | name |
+--------+------+
| 1      | john |
| 2      | jane |
+--------+------+

주의사항

• Slave에 데이터를 추가하면 Master에 반영되지 않으므로 꼭 변경 작업은 Master에서 진행해야한다.

참고

• https://ykarma1996.tistory.com/83