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