CentOS 5.4에서 MySQL SSL을 사용한 데이터베이스 복제 설정 가이드

버전 1.0
저자: Falko Timme
Follow me on Twitter

목적 및 배경

이 문서는 MySQL 복제를 SSL(암호화)로 보호하는 방법을 설명합니다. MySQL 복제는 한 서버(마스터)의 데이터베이스를 다른 서버(슬레이브)에 동일하게 복사하여 실시간으로 동기화하는 기능입니다. 이 문서는 마스터에서 슬레이브로의 트래픽을 SSL로 암호화하여 패스워드나 전송 중인 데이터를 도청으로부터 보호하는 방법에 초점을 둡니다.

정의: 복제 — 마스터 서버에서 발생하는 변경사항(트랜잭션)을 슬레이브 서버의 바인로그(binlog)를 통해 동일하게 적용하는 과정.

중요: 복제는 백업 정책이 아닙니다. 실수로 DELETE 명령을 실행하면 슬레이브에도 그대로 반영됩니다. 하지만 하드웨어 장애에 대한 보호 수단으로 유용합니다.

노트: 이 가이드는 CentOS 5.4에서 테스트된 내용을 바탕으로 작성되었습니다. 다른 리눅스 배포판에서도 대부분 설정이 유사하게 적용됩니다.

사전 조건

• 마스터: server1.example.com (IP: 192.168.0.100)
• 슬레이브: server2.example.com (IP: 192.168.0.101)
• 두 시스템 모두 CentOS 5.4 실행 중
• exampledb 데이터베이스는 마스터에 이미 존재하며 슬레이브에는 없음
• 루트(root) 권한으로 작업 수행(설정 및 인증서 복사에 필요)

Important: 실제 운영 환경에서 루트 대신 sudo를 사용한 최소 권한 원칙을 적용하세요.

준비 단계 요약

• MySQL 5 설치 및 자동 시작 등록
• MySQL에서 SSL 지원 활성화
• CA/서버/클라이언트 인증서 생성
• 마스터 설정: binlog, server-id, replication 계정 생성(SSL 요구 권장)
• 데이터베이스 잠금 → 덤프 생성 → 슬레이브로 전송
• 슬레이브 설정: 인증서 배치, 복제 설정, 복제 시작 및 검증

1 MySQL 5 설치 및 SSL 지원 활성화

먼저 server1과 server2에 MySQL이 설치되어 있는지 확인하세요. 설치되어 있지 않다면 다음을 실행합니다.

server1/server2:

yum install mysql mysql-devel mysql-server

시스템 부팅 시 MySQL이 자동으로 시작되도록 설정하고 MySQL 서비스를 시작합니다:

chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start

루트 사용자 MySQL 패스워드를 설정합니다(아래 명령은 예시입니다. 강력한 비밀번호로 교체하세요):

server1:

mysqladmin -u root password yourrootsqlpassword
mysqladmin -h server1.example.com -u root password yourrootsqlpassword

server2:

mysqladmin -u root password yourrootsqlpassword
mysqladmin -h server2.example.com -u root password yourrootsqlpassword

이제 MySQL이 SSL을 지원하는지 확인합니다. MySQL 셸에 로그인한 뒤 SSL 관련 변수를 확인합니다:

server1/server2:

mysql -u root -p

MySQL 셸에서 실행:

show variables like '%ssl%';

출력 예시(처음에는 have_openssl과 have_ssl이 DISABLED일 수 있습니다):

mysql> show variables like ‘%ssl%’; +—————+———-+ | Variable_name | Value | +—————+———-+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +—————+———-+

이 경우, MySQL은 SSL을 지원하지만 활성화되지 않은 상태입니다. MySQL 셸을 종료하고 설정 파일을 수정하여 활성화합니다:

quit;

/etc/my.cnf 파일을 편집합니다:

vi /etc/my.cnf

[mysqld] 섹션에 ssl 옵션을 추가합니다(원래 설정 블록은 유지):

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
ssl

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

MySQL을 재시작하고 SSL 활성화를 확인합니다:

/etc/init.d/mysqld restart
mysql -u root -p
show variables like '%ssl%';

활성화된 출력 예시:

mysql> show variables like ‘%ssl%’; +—————+——-+ | Variable_name | Value | +—————+——-+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +—————+——-+

quit;

Notes: 만약 MySQL 패키지가 SSL 없이 빌드되어 배포된다면(rare), 배포판에서 SSL을 지원하는 패키지를 사용하거나 MySQL을 OpenSSL로 다시 빌드해야 합니다.

2 마스터에서 인증서 생성 및 배포

SSL 인증서를 생성하기 위해 /etc/mysql/newcerts 디렉터리를 생성하고 이동합니다:

mkdir -p /etc/mysql/newcerts && cd /etc/mysql/newcerts

CA 인증서 생성(자가 서명 CA):

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

서버 인증서 생성:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

클라이언트 인증서(슬레이브가 클라이언트 역할) 생성:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

생성된 파일 확인:

ls -l

출력 예시(파일 목록):

[root@server1 newcerts]# ls -l total 32 -rw-r–r– 1 root root 1375 Feb 8 17:37 ca-cert.pem -rw-r–r– 1 root root 1679 Feb 8 17:37 ca-key.pem -rw-r–r– 1 root root 1119 Feb 8 17:37 client-cert.pem -rw-r–r– 1 root root 1675 Feb 8 17:37 client-key.pem -rw-r–r– 1 root root 968 Feb 8 17:37 client-req.pem -rw-r–r– 1 root root 1119 Feb 8 17:37 server-cert.pem -rw-r–r– 1 root root 1679 Feb 8 17:37 server-key.pem -rw-r–r– 1 root root 968 Feb 8 17:37 server-req.pem [root@server1 newcerts]#

이제 슬레이브로 전송할 파일을 준비합니다. 슬레이브에는 ca-cert.pem, client-cert.pem, client-key.pem을 복사해야 합니다. 슬레이브에서 디렉터리를 생성합니다:

server2:

mkdir -p /etc/mysql/newcerts

서버1에서 파일 전송(예: scp 사용):

server1:

scp /etc/mysql/newcerts/ca-cert.pem [email protected]:/etc/mysql/newcerts
scp /etc/mysql/newcerts/client-cert.pem [email protected]:/etc/mysql/newcerts
scp /etc/mysql/newcerts/client-key.pem [email protected]:/etc/mysql/newcerts

파일 권한: 클라이언트/서버 키 파일(client-key.pem, server-key.pem)은 읽기 권한을 제한하세요(예: 600).

chmod 600 /etc/mysql/newcerts/client-key.pem
chmod 600 /etc/mysql/newcerts/server-key.pem
chown mysql:mysql /etc/mysql/newcerts/*.pem

3 마스터 설정(옵션 포함)

/etc/my.cnf를 열고 ssl-ca, ssl-cert, ssl-key 항목을 추가합니다. 또한 마스터로서의 설정(서버 아이디, 바이너리 로그 등)을 지정합니다:

vi /etc/my.cnf

[mysqld] 섹션 예시:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/server-cert.pem
ssl-key=/etc/mysql/newcerts/server-key.pem
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = exampledb

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

MySQL 재시작:

/etc/init.d/mysqld restart

복제용 사용자 생성: 슬레이브가 마스터에 접속할 계정을 만듭니다. SSL 접속만 허용하려면 REQUIRE SSL을 사용합니다(권장).

mysql -u root -p

MySQL 셸에서:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL;
FLUSH PRIVILEGES;
quit;

설명: REQUIRE SSL을 지정하면 해당 계정은 암호화된 연결로만 접속할 수 있습니다. 이미 존재하는 사용자에 대해 SSL 연결만 허용하려면:

GRANT USAGE ON *.* TO 'slave_user'@'%' REQUIRE SSL;

4 데이터 일관성 확보: 덤프와 잠금

복제 시작 전 마스터 데이터와 슬레이브 데이터가 동일해야 합니다. 이를 위해 다음 절차를 따릅니다:

1. 마스터에서 exampledb 데이터베이스를 잠급니다.
2. 마스터의 현재 바이너리 로그 파일명과 위치를 확인합니다.
3. 잠금 상태에서 mysqldump로 스냅샷을 만들고 슬레이브로 전송합니다.
4. 잠금을 해제합니다.

마스터에서 실행:

mysql -u root -p

MySQL 셸에서:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

SHOW MASTER STATUS 출력 예시(파일명과 포지션을 기록해 둡니다):

mysql> SHOW MASTER STATUS; +——————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000001 | 3096416 | exampledb | | +——————+———-+————–+——————+ 1 row in set (0.00 sec)

중요: 이 상태에서 MySQL 셸을 그대로 열어 둔 상태로 유지합니다(잠금을 유지하기 위함). 다른 터미널 윈도우를 열어 덤프 작업을 수행하세요.

두 번째 쉘에서 실행(마스터):

cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql [email protected]:/tmp

첫 번째 쉘로 돌아가 잠금을 해제합니다:

UNLOCK TABLES;
quit;

메모: 덤프 파일 전송 시 네트워크 암호화(예: scp 또는 rsync+ssh)를 사용하세요.

5 슬레이브 설정 및 복제 시작

슬레이브에서 다음 설정을 진행합니다.

1. /etc/mysql/newcerts에 CA와 클라이언트 인증서가 있는지 확인합니다.
2. /etc/my.cnf에 ssl 항목을 추가합니다.

슬레이브의 /etc/my.cnf 예시:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/client-cert.pem
ssl-key=/etc/mysql/newcerts/client-key.pem
server-id               = 2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

MySQL을 재시작합니다:

/etc/init.d/mysqld restart

디스크에 전송한 덤프 파일을 임포트합니다(슬레이브에서):

mysql -u root -p

슬레이브에 스냅샷 임포트:

cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

이제 슬레이브에서 마스터의 파일명과 위치를 사용해 복제를 설정합니다. 마스터에서 기록한 File과 Position을 사용하세요(예: mysql-bin.000001, 3096416).

슬레이브 MySQL 셸에서:

CHANGE MASTER TO
  MASTER_HOST='server1.example.com',
  MASTER_USER='slave_user',
  MASTER_PASSWORD='slave_password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=3096416,
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/mysql/newcerts/ca-cert.pem',
  MASTER_SSL_CERT='/etc/mysql/newcerts/client-cert.pem',
  MASTER_SSL_KEY='/etc/mysql/newcerts/client-key.pem';

START SLAVE;

SHOW SLAVE STATUS\G

SHOW SLAVE STATUS 출력에서 확인해야 할 주요 항목:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Last_IO_Error: (빈 값)
• Last_SQL_Error: (빈 값)

복제 오류가 있을 경우 Last_IO_Error 및 Last_SQL_Error를 확인하고 로그(/var/log/mysqld.log)에서 원인을 찾습니다.

검증

마스터에서 간단한 INSERT/UPDATE를 수행하고 슬레이브에서 데이터가 반영되는지 확인하세요.

마스터에서:

USE exampledb;
INSERT INTO test_table (col) VALUES ('replication test');

슬레이브에서:

SELECT * FROM test_table ORDER BY id DESC LIMIT 1;

데이터가 동일하면 복제가 정상 동작합니다.

보안 하드닝 권장 사항

• 복제 전용 계정에 최소 권한만 부여하세요(예: REPLICATION SLAVE).
• REPLICATION 계정에 대해 REQUIRE SSL을 사용해 암호화 연결만 허용하세요.
• 키 파일 권한 제한(600) 및 mysql 사용자 소유 설정을 적용하세요.
• CA 키(ca-key.pem)는 오프라인 안전 장소에 보관하세요.
• 방화벽에서 MySQL 포트(3306) 접근 제어: 마스터는 슬레이브 IP만 허용하세요.
• 정기적으로 바이너리 로그와 백업 정책을 점검하세요.

Notes: 운영 환경에서는 자체 서명 CA 대신 조직 내부 PKI 또는 공인 CA 사용을 고려하세요.

문제 해결 체크리스트

1. SHOW SLAVE STATUS\G에서 Slave_IO_Running 또는 Slave_SQL_Running이 No인 경우:
   • Last_IO_Error/Last_SQL_Error 확인
   • 네트워크 연결 확인(ping, telnet server1 3306)
   • 계정 정보/비밀번호 확인
   • SSL 인증서 경로 및 권한 확인
2. 인증서 관련 오류:
   • CA가 일치하는지 확인(마스터와 슬레이브가 동일한 ca-cert.pem 사용)
   • 키 파일 권한(600)
   • 파일 포맷(PEM) 확인
3. 바이너리 로그 파일/포지션 불일치:
   • 마스터에서 기록한 정확한 File과 Position을 사용했는지 확인
4. 덤프 후 데이터 불일치:
   • 덤프를 생성할 때 FLUSH TABLES WITH READ LOCK 상태였는지 확인

운영자(롤별) 체크리스트

관리자(설계/정책):

• 복제 아키텍처 승인
• 보안 정책(SSL, 키 저장소) 정의
• 백업 및 롤백 정책 수립

시스템 엔지니어(설치/설정):

• MySQL 설치 및 자동 시작 구성
• /etc/my.cnf 설정 적용 및 백업
• 인증서 생성 및 안전한 배포

DBA(운영/검증):

• binlog 설정 및 서버 아이디 확인
• 복제 사용자 생성 및 권한 검토
• 복제 상태 주기적 확인

보안 담당자:

• 키 관리 및 접근 권한 검토
• 네트워크 방화벽 정책 검토
• 감시 및 감사 로그 확인

롤백 및 사고 대응 요령

사고 발생 시 신속 복구 절차(요약):

1. 복제 정지: 슬레이브에서 STOP SLAVE;
2. 원인 파악: 에러 메시지 및 로그 검사
3. 필요 시 슬레이브를 초기화하고(RESET SLAVE ALL), 최신 스냅샷으로 재동기화
4. 마스터에 치명적 오류가 있으면 이전 정상 시점의 백업에서 복원 후 재복제 구성

권장 단계(세부):

• 문제가 마스터의 데이터 손상(예: 잘못된 DELETE)인 경우, 복구를 위해 백업에서 복원하거나 포인트 인 타임 복구(PITR)를 고려하세요. 복제는 삭제를 막지 못합니다.

대안 및 확장 방안

• 복제를 SSL 없이 사설 네트워크 또는 VPN으로 보호: 네트워크 레벨 보안으로 전송 보호를 확보하는 방식.
• MySQL 버전 업그레이드(신뢰성 및 성능 향상): CentOS 5.4는 오래된 환경입니다. 최신 MySQL/MariaDB와 최신 OS를 사용하면 보안 및 기능이 향상됩니다.
• GTID(Global Transaction Identifiers) 기반 복제: MySQL 5.6+에서는 GTID를 사용해 복제 관리가 용이합니다. (CentOS 5.4의 MySQL 5.0/5.1에서는 GTID 미지원)

언제 이 방법이 적합하지 않은가:

• 운영 중인 MySQL 버전이 SSL을 지원하지 않을 때(패키지 재빌드 또는 업그레이드 필요).
• 네트워크가 이미 완전한 L2 보안(전용 전용선 등)으로 보장되어 있고 추가 암호화가 불필요한 경우.

검사 및 수락 기준

• MySQL 서버가 SSL을 활성화하여 have_ssl/has_openssl 값이 YES여야 합니다.
• 슬레이브의 SHOW SLAVE STATUS\G에서 Slave_IO_Running과 Slave_SQL_Running이 모두 Yes여야 합니다.
• 데이터 일관성: 마스터에서 생성한 테스트 레코드가 슬레이브에 반영되어야 합니다.
• 네트워크 트래픽이 암호화되어야 하며(패킷 캡처 시 평문 패스워드가 보이지 않아야 함) 복제 연결은 SSL을 사용해야 합니다.

보안 및 개인정보 고려사항

• 전송 중 데이터 암호화(SSL)는 도청으로부터 보호합니다. 그러나 데이터 저장(디스크) 암호화는 별도로 고려해야 합니다.
• 인증서와 키는 민감 정보입니다. 특히 ca-key.pem은 절대 노출되지 않도록 보관하세요.
• 개인정보(PII)가 포함된 데이터베이스를 복제할 경우 접근 제어, 로그 관리 및 보존 정책을 검토하세요(예: GDPR 관련 보관기간, 접근 기록). 이 가이드는 법적 조언이 아닙니다.

점검표 템플릿

• MySQL 설치 및 자동 시작 구성됨
• root 패스워드 설정됨
• SSL 활성화됨(have_ssl = YES)
• 인증서 생성 및 권한 설정(600)
• CA 인증서가 슬레이브에 복사됨
• 마스터에 binlog 설정 및 server-id 설정됨
• REPLICATION SLAVE 계정 생성됨(권장: REQUIRE SSL)
• 덤프 생성 시 테이블 잠금 수행됨
• 슬레이브에서 덤프 임포트 완료됨
• CHANGE MASTER TO로 SSL 옵션 포함하여 설정됨
• SHOW SLAVE STATUS\G에서 복제 정상 확인됨

간단한 의사결정 흐름도

flowchart TD
  A[복제 필요 여부 판단] --> B{네트워크 보안}
  B -->|전용 네트워크| C[SSL 필수 아님, 그러나 권장]
  B -->|공용/불확실| D[SSL 사용]
  D --> E{MySQL 버전 SSL 지원?}
  E -->|예| F[인증서 생성 및 설정]
  E -->|아니오| G[MySQL 업그레이드 또는 재빌드]
  F --> H[마스터 설정]
  H --> I[데이터 덤프 및 동기화]
  I --> J[슬레이브 설정 및 검증]

용어집(한 줄 정의)

• 마스터: 복제의 원본이 되는 MySQL 서버.
• 슬레이브: 마스터의 변경을 적용받는 복제 대상 서버.
• 바이너리 로그(binlog): 마스터에서 발생한 변경을 기록하는 파일.
• FLUSH TABLES WITH READ LOCK: 데이터 일관성 확보를 위해 테이블을 읽기 전용으로 잠그는 명령.

요약

• 이 가이드는 CentOS 5.4 환경에서 MySQL 복제를 SSL로 보호하는 전체 과정을 다룹니다.
• SSL 활성화, 인증서 생성, 마스터와 슬레이브 설정, 데이터 덤프 및 검증 절차를 포함합니다.
• 운영 단계에서 보안 하드닝, 모니터링, 롤백 계획을 반드시 수립하세요.

Important: 이 문서는 일반적인 기술 가이드입니다. 환경별 차이로 일부 명령이나 경로가 달라질 수 있습니다. 변경 전 항상 설정 파일과 데이터를 백업하세요.