【ITニュース解説】Understanding MySQL Backup with Consistent Snapshot and MDL Locks

システムエンジニアがデータベースを扱う上で、データのバックアップは非常に重要な作業だ。特に、MySQLのようなリレーショナルデータベースでは、データの整合性を保ちながらバックアップを取ることが求められる。本記事では、MySQLで「一貫性のあるスナップショット」を使ってバックアップを取る仕組みと、その際に発生しうる問題点について詳しく解説する。

データベースのバックアップには、大きく分けて物理バックアップと論理バックアップがある。ここでは、mysqldumpのような論理バックアップに焦点を当てる。論理バックアップでは、データベースの内容をSQL文の形式でエクスポートする。このとき、もしバックアップ中にデータが更新されてしまうと、エクスポートされたデータは整合性がとれていない、つまり「壊れた」状態になりかねない。例えば、あるテーブルからデータを読み込んでいる最中に、別のテーブルとの関連性が更新されてしまうと、復元した際にデータがおかしくなってしまう可能性があるのだ。これを防ぐために、バックアップを開始した時点でのデータベースの状態を「一貫したスナップショット」として捉え、そのスナップショットに基づいてデータを取得する必要がある。

一貫したスナップショットによるバックアップを実現するための具体的な手順を見ていこう。これは、トランザクションという概念を巧みに利用する方法だ。

まず、データベースセッションの「隔離レベル」をREPEATABLE READに設定する。これは、トランザクション内で同じデータを複数回読み込んだ場合、常に最初の読み込み時と同じデータが見えることを保証するための設定だ。バックアップ中にデータが変更されても、このトランザクション内では常に同じデータが見える、という安定した状態を作り出す。

次に、START TRANSACTION WITH CONSISTENT SNAPSHOT;という命令でトランザクションを開始する。この命令が実行された瞬間のデータベースの状態が、まさに「一貫したスナップショット」として固定される。このトランザクション内で実行されるすべての読み取り操作は、このスナップショット時点のデータしか見ることができない。これにより、バックアップ中に外部でどれだけデータが変更されても、バックアップされるデータは一貫性が保たれる。

バックアップ対象のテーブルが複数ある場合、あるテーブルのデータ取得中に、別のテーブルのスキーマ（テーブルの構造定義）が変更されると問題が発生することがある。そこで、特定のテーブルのスキーマ情報（SHOW CREATE TABLE）を取得した後、そのテーブルのデータを取得（SELECT * FROM）する一連の操作の途中に、「セーブポイント」という一時的な目印を設定する。SAVEPOINT sp;という命令でセーブポイントspを設定する。

テーブルのスキーマとデータを取得したら、最後にROLLBACK TO SAVEPOINT sp;という命令を実行して、先ほど設定したセーブポイントに戻る。ここで重要なのが「MDL（メタデータロック）」という概念だ。MySQLでは、テーブルの構造（メタデータ）に対する変更（DDL操作）を防ぐために、MDLというロック機構を使用する。通常のSELECT操作でも、MDLの読み取りロックが取得される。もしこの読み取りロックが長時間保持されると、テーブル構造を変更したい他の処理（DDL）がブロックされ、システム全体の処理が遅延してしまう可能性がある。セーブポイントにロールバックすることで、このMDL読み取りロックを早期に解放し、他のDDL操作がスムーズに進めるようにするのだ。これにより、バックアッププロセス全体を通して一貫性は保ちつつ、他の処理への影響を最小限に抑えることができる。

しかし、バックアップ中にテーブルの構造を変更するDDL（データ定義言語）ステートメントが到着した場合、状況は複雑になる。いくつかのシナリオが考えられる。

一つ目のシナリオは、バックアップがまだテーブルのスキーマを取得する前（SHOW CREATE TABLE実行前）にDDLが実行された場合だ。この場合、バックアップは新しいスキーマ情報に基づいて処理を続行するため、特に問題は発生しない。新しいスキーマがそのままバックアップされる。

二つ目のシナリオは、バックアップがテーブルのスキーマを取得した後（SHOW CREATE TABLE実行後）、データの読み込みを開始する前（SELECT * FROM実行前）にDDLが実行された場合だ。この状況では、テーブルの定義が変更されたにもかかわらず、バックアップは古いスキーマ情報を使ってデータを読み込もうとする。これにより、バックアップ処理は「テーブル定義が変更されました」というエラーメッセージを出して終了してしまう。これは、バックアップが失敗するケースであり、再度バックアップをやり直す必要がある。

さらに厄介なシナリオは、バックアップがテーブルのスキーマを取得した後、そしてセーブポイントへのロールバックが実行されるまでの間、つまりデータを読み込んでいる最中にDDLが実行される場合だ。この期間中、バックアッププロセスはMDL読み取りロックを保持しているため、新しいDDL操作はこのロックによってブロックされてしまう。DDL操作はMDL読み取りロックが解放されるまで待機させられ、データベースのレプリケーション（データベース間のデータ同期）も停止してしまうことがある。この結果、レプリケーションの遅延が発生し、最悪の場合、サービス全体のパフォーマンスに影響を与える可能性もある。このMDL読み取りロックは、セーブポイントへのロールバックが実行されるまで解除されない。

最後のシナリオは、セーブポイントへのロールバックがすでに完了し、MDLロックが解放された後にDDLが実行された場合だ。この場合、DDLはスムーズに実行され、バックアッププロセスには影響がない。ただし、バックアップされたデータはDDL適用前の古いスキーマに基づいている点に注意が必要だ。

これらのシナリオからわかる主要な教訓は次のとおりだ。WITH CONSISTENT SNAPSHOTは、トランザクションレベルで一貫性のあるデータビューを作成するために不可欠な機能である。そして、セーブポイントを適切に利用することで、MDLロックを必要な期間だけ保持し、可能な限り早く解放することで、DDL操作によるブロックやレプリケーション遅延のリスクを減らすことができる。しかし、バックアップ中にDDLがいつ実行されるかによって、バックアップが成功するか、失敗するか、あるいはシステム全体の遅延を引き起こすかが変わるため、DDLのタイミングを慎重に管理することが重要になる。システムエンジニアとしては、これらの仕組みを理解し、安全かつ効率的なバックアップ戦略を立てることが求められるだろう。