[Infra Ops] DDL 반영 절차와 Migration 안전 운영

1. 개발 DB (dev)     → SQL 작성 및 기본 검증
2. 스테이징 DB (stg) → 운영과 동일한 데이터 구조로 검증
3. 운영 DB (prod)    → 체크리스트 통과 후 최종 반영

# 영향 테이블 크기 확인
mysql -h db-server -u dba -p mydb -e "
  SELECT
    table_name,
    table_rows,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
  FROM information_schema.tables
  WHERE table_schema = 'mydb'
  AND table_name = 'users';
"

# 현재 접속 세션 수 확인
mysql -h db-server -u dba -p mydb -e "SHOW PROCESSLIST;"
mysql -h db-server -u dba -p mydb -e "SELECT COUNT(*) FROM information_schema.processlist;"

[변경 요청]  "users에 phone 컬럼 추가"
   │
   ① 작성        변경 SQL + 되돌릴 UNDO SQL을 한 쌍으로   (V10__add / V10__undo)
   │              → 비가역 DDL(DROP·TRUNCATE)은 여기서 표시하고 백업을 전제로
   │
   ② 개발 검증    dev DB에서 실행 → 문법·제약 충돌·결과 확인
   │
   ③ 스테이징 실측 운영과 같은 행수 테이블에서 time으로 Lock·소요 시간 측정
   │
   ④ 방식 판단    크기·Lock 영향으로 결정
   │              작으면 직접 ALTER / 크면 pt-osc·gh-ost (온라인)
   │
   ⑤ 백업·시점    논리·물리 백업 확인 + 트래픽 저점/점검창 확보
   │
   ⑥ 운영 적용    SQL 또는 온라인 도구로 반영
   │              → 복제 지연·PROCESSLIST 감시
   │
   ⑦ 검증         SHOW COLUMNS·DESCRIBE로 확인
   ▼              이상 → 준비한 UNDO SQL로 롤백
[반영 완료 또는 안전 롤백]

# 1. 백업 확인 (반드시 먼저)
ls -lh /backup/mydb_$(date +%Y%m%d)*.sql 2>/dev/null || echo "WARNING: 오늘 백업 없음"

# 2. DDL 파일 내용 최종 확인
cat /tmp/V10__add_user_phone_column.sql
# ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email;

# 3. 스테이징에서 실행 시간 측정 (운영 전 반드시)
time mysql -h stg-db-server -u dba -p mydb < /tmp/V10__add_user_phone_column.sql

# 4. 운영 DB 적용
mysql -h db-server -u dba -p mydb < /tmp/V10__add_user_phone_column.sql

# 5. 적용 결과 확인
mysql -h db-server -u dba -p mydb -e "SHOW COLUMNS FROM users LIKE 'phone';"

# 6. 스키마 전체 확인
mysql -h db-server -u dba -p mydb -e "DESCRIBE users;"

-- V10__add_user_phone_column.sql (적용)
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email;

-- V10__undo_add_user_phone_column.sql (롤백용)
ALTER TABLE users DROP COLUMN phone;

# 잘못 적용됐을 때 롤백
mysql -h db-server -u dba -p mydb < /tmp/V10__undo_add_user_phone_column.sql
mysql -h db-server -u dba -p mydb -e "DESCRIBE users;"  # 컬럼 사라졌는지 확인

V{버전}__{설명}.sql

-- V10__add_user_phone_column.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email;
CREATE INDEX idx_users_phone ON users(phone);

# Spring Boot + Flyway 마이그레이션 실행
# 애플리케이션 기동 시 자동 실행, 또는 수동 실행:
mvn flyway:migrate -Dflyway.url=jdbc:mysql://db-server:3306/mydb \
    -Dflyway.user=dba -Dflyway.password=${DB_PASSWORD}

# 마이그레이션 이력 확인
mvn flyway:info

# 또는 DB에서 직접 확인
mysql -h db-server -u dba -p mydb -e "SELECT * FROM flyway_schema_history ORDER BY installed_rank DESC LIMIT 10;"

+--------------+---------+------------------------------+---------+---------------------+---------+
| installed_rank | version | description                 | success | installed_on        | checksum|
+--------------+---------+------------------------------+---------+---------------------+---------+
|           10 | 10      | add user phone column        |       1 | 2026-05-30 10:30:00 | 12345678|
|            9 | 9       | add product category         |       1 | 2026-05-28 15:00:00 | 87654321|

# pt-online-schema-change 설치
yum install percona-toolkit
# 또는
apt-get install percona-toolkit

# 기본 사용법 (컬럼 추가 예시)
pt-online-schema-change \
  --alter "ADD COLUMN phone VARCHAR(20) NULL AFTER email" \
  --host=db-server \
  --user=dba \
  --password=${DB_PASSWORD} \
  --database=mydb \
  --table=users \
  --execute

# 실행 전 dry-run (실제 실행 없이 계획만 출력)
pt-online-schema-change \
  --alter "ADD COLUMN phone VARCHAR(20) NULL AFTER email" \
  --host=db-server --user=dba --password=${DB_PASSWORD} \
  --database=mydb --table=users \
  --dry-run

# 진행 상황 모니터링 (별도 터미널)
watch -n 5 "mysql -h db-server -u dba -p mydb -e 'SHOW TABLE STATUS LIKE \"_users_new\"'"