[MySQL] DB 관리를 위한 서버 이중화(Replication) - 2편

선상원·2024년 10월 20일

DBA mysql replication

mysql

목록 보기

8/12

작성일: 2023-04-07 (금)

오늘의 주제는 “Replication” 입니다.

지난번에는 MySQL 에서 기본적으로 제공하는 데이터베이스 이중화 작업에 대해서 간단하게 셋팅하는 작업을 진행했다면,

오늘은 MySQL Replication 에서도 가장 기본적인 Master / Slave 구성을 셋팅하고,
Cold-Backup / Hot-Backup 백업 방식을 통한 동기화 작업을 진행해보려고 합니다.

그전에 간단하게 Cold-Backup 과 Hot-Backup 용어에 대해서 알아볼까요?

Cold-Backup

데이터베이스 서버를 완전히 종료한 후, 수행하는 백업 방식
서버의 다운타임이 발생하기 때문에 가용성을 포기해야 하며, 작업을 진행하는 동안 사용자에게 서비스를 제공할 수 없는 백업 방식

Hot-Backup

데이터베이스 서버를 종료하지 않고, 서비스가 동작하는 상황에서 수행하는 백업 방식
가용성을 지키고, 서비스 사용자에게 미칠 수 있는 영향도를 최소화할 수 있는 백업 방식

🚨 데이터베이스 초기 구축 단계부터 이중화를 구성할 경우 문제가 없겠지만,
대부분의 경우 비용이 2배로 발생하기 때문에 서비스의 규모가 커져 인프라가 중요시 될 때, 이중화를 고려하곤 합니다.
데이터가 충분한 상황에서 이중화를 구성해야 하는 상황이라면
무조건 마스터DB 데이터를 백업한 뒤, 백업 데이터를 이용하여 슬레이브DB 서버를 구축해야만 합니다.

첫 번째로는 마스터DB Cold-Backup 방식을 통한 이중화 구성 작업을 시작해보겠습니다.

Cold-Backup

Master DB
- HOST: mysql-master
- PORT: 3306
- USER: repl_user
- PASSWORD: mysql1234
Slave DB
- HOST: mysql-slave
- PORT: 3307
- USER: repl_user
- PASSWORD: mysql1234

Master DB 서버 데이터 확인

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.08 sec)

mysql> use test;

Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| test01         |
| test02         |
| test03         |
+----------------+
3 rows in set (0.01 sec)

Master DB 서버 백업
```
mysqldump -u root -p --master-data=1 
--no-autocommit=1 
--single-transaction=1 
--extended-insert=1 
--databases test > test.sql && cat test.sql | grep "CHANGE MASTER"
```
- --master-data: 덤프 파일에 CHANGE TO 구문 생성
  (option 1: 구문 생성, option 2: 구문 생성(주석 처리)
- --no-autocommit: Auto Commit 여부
  (option 1: auto_commit 을 끄고 테이블 입력 후 commit)
- --single-transaction: 작업 후, 변경된 데이터의 내역을 다시 적용하지 않음
- --extended-insert: INSERT 구문이 늘어나는 것을 방지
- --databases: 특정 데이터베이스만 백업

🚨 Aurora MySQL 서버의 경우,
master-data 옵션을 사용할 수 없도록 설정되어 있으니 주의해야 합니다.

Master → Slave 데이터 복구

# 데이터 복구
mysql -u root -p < ./test.sql

Slave DB 서버 Replication 설정

STOP SLAVE ;
CHANGE MASTER TO 
master_host='mysql-master', 
master_user='repl_user', 
master_password='mysql1234', 
master_log_file='mysql-bin.000004', 
master_log_pos=605470 ;
START SLAVE ;

mysql> show slave status \G ;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-master
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 661826
               Relay_Log_File: 5a9b1d8f8507-relay-bin.000002
                Relay_Log_Pos: 56676
        Relay_Master_Log_File: mysql-bin.000004
             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: 661826
              Relay_Log_Space: 56890
              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: 5d485bcf-c6f9-11ed-bae5-0242ac110002
             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: 
1 row in set (0.00 sec)

Cold Backup의 경우
서비스를 중지한 상황에서 작업이 진행되기 때문에 작업 난이도가 매우 낮고 안전하지만,
해당 시간 동안 서비스를 고객에게 제공할 수 없기 때문에 사전 점검 안내가 필수적으로 이루어져야 합니다.

다음은 가용성을 지키고, 서비스 사용자에게 미칠 수 있는 영향도를 최소화할 수 있는
Hot-Backup 방법입니다.

Hot-Backup

Master DB
- HOST: mysql-master
- PORT: 3306
- USER: repl_user
- PASSWORD: mysql1234
Slave DB
- HOST: mysql-slave
- PORT: 3307
- USER: repl_user
- PASSWORD: mysql1234

100 millisecond 마다 Master DB 서버로 데이터를 적재하는 상황 연출

#!bin/bash

echo "MySQL Replication TEST"

LOOP=100000
NUMBER=0
while [ $NUMBER -le $LOOP ]
do
	mysql -u root -pmysql1234 test -e "INSERT INTO doznut.test01(name) VALUES ('MySQL')"
	echo "number: ${NUMBER}"
	((NUMBER++))

	sleep 0.1
done

bash test.sh

Master DB 실시간 데이터 확인

mysql> SELECT * FROM test.test01 ORDER BY idx DESC LIMIT 5;
+------+---------+---------------------+
| idx  | name    | created_at          |
+------+---------+---------------------+
| 1000 | MySQL   | 2023-05-20 06:23:12 |
|  999 | MySQL   | 2023-05-20 06:23:12 |
|  998 | MySQL   | 2023-05-20 06:23:12 |
|  997 | MySQL   | 2023-05-20 06:23:12 |
|  996 | MySQL   | 2023-05-20 06:23:11 |
+------+---------+---------------------+
5 rows in set (0.03 sec)

mysql> SELECT COUNT(*) FROM test.test01;
+----------+
| COUNT(*) |
+----------+
|     1020 |
+----------+
1 row in set (0.07 sec)

Master DB 서버 백업

mysqldump -u root -p --master-data=1 
--no-autocommit=1 
--single-transaction=1 
--extended-insert=1 
--databases test > test.sql && cat test.sql | grep "CHANGE MASTER"

Master → Slave 데이터 복구

# 데이터 복구
mysql -u root -p < ./test.sql

Slave DB 서버 Replication 설정

STOP SLAVE ;
CHANGE MASTER TO 
master_host='mysql-master', 
master_user='repl_user', 
master_password='mysql1234', 
master_log_file='mysql-bin.000004', 
master_log_pos=605470 ;
START SLAVE ;

mysql> show slave status \G ;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-host
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 661826
               Relay_Log_File: 5a9b1d8f8507-relay-bin.000002
                Relay_Log_Pos: 56676
        Relay_Master_Log_File: mysql-bin.000004
             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: 661826
              Relay_Log_Space: 56890
              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: 5d485bcf-c6f9-11ed-bae5-0242ac110002
             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: 
1 row in set (0.00 sec)

Slave 서버 데이터 확인

mysql> SELECT * FROM test.test01 ORDER BY idx DESC LIMIT 5;
+------+---------+---------------------+
| idx  | name    | created_at          |
+------+---------+---------------------+
| 2540 | MySQL   | 2023-05-20 06:27:54 |
| 2539 | MySQL   | 2023-05-20 06:27:54 |
| 2538 | MySQL   | 2023-05-20 06:27:53 |
| 2537 | MySQL   | 2023-05-20 06:27:53 |
| 2536 | MySQL   | 2023-05-20 06:27:53 |
+------+---------+---------------------+
5 rows in set (0.03 sec)

mysql> SELECT COUNT(*) FROM test.test01;
+----------+
| COUNT(*) |
+----------+
|     2594 |
+----------+
1 row in set (0.04 sec)

💡 오늘은 Cold Backup & Hot Backup 방식을 통한 데이터베이스 이중화 작업을 진행해보았습니다.

위의 작업처럼 데이터 용량이 크지 않을 경우라면 mysqldump 명령어를 통해
빠르게 Slave DB 서버로 복원 및 이중화 구성을 셋팅할 수 있습니다.
그러나, 데이터 용량이 100GB, 500GB, 1TB 이상으로 커진 서버를 mysqldump 명령어를 통해 백업한다면 작업 시간을 예상할 수 없을 것입니다.

대용량 데이터를 백업하는 경우에는 Percona 사에서 제공하는 Xtrabackup Toolkit을 이용하여 빠르게 백업하고 복구할 수 있습니다.

선상원

쉼 없는 고민과 학습을 통해 가장 효율적인 데이터베이스 관리 방안을 찾고자 노력하는 DBA 입니다.

[MySQL] DB 관리를 위한 서버 이중화(Replication) - 2편

mysql

작성일: 2023-04-07 (금)

Cold-Backup

Hot-Backup

Cold-Backup

Hot-Backup

[MySQL] DB 관리를 위한 서버 이중화(Replication) - 1편

[MySQL] SQL문 최적화

0개의 댓글

[MySQL] DB 관리를 위한 서버 이중화(Replication) - 2편

mysql

작성일: 2023-04-07 (금)

Cold-Backup

Hot-Backup

Cold-Backup

Hot-Backup

[MySQL] DB 관리를 위한 서버 이중화(Replication) - 1편

[MySQL] SQL문 최적화

0개의 댓글

[MySQL] SQL문 최적화