MySQL 8.4 InnoDB 클러스터 운영과 쿼리 최적화 핸드북

┌─────────────────────────────────────────────────┐
│                  Application                     │
└──────────────────────┬──────────────────────────┘
                       │
              ┌────────▼────────┐
              │  MySQL Router   │
              │  (R/W split)    │
              └───┬────────┬────┘
         Write    │        │   Read
         ┌────────▼──┐  ┌──▼────────┐
         │  Primary   │  │ Secondary │
         │  (node-1)  │  │ (node-2)  │
         └────────────┘  └───────────┘
                  │           │
              ┌───▼───────────▼───┐
              │    Secondary      │
              │    (node-3)       │
              └───────────────────┘
         ← Group Replication (Paxos) →

# 각 노드에서 MySQL 8.4 설치 (Ubuntu/Debian 예시)
sudo apt-get update
sudo apt-get install mysql-server-8.4 mysql-shell mysql-router

# InnoDB 엔진이 아닌 테이블 확인
mysql -u root -p -e "
  SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
  FROM information_schema.TABLES
  WHERE ENGINE != 'InnoDB'
    AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys');
"

# PRIMARY KEY 없는 테이블 확인
mysql -u root -p -e "
  SELECT t.TABLE_SCHEMA, t.TABLE_NAME
  FROM information_schema.TABLES t
  LEFT JOIN information_schema.TABLE_CONSTRAINTS c
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME
    AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
  WHERE c.TABLE_NAME IS NULL
    AND t.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
    AND t.TABLE_TYPE = 'BASE TABLE';
"

[mysqld]
# 기본 설정
server_id=1                          # 노드마다 고유값 (1, 2, 3)
bind-address=0.0.0.0
port=3306
datadir=/var/lib/mysql

# GTID 활성화
gtid_mode=ON
enforce_gtid_consistency=ON

# Binary Log
log_bin=mysql-bin
binlog_format=ROW
binlog_transaction_dependency_tracking=WRITESET
log_replica_updates=ON

# Group Replication 기본 설정
plugin_load_add=group_replication.so
group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot=OFF
group_replication_local_address="node1:33061"
group_replication_group_seeds="node1:33061,node2:33061,node3:33061"
group_replication_bootstrap_group=OFF

# InnoDB 튜닝
innodb_buffer_pool_size=4G           # 물리 메모리의 70~80%
innodb_buffer_pool_instances=4       # buffer_pool_size / 1G
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=1     # 데이터 안정성 최우선
innodb_flush_method=O_DIRECT

# 비활성화된 기본값 중 워크로드에 따라 활성화 검토
# innodb_adaptive_hash_index=ON      # OLTP 핫스팟 쿼리가 많으면 활성화
# innodb_change_buffering=all        # HDD 사용 시 활성화

# 복제 관련
replica_parallel_type=LOGICAL_CLOCK
replica_parallel_workers=4           # 4~8 범위 권장
replica_preserve_commit_order=ON

# 스토리지 엔진 제한 (InnoDB 외 사용 방지)
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

# 트랜잭션 격리 수준 (Multi-Primary 사용 시)
# transaction_isolation=READ-COMMITTED

// MySQL Shell (mysqlsh) 접속
// mysqlsh root@node1:3306

// 1. 각 인스턴스 설정 확인 및 자동 구성
dba.configureInstance('root@node1:3306', {
  clusterAdmin: 'clusteradmin',
  clusterAdminPassword: 'SecureP@ss!2026',
})
dba.configureInstance('root@node2:3306', {
  clusterAdmin: 'clusteradmin',
  clusterAdminPassword: 'SecureP@ss!2026',
})
dba.configureInstance('root@node3:3306', {
  clusterAdmin: 'clusteradmin',
  clusterAdminPassword: 'SecureP@ss!2026',
})

// 2. 첫 번째 노드에서 클러스터 생성 (Bootstrap)
shell.connect('clusteradmin@node1:3306')
var cluster = dba.createCluster('prodCluster', {
  multiPrimary: false, // Single-Primary 모드 권장
  memberWeight: 50,
  expelTimeout: 5,
  autoRejoinTries: 3,
  consistency: 'BEFORE_ON_PRIMARY_FAILOVER',
})

// 3. 나머지 노드 추가 (Clone 방식 권장)
cluster.addInstance('clusteradmin@node2:3306', {
  recoveryMethod: 'clone',
})
cluster.addInstance('clusteradmin@node3:3306', {
  recoveryMethod: 'clone',
})

// 4. 클러스터 상태 확인
cluster.status()

# Router 부트스트랩 (클러스터 메타데이터 자동 설정)
mysqlrouter --bootstrap clusteradmin@node1:3306 \
  --directory /opt/mysqlrouter \
  --user=mysqlrouter \
  --conf-use-sockets \
  --conf-bind-address=0.0.0.0

# Router 시작
/opt/mysqlrouter/start.sh

# 또는 systemd로 관리
sudo systemctl enable mysqlrouter
sudo systemctl start mysqlrouter

// MySQL Shell에서 클러스터 객체 가져오기
shell.connect('clusteradmin@node1:3306')
var cluster = dba.getCluster()

// 클러스터 전체 상태 확인
cluster.status({ extended: 1 })

// 특정 노드 상태 상세 확인
cluster.status({ extended: 2 })

// 노드 일시 제거 (유지보수)
cluster.removeInstance('clusteradmin@node2:3306', { force: false })

// 노드 재추가
cluster.addInstance('clusteradmin@node2:3306', { recoveryMethod: 'clone' })

// 클러스터 옵션 조회
cluster.options()

// Primary 수동 전환 (graceful switchover)
cluster.setPrimaryInstance('clusteradmin@node2:3306')

// Router 연결 상태 확인
cluster.listRouters()

-- Group Replication 멤버 상태 확인
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE
FROM performance_schema.replication_group_members;

-- 트랜잭션 적용 지연 확인 (각 Secondary에서 실행)
SELECT
  CHANNEL_NAME,
  COUNT_TRANSACTIONS_IN_QUEUE AS trx_in_queue,
  COUNT_TRANSACTIONS_CHECKED AS trx_checked,
  COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE AS remote_queue,
  LAST_CONFLICT_FREE_TRANSACTION
FROM performance_schema.replication_group_member_stats
WHERE CHANNEL_NAME = 'group_replication_applier';

-- Applier 워커 상태 확인
SELECT
  WORKER_ID, LAST_SEEN_TRANSACTION,
  APPLYING_TRANSACTION, LAST_APPLIED_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker
WHERE CHANNEL_NAME = 'group_replication_applier';

-- InnoDB 클러스터 메타데이터 확인
SELECT * FROM mysql_innodb_cluster_metadata.clusters;
SELECT * FROM mysql_innodb_cluster_metadata.instances;

// 장애 노드 복구 후 자동 재합류 확인
cluster.status()
// MEMBER_STATE가 RECOVERING -> ONLINE으로 변경되는지 확인

// 자동 재합류가 실패한 경우
cluster.rejoinInstance('clusteradmin@node3:3306')

// 데이터 격차가 크면 Clone으로 재합류
cluster.removeInstance('clusteradmin@node3:3306', { force: true })
cluster.addInstance('clusteradmin@node3:3306', { recoveryMethod: 'clone' })

// 새 Primary 확인
cluster.status()

// 기존 Primary 복구 후 Secondary로 재합류
cluster.rejoinInstance('clusteradmin@node1:3306')

// 살아 있는 노드에서 강제 쿼럼 복구
// 주의: 데이터 손실 가능성 존재
cluster.forceQuorumUsingPartitionOf('clusteradmin@node1:3306')

// 이후 다운된 노드 복구 후 재합류
cluster.rejoinInstance('clusteradmin@node2:3306')
cluster.rejoinInstance('clusteradmin@node3:3306')

// 1. 가장 최신 GTID를 가진 노드를 먼저 시작
// 각 노드에서 확인:
// SELECT @@gtid_executed;

// 2. 해당 노드에서 클러스터 재부팅
shell.connect('clusteradmin@node1:3306')
var cluster = dba.rebootClusterFromCompleteOutage('prodCluster')

// 3. 나머지 노드는 자동 재합류 또는 수동 합류
cluster.rejoinInstance('clusteradmin@node2:3306')
cluster.rejoinInstance('clusteradmin@node3:3306')

-- 1. 슬로우 쿼리 로그 활성화
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;        -- 1초 이상 쿼리 기록
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 2. performance_schema로 상위 슬로우 쿼리 식별
SELECT
  SCHEMA_NAME,
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_sec,
  ROUND(AVG_TIMER_WAIT / 1000000000000, 3) AS avg_sec,
  SUM_ROWS_EXAMINED AS rows_examined,
  SUM_ROWS_SENT AS rows_sent,
  ROUND(SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0), 1) AS exam_to_sent_ratio,
  FIRST_SEEN,
  LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

# 3. pt-query-digest로 슬로우 로그 분석 (Percona Toolkit)
pt-query-digest /var/log/mysql/slow.log \
  --limit=20 \
  --order-by=Query_time:sum \
  --output report > /tmp/slow_report.txt

EXPLAIN ANALYZE
SELECT o.order_id, o.order_date, c.customer_name, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date BETWEEN '2026-01-01' AND '2026-03-01'
  AND c.region = 'APAC'
GROUP BY o.order_id, o.order_date, c.customer_name
ORDER BY total DESC
LIMIT 50;

-- 자주 실행되는 쿼리 패턴
SELECT customer_id, order_date, status
FROM orders
WHERE customer_id = 12345
  AND status = 'SHIPPED'
ORDER BY order_date DESC;

-- 커버링 인덱스 생성
CREATE INDEX idx_orders_covering
  ON orders (customer_id, status, order_date DESC, order_id);

-- EXPLAIN에서 Extra: Using index 확인
EXPLAIN SELECT customer_id, order_date, status
FROM orders
WHERE customer_id = 12345
  AND status = 'SHIPPED'
ORDER BY order_date DESC;

-- 인덱스를 invisible로 변경 (옵티마이저가 무시)
ALTER TABLE orders ALTER INDEX idx_old_index INVISIBLE;

-- 일정 기간 모니터링 후 문제가 없으면 삭제
DROP INDEX idx_old_index ON orders;

-- 문제가 발생하면 즉시 복원
ALTER TABLE orders ALTER INDEX idx_old_index VISIBLE;

-- 컬럼에 히스토그램 생성
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, region WITH 100 BUCKETS;

-- 히스토그램 정보 확인
SELECT
  SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
  JSON_EXTRACT(HISTOGRAM, '$.\"number-of-buckets-specified\"') AS buckets,
  JSON_EXTRACT(HISTOGRAM, '$.\"sampling-rate\"') AS sampling_rate,
  JSON_EXTRACT(HISTOGRAM, '$.\"histogram-type\"') AS hist_type
FROM information_schema.COLUMN_STATISTICS
WHERE TABLE_NAME = 'orders';

-- 현재 버퍼 풀 히트율 확인
SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_rate;

-- 99% 이하이면 buffer_pool_size 증가 검토

-- 버퍼 풀 상세 상태
SELECT
  POOL_ID,
  POOL_SIZE,
  FREE_BUFFERS,
  DATABASE_PAGES,
  MODIFIED_DB_PAGES,
  PAGES_MADE_YOUNG,
  PAGES_NOT_MADE_YOUNG
FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- 온라인으로 버퍼 풀 크기 변경 (재시작 불필요)
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;  -- 8GB