MySQL Runbook

症状 MySQLに接続しようとすると ERROR 2003 (HY000): Can't connect to MySQL server on 'hostname' (111 "Connection refused") が表示される 結論：まずこれを確認 MySQLプロセスが起動しているか確認する MySQLが正しいポート（デフォルト3306）でリッスンしているか確認する bind-addressの設定がアクセス元を許可しているか確認する トラブルシューティングフロー flowchart TD A[Connection refused発生] --> B{MySQLプロセス起動中?} B -->|No| C[MySQLを起動する] B -->|Yes| D{ポート3306でリッスン?} D -->|No| E[my.cnfのport設定を確認] D -->|Yes| F{bind-address設定は?} F -->|127.0.0.1のみ| G[bind-addressを変更] F -->|0.0.0.0| H{ファイアウォール確認} H -->|ブロック| I[ファイアウォール設定変更] H -->|許可済み| J[MySQLユーザー権限確認] C --> K[起動ログを確認] K -->|エラーあり| L[エラー内容に応じた対処] K -->|正常起動| D よくある原因 MySQLプロセスが停止している - サーバー再起動後に自動起動していない bind-addressが127.0.0.1に設定されている - ローカル以外からの接続を受け付けない ポート番号が異なる - 設定変更やバージョン違いで3306以外を使用 ファイアウォールでブロックされている - iptables/firewalld/ufw等で3306が閉じている ソケットファイルの問題 - /var/lib/mysql/mysql.sock が存在しない・権限不正 MySQLが起動途中でクラッシュしている - ディスク容量不足やメモリ不足 SELinuxによるブロック - SELinuxが有効な環境でポリシー違反 確認手順 ステップ1: MySQLプロセスを確認する # systemdの場合 systemctl status mysql # または systemctl status mysqld # プロセス直接確認 ps aux | grep mysql 🔍 チェックポイント: active (running) と表示されれば起動中 ...

症状 MySQLで Deadlock found when trying to get lock; try restarting transaction エラーが発生する 結論：まずこれを確認 SHOW ENGINE INNODB STATUS でデッドロックの詳細を確認する 直近のデッドロック情報から、競合しているトランザクションを特定する アプリケーション側でリトライ処理が実装されているか確認する トラブルシューティングフロー flowchart TD A[Deadlockエラー発生] --> B{頻度は?} B -->|まれに発生| C[アプリでリトライ実装] B -->|頻繁に発生| D[SHOW ENGINE INNODB STATUS] D --> E{競合箇所を特定} E --> F[インデックス不足?] E --> G[トランザクション長すぎ?] E --> H[ロック順序不統一?] F -->|Yes| I[インデックス追加] G -->|Yes| J[トランザクション分割] H -->|Yes| K[ロック順序を統一] C --> L[監視を継続] I --> L J --> L K --> L よくある原因 インデックス不足 - フルテーブルスキャンにより広範囲のロックが発生している トランザクションが長い - ロック保持時間が長く、競合が発生しやすい ロック順序の不統一 - 複数トランザクションが異なる順序でリソースをロックしている 同時実行数が多い - 並列処理による競合が増加している ギャップロック - 存在しない行への挿入で意図しないロック範囲が発生している 外部キー制約 - 親テーブルと子テーブルで同時に操作が発生している 確認手順 ステップ1: デッドロック情報を取得する mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" | grep -A 50 "LATEST DETECTED DEADLOCK" 🔍 チェックポイント: LATEST DETECTED DEADLOCK セクションにデッドロックの詳細が表示される ...

症状 MySQLで「InnoDB: Tablespace is corrupted」「Cannot load from mysql.proc」などのエラーが表示され、テーブルにアクセスできない 結論：まずこれを確認 SHOW ENGINE INNODB STATUS\G でエラー詳細を確認 エラーログ（/var/log/mysql/error.log）で破損箇所を特定 バックアップの有無を確認してから復旧作業を開始 トラブルシューティングフロー flowchart TD A[テーブルスペース破損エラー] --> B{MySQLは起動している?} B -->|No| C[innodb_force_recovery で起動試行] B -->|Yes| D{エラーログを確認} D --> E{破損箇所は?} E -->|システムテーブル| F[mysql.proc等のシステム復旧] E -->|ユーザーテーブル| G[該当テーブルの復旧] E -->|ibdata1| H[全体復旧が必要] C --> I{起動した?} I -->|Yes| J[データをダンプ] I -->|No| K[recovery levelを上げて再試行] J --> L[再構築] よくある原因 ディスク障害: 物理的なディスクエラーによるデータファイル破損 電源断・強制終了: トランザクション途中での異常終了 ディスク容量不足: 書き込み途中でディスクフル発生 MySQLの異常終了: OOM Killerやプロセスkillによる中断 ファイルシステム破損: ext4/xfsなどのファイルシステムレベルの問題 バージョンアップ失敗: MySQL/MariaDBのアップグレード時の互換性問題 手動でのファイル操作: .ibdファイルや.frmファイルの誤削除・移動 確認手順 ステップ1: エラーログを確認する sudo tail -100 /var/log/mysql/error.log # または sudo tail -100 /var/log/mysqld.log 🔍 チェックポイント: corrupted、cannot load、page checksum mismatch などのキーワードを探す ...

症状 MySQLに接続しようとすると「ERROR 1040 (HY000): Too many connections」が表示される 結論：まずこれを確認 現在の接続数を確認：SHOW STATUS LIKE 'Threads_connected'; 最大接続数を確認：SHOW VARIABLES LIKE 'max_connections'; 不要な接続があれば切断、なければ max_connections を一時的に増やす トラブルシューティングフロー flowchart TD A[Too many connections発生] --> B{MySQLに接続できる?} B -->|Yes| C[接続数を確認] B -->|No| D[rootで緊急接続を試行] C --> E{接続数が上限に近い?} E -->|Yes| F{不要な接続がある?} E -->|No| G[別の原因を調査] F -->|Yes| H[不要な接続を切断] F -->|No| I[max_connectionsを増やす] D --> J{緊急接続できた?} J -->|Yes| C J -->|No| K[MySQLプロセスを確認] H --> L[根本原因を調査] I --> L よくある原因 アプリケーションの接続リーク - 接続を閉じずに放置しているコードがある max_connections が小さすぎる - デフォルト値（151）がアクセス量に対して不足 スロークエリによる接続滞留 - 遅いクエリが接続を長時間占有している コネクションプールの設定ミス - プールサイズが大きすぎる、またはリーク 急激なアクセス増加 - 想定外のトラフィックスパイク バッチ処理の同時実行 - 複数のバッチが同時に大量接続を生成 監視ツールの過剰接続 - 監視システムが多数の接続を維持している 確認手順 ステップ1: MySQLに接続できるか確認する mysql -u root -p -e "SELECT 1" 🔍 チェックポイント: 接続できれば次のステップへ。接続できない場合はステップ1-Bへ ...

症状 MySQL レプリケーションで Seconds_Behind_Master が増加し続ける、または高い値のまま減少しない 結論：まずこれを確認 SHOW SLAVE STATUS\G で Slave_IO_Running と Slave_SQL_Running が両方 Yes か確認 Seconds_Behind_Master の値と推移を確認（増加中か、一定か） スレーブ側の CPU・ディスク I/O 負荷を確認 トラブルシューティングフロー flowchart TD A[Seconds_Behind_Master が高い] --> B{Slave_IO_Running は Yes?} B -->|No| C[ネットワーク/マスター接続の問題] B -->|Yes| D{Slave_SQL_Running は Yes?} D -->|No| E[SQL スレッドエラーを確認] D -->|Yes| F{遅延は増加中?} F -->|Yes| G[スレーブの処理能力不足] F -->|No| H{大量の更新があった?} H -->|Yes| I[一時的な遅延 - 待機] H -->|No| J[長時間クエリ/ロック確認] C --> K[マスター側ログ・ネットワーク確認] E --> L[Last_SQL_Error を確認] G --> M[スレーブスペック/設定確認] J --> N[SHOW PROCESSLIST 確認] よくある原因 マスターでの大量更新 - バッチ処理やデータ移行で一時的に遅延が発生 スレーブのディスク I/O 不足 - HDD使用時やIOPS制限がある環境で発生しやすい スレーブの CPU 不足 - シングルスレッドの SQL スレッドがボトルネックになる 長時間実行クエリ - マスターで実行された ALTER TABLE などがスレーブで再実行される ネットワーク帯域不足 - バイナリログの転送が追いつかない テーブルロック競合 - スレーブ上の読み取りクエリとの競合 不適切な設定 - sync_binlog、innodb_flush_log_at_trx_commit の設定不整合 確認手順 ステップ1: レプリケーション状態を確認する mysql -e "SHOW SLAVE STATUS\G" | grep -E "(Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_SQL_Error|Master_Log_File|Relay_Master_Log_File)" 🔍 チェックポイント: Slave_IO_Running: Yes、Slave_SQL_Running: Yes であれば基本的な接続は正常 ...

症状 MySQLでINSERTまたはUPDATE実行時に「Cannot add or update a child row: a foreign key constraint fails」エラーが発生する。 結論：まずこれを確認 挿入・更新しようとしている外部キーの値が、参照先テーブルに存在するか確認 存在しない場合は、先に参照先テーブルにデータを追加する データ型・文字コード・照合順序が一致しているか確認 トラブルシューティングフロー flowchart TD A[外部キー制約エラー発生] --> B{エラーメッセージの<br>制約名を確認} B --> C[参照先テーブル・カラムを特定] C --> D{参照先に該当データ<br>が存在する?} D -->|No| E[参照先にデータを追加] D -->|Yes| F{データ型は一致?} F -->|No| G[カラムのデータ型を修正] F -->|Yes| H{文字コード・照合順序<br>は一致?} H -->|No| I[文字コードを統一] H -->|Yes| J[NULL値や空文字を確認] E --> K[再度INSERT/UPDATE実行] G --> K I --> K J --> K よくある原因 参照先にデータが存在しない - 最も多い原因。外部キーの値が親テーブルに未登録 データ型の不一致 - INT と BIGINT、UNSIGNED の有無など微妙な違い 文字コードの不一致 - utf8 と utf8mb4 の違いなど 照合順序（COLLATION）の不一致 - utf8_general_ci と utf8_unicode_ci など NULL値の扱い - 外部キーカラムがNOT NULLなのにNULLを挿入しようとしている トランザクション内での順序問題 - 親レコードをCOMMIT前に子レコードを挿入 一時的に制約を無効化した後の再有効化 - 不整合データが残っている状態 確認手順 ステップ1: エラーメッセージから制約名を特定する # エラー例 # Cannot add or update a child row: a foreign key constraint fails # (`database`.`child_table`, CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) # REFERENCES `parent_table` (`id`)) 🔍 チェックポイント: CONSTRAINT の後の fk_parent_id が制約名、REFERENCES の後が参照先 ...

症状 MySQLで大きなデータを INSERT/UPDATE しようとすると「Packet too large」または「Got a packet bigger than ‘max_allowed_packet’ bytes」エラーが表示される。 結論：まずこれを確認 SHOW VARIABLES LIKE 'max_allowed_packet'; で現在の設定値を確認 送信しようとしているデータサイズと比較 設定値が小さければ my.cnf で増やす トラブルシューティングフロー flowchart TD A[Packet too large エラー発生] --> B{エラー発生箇所は?} B -->|クライアント側| C[クライアントの max_allowed_packet 確認] B -->|サーバー側| D[サーバーの max_allowed_packet 確認] C --> E{設定値 < データサイズ?} D --> E E -->|Yes| F[設定値を増やす] E -->|No| G[データ分割を検討] F --> H{一時的な変更?} H -->|Yes| I[SET GLOBAL で変更] H -->|No| J[my.cnf を編集] J --> K[MySQL 再起動] I --> L[動作確認] K --> L よくある原因 max_allowed_packet のデフォルト値が小さい - MySQL 5.7 以前はデフォルト 4MB、8.0 は 64MB BLOB/TEXT カラムに大きなファイルを格納 - 画像・PDF などのバイナリデータ挿入時に発生しやすい 大量の行を一括 INSERT - VALUES が多すぎてパケットサイズ超過 クライアントとサーバーで設定が異なる - 両方の設定が必要な場合がある レプリケーション環境での設定漏れ - マスター/スレーブで設定が異なる mysqldump のリストア時 - ダンプファイルに大きなデータが含まれている 確認手順 ステップ1: 現在の設定値を確認する mysql -u root -p -e "SHOW VARIABLES LIKE 'max_allowed_packet';" 🔍 チェックポイント: 値がバイト単位で表示される（例: 67108864 = 64MB） ...

症状 MySQLで Lock wait timeout exceeded; try restarting transaction エラーが発生し、クエリが実行できない。 結論：まずこれを確認 SHOW ENGINE INNODB STATUS\G でロック待ちのトランザクションを確認 SHOW PROCESSLIST で長時間実行中のクエリを特定 原因のトランザクションを KILL するか、完了を待つ トラブルシューティングフロー flowchart TD A[Lock wait timeout発生] --> B{SHOW PROCESSLIST確認} B --> C{長時間クエリあり?} C -->|Yes| D[該当クエリを特定] C -->|No| E[INNODB STATUSを確認] D --> F{KILLして良い?} F -->|Yes| G[KILL プロセスID] F -->|No| H[完了を待つ] E --> I{ロック情報あり?} I -->|Yes| J[ロック元を特定] I -->|No| K[アプリケーション側を確認] J --> F G --> L[再実行して確認] H --> L K --> M[コネクション管理を確認] よくある原因 長時間トランザクションが未コミット - BEGIN後にCOMMIT/ROLLBACKされていない 大量データの更新処理 - UPDATE/DELETEが多くの行をロック中 デッドロックの発生 - 複数トランザクションが互いにロック待ち innodb_lock_wait_timeout が短い - デフォルト50秒では足りない場合がある 外部キー制約によるロック伝播 - 親テーブルへのロックが子テーブルに影響 インデックス未使用のUPDATE/DELETE - テーブルスキャンで広範囲をロック アプリケーションのコネクションリーク - トランザクションが放置されている 確認手順 ステップ1: 現在のプロセスを確認する mysql -u root -p -e "SHOW FULL PROCESSLIST\G" 🔍 チェックポイント: Time が大きく、State が Locked や Waiting for table metadata lock のプロセスがないか確認 ...

症状 MySQLのバイナリログ（binlog）がディスク容量を圧迫し、ディスクフルまたは空き容量不足が発生する。 結論：まずこれを確認 SHOW BINARY LOGS; でバイナリログのサイズと数を確認する レプリケーション使用中なら SHOW SLAVE STATUS; でスレーブの読み取り位置を確認する 不要なログを PURGE BINARY LOGS で削除する トラブルシューティングフロー flowchart TD A[ディスク容量不足を検知] --> B{バイナリログが原因?} B -->|Yes| C{レプリケーション使用中?} B -->|No| Z[他の原因を調査] C -->|Yes| D[スレーブの読み取り位置を確認] C -->|No| E[expire_logs_days設定を確認] D --> F{スレーブが遅延している?} F -->|Yes| G[スレーブ遅延を解消] F -->|No| H[安全にPURGE可能] E --> I[保持期間を設定] G --> H I --> H H --> J[PURGE BINARY LOGSを実行] よくある原因 expire_logs_days / binlog_expire_logs_seconds が未設定 — バイナリログが自動削除されない レプリケーションスレーブの遅延 — スレーブが読み終わるまでログを保持し続ける 大量のDML実行 — INSERT/UPDATE/DELETEが多いとログが急速に肥大化する binlog_format=ROW での大規模更新 — 行単位で記録されるためログサイズが増大する 長期間のメンテナンス放置 — 定期的なログ削除が行われていない バックアップツールがログを保持 — mysqlbinlogやバックアップソフトがログを参照中 確認手順 ステップ1: バイナリログの状態を確認する mysql -u root -p -e "SHOW BINARY LOGS;" 🔍 チェックポイント: ログファイルの数と各ファイルのサイズを確認する。合計サイズがディスク使用量と一致するか確認する。 ...

症状 MySQLに接続しようとすると「Authentication plugin ‘caching_sha2_password’ cannot be loaded」または「Plugin caching_sha2_password could not be loaded」エラーが表示される。 結論：まずこれを確認 クライアントがMySQL 8.0のデフォルト認証プラグイン（caching_sha2_password）に対応しているか確認 対応していない場合、ユーザーの認証プラグインを mysql_native_password に変更する または、クライアント/ドライバをアップデートする トラブルシューティングフロー flowchart TD A[認証プラグインエラー発生] --> B{MySQLサーバーのバージョンは?} B -->|8.0以上| C{クライアントは8.0対応?} B -->|5.7以下| D[別の原因を調査] C -->|対応| E[SSL/TLS設定を確認] C -->|非対応| F{クライアント更新可能?} F -->|可能| G[クライアントをアップデート] F -->|不可| H[認証プラグインを変更] E --> I[接続文字列を確認] H --> J[mysql_native_passwordに変更] よくある原因 クライアントが古い - MySQL 8.0より前のクライアントライブラリを使用している ドライバが非対応 - 使用しているプログラミング言語のMySQLドライバが caching_sha2_password 未対応 SSL/TLSが無効 - caching_sha2_password はSSL接続またはRSA鍵交換が必要 サーバー設定の不一致 - default_authentication_plugin の設定とユーザー設定が異なる libmysqlclient が古い - システムにインストールされているMySQLクライアントライブラリが古い Docker/コンテナ環境 - 古いMySQLイメージを使用している 確認手順 ステップ1: MySQLサーバーのバージョンを確認する mysql --version または接続できる場合： ...