mysqldumpは--opt使うなら--single-transactionもつけるのがオススメ
始めに
本番環境ではないのですが、検証環境でやらかしてしまったインシデントなのでブログにしてお焚き上げします。
やりたかったことは検証環境で不具合が発生していたので、ローカルで検証するために検証環境のDBをエクスポートして、ローカルで再現させることでした。しかし、そのエクスポートを実施したところ、検証環境が機能しなくなったので事象を共有します。
環境
- MySQL
- 8.0.23
まとめ
mysqldumpのオプションに--optを付与するなら、--single-transactionもつけるべき。
問題の詳細と解決策
3年ほど使用してきた信頼と実績のある次のコマンドでDBをエクスポートしていました。
mysqldump -u operator -p --opt --set-gtid-purged=OFF --add-drop-database --no-tablespaces --databases stg --no-tablespaces > stg.dump.sql
今まで問題がなかったのですが、今回に限り問題が発生してしまいました。
内容としてはエクスポート時にすべてのテーブルをロックをしたため、DBが応答せず、APIがタイムアウトしていました。原因としては、--optを使用したためです。--optは糖衣構文で次の組み合わせの短縮形です。
- --add-drop-table
- --add-locks
- --create-options
- --disable-keys
- --extended-insert
- --lock-tables
- --quick
- --set-charset
こちらを読むと分かるとおり、--add-locksと--lock-tablesも含まれています。意図的にテーブルロックを行ってからエクスポートを行っています。テーブルをロックすることで、確実に処理開始当時と同一の内容をエクスポートできますが、その代わりに既存処理で部分的でもテーブルロックがかかっていると全テーブルのロックをかけてしまいます。今回の場合、エクスポート開始時にデッドロックが起きていた結果、全テーブルのロックをかけたままエクスポート側だけプロセスが残り続けてしまい、APIがタイムアウトしていました。
--single-transactionを付与することで--lock-tablesは無効化されるため、--optを使用する場合は--single-transactionを併記したほうがよいでしょう。
mysqldump -u operator -p --single-transaction --opt --set-gtid-purged=OFF --add-drop-database --no-tablespaces --databases stg --no-tablespaces > stg.dump.sql
万が一プロセスが残ってしまった場合
※ 実際は権限がなくてこの操作をしていないので誤りがあるかもしれません。
# プロセスIDを調べる
SELECT * FROM performance_schema.threads WHERE type = 'FOREGROUND';
# または
SHOW PROCESSLIST;
# プロセスを殺す
KILL [process_id];
ソースコード
なし。
終わりに
大元のやりたかったこととしては、検証環境でデッドロックや別のタイミングではOOMが発生していたので、ローカルで行った際に再現するか調査するためにデータエクスポートをしようとしたのですが、そのときの処理が誤っていました。
特に3年ほど使用していたコマンドだったので疑っていなかったのもあり、これほどの被害を出すとは思ってもみなかったです。
本番環境ではエクスポートを使用せずにスナップショットから新規検証用DBを用意しているから本番では発生しないものの、たくさんの検証の手を止めてしまったインシデントとして残しておきます…。
Discussion