MySQL 실시간 동기화(복제) 구축하는 방법

MySQL 실시간 동기화(복제) 구축하는 방법

MySQL에서 실시간 동기화를 설정하려면 일반적으로 MySQL 복제(Replication) 기능을 사용합니다. 이를 통해 마스터 데이터베이스의 데이터를 하나 이상의 슬레이브 데이터베이스에 실시간으로 동기화할 수 있습니다. 아래는 MySQL 복제를 설정하는 과정을 단계별로 정리한 가이드입니다. 따라 하기 쉽게 간결하고 명확하게 설명하겠습니다.

1. 사전 준비

• 환경: MySQL 5.7 이상 (최신 버전 권장).
• 서버: 마스터 서버 1대, 슬레이브 서버 1대 이상.
• 권한: MySQL 루트 계정 또는 복제 설정 권한이 있는 계정.
• 네트워크: 마스터와 슬레이브 간 방화벽이 열려 있어야 하며, 동일 네트워크 내에서 통신 가능해야 함.
• 데이터베이스 백업: 마스터 DB의 초기 데이터를 슬레이브로 복사하기 위해 백업 준비.

---

2. 마스터 서버 설정

마스터 서버에서 MySQL 설정 파일을 수정하고 복제를 활성화합니다.

(1) MySQL 설정 파일 수정

1. MySQL 설정 파일(my.cnf 또는 my.ini)을 엽니다.
   • 리눅스: /etc/my.cnf 또는 /etc/mysql/my.cnf
   • 윈도우: C:\ProgramData\MySQL\MySQL Server X.Y\my.ini
2. [mysqld] 섹션에 아래 설정을 추가합니다:

   [mysqld]
   server-id=1                # 마스터 서버의 고유 ID
   log_bin=mysql-bin          # 바이너리 로그 활성화
   binlog_format=ROW          # ROW 포맷 사용 (안정적)

3. MySQL 서비스를 재시작합니다:

   sudo systemctl restart mysql   # 리눅스

(2) 복제 전용 사용자 생성

1. MySQL 클라이언트에서 마스터 DB에 접속:

   mysql -u root -p

2. 복제 전용 사용자 생성 및 권한 부여:

   CREATE USER 'repl_user'@'%' IDENTIFIED BY 'your_password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
   FLUSH PRIVILEGES;

   • 'repl_user'와 'your_password'는 원하는 값으로 변경하세요.
   • '%'는 모든 호스트에서 접속 가능하도록 설정. 특정 IP로 제한하려면 'repl_user'@'slave_ip'로 지정.

(3) 마스터 상태 확인

1. 마스터의 바이너리 로그 파일 이름과 위치를 확인:

   SHOW MASTER STATUS;

   출력 예시:

   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      154 |              |                  |
   +------------------+----------+--------------+------------------+

   • File과 Position 값을 메모해둡니다. 슬레이브 설정 시 필요합니다.

(4) 마스터 데이터 백업

1. 마스터 DB를 잠가 데이터 일관성 유지:

   FLUSH TABLES WITH READ LOCK;

2. 백업 수행 (예: mysqldump 사용):

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

3. 잠금 해제:

   UNLOCK TABLES;

4. 백업 파일(backup.sql)을 슬레이브 서버로 전송 (예: SCP 사용):

   scp backup.sql user@slave_server:/path/to/backup.sql

---

3. 슬레이브 서버 설정

슬레이브 서버에서 MySQL 설정을 수정하고 마스터와 연결합니다.

(1) MySQL 설정 파일 수정

1. 슬레이브 서버의 MySQL 설정 파일(my.cnf 또는 my.ini)을 엽니다.
2. [mysqld] 섹션에 아래 설정을 추가:

   [mysqld]
   server-id=2                # 슬레이브 서버의 고유 ID (마스터와 달라야 함)
   read_only=1                # 슬레이브를 읽기 전용으로 설정 (선택)

3. MySQL 서비스 재시작:

   sudo systemctl restart mysql

(2) 마스터 데이터 복원

1. 슬레이브 서버에서 백업 데이터를 복원:

   mysql -u root -p < /path/to/backup.sql

(3) 슬레이브 복제 설정

1. MySQL 클라이언트에서 슬레이브 DB에 접속:

   mysql -u root -p

2. 마스터와 연결 설정:

   CHANGE MASTER TO
       MASTER_HOST='master_ip',          -- 마스터 서버 IP
       MASTER_USER='repl_user',          -- 복제 사용자
       MASTER_PASSWORD='your_password',  -- 복제 사용자 비밀번호
       MASTER_LOG_FILE='mysql-bin.000001', -- 마스터의 바이너리 로그 파일 (SHOW MASTER STATUS에서 확인)
       MASTER_LOG_POS=154;               -- 마스터의 로그 위치 (SHOW MASTER STATUS에서 확인)

   • MASTER_HOST, MASTER_LOG_FILE, MASTER_LOG_POS는 실제 값으로 대체.
3. 슬레이브 복제 시작:

   START SLAVE;

(4) 슬레이브 상태 확인

1. 슬레이브 상태를 확인:

   SHOW SLAVE STATUS\G

2. 중요한 출력 항목 확인:
   • Slave_IO_Running: Yes → 마스터와의 연결 상태 정상.
   • Slave_SQL_Running: Yes → 복제 SQL 실행 정상.
   • Seconds_Behind_Master: 0 → 마스터와 동기화 지연 없음.
   • 오류가 있다면 Last_Error 항목을 확인해 디버깅.

---

4. 테스트 및 모니터링

1. 데이터 테스트:
   • 마스터에서 테이블에 데이터를 삽입/수정:

     USE test_db;
     INSERT INTO test_table (column1) VALUES ('test');

   • 슬레이브에서 동일한 데이터가 복제되었는지 확인:

     SELECT * FROM test_db.test_table;

2. 모니터링:
   • 주기적으로 SHOW SLAVE STATUS\G를 실행해 동기화 상태 확인.
   • 오류 발생 시 MySQL 로그 파일(/var/log/mysql/error.log) 점검.
   • 필요 시 모니터링 도구 (예: Percona Monitoring, Zabbix) 사용.

---

5. 추가 팁

• 보안:
  • 복제 사용자(repl_user)의 비밀번호를 강력하게 설정.
  • 방화벽에서 MySQL 포트(기본 3306)를 마스터-슬레이브 간 통신만 허용.
  • SSL로 복제 연결 암호화 (필요 시 MASTER_SSL=1 추가).
• 장애 처리:
  • 슬레이브가 마스터를 따라가지 못하면 STOP SLAVE; 후 CHANGE MASTER TO로 로그 위치 재설정.
  • 마스터 장애 시 슬레이브를 새 마스터로 승격(고급 설정 필요).
• 성능 최적화:
  • 복제 지연 최소화를 위해 binlog_format=ROW 사용.
  • 대규모 데이터베이스의 경우 병렬 복제 활성화:

    slave_parallel_workers=4
    slave_parallel_type=LOGICAL_CLOCK

---

6. 문제 해결

• "Access denied" 오류: repl_user의 권한 또는 비밀번호 확인.
• "Could not connect" 오류: 방화벽, 네트워크, MASTER_HOST IP 확인.
• 복제 지연: Seconds_Behind_Master 값 확인 후 네트워크 속도 또는 슬레이브 성능 점검.

---

이 가이드를 따라 하면 MySQL 마스터-슬레이브 복제를 설정해 실시간 동기화를 구현할 수 있습니다.