第 7 章 バックアップとリカバリ

目次

7.1 バックアップとリカバリの種類
7.2 データベースバックアップ方法
7.3 バックアップおよびリカバリ戦略の例
7.3.1 バックアップポリシーの確立
7.3.2 リカバリへのバックアップの使用
7.3.3 バックアップ戦略サマリー
7.4 バックアップへの mysqldump の使用
7.4.1 mysqldump による SQL フォーマットでのデータのダンプ
7.4.2 SQL フォーマットバックアップのリロード
7.4.3 mysqldump による区切りテキストフォーマットでのデータのダンプ
7.4.4 区切りテキストフォーマットバックアップのリロード
7.4.5 mysqldump のヒント
7.5 バイナリログを使用したポイントインタイム (増分) リカバリ
7.5.1 イベント時間を使用したポイントインタイムリカバリ
7.5.2 イベントの位置を使用したポイントインタイムリカバリ
7.6 MyISAM テーブルの保守とクラッシュリカバリ
7.6.1 クラッシュリカバリへの myisamchk の使用
7.6.2 MyISAM テーブルのエラーのチェック方法
7.6.3 MyISAM テーブルの修復方法
7.6.4 MyISAM テーブルの最適化
7.6.5 MyISAM テーブル保守スケジュールのセットアップ

システムクラッシュ、ハードウェアの障害、またはユーザーが誤ってデータを削除するなどの問題が発生した場合に、データをリカバリし、再度起動して、実行できるように、データベースをバックアップすることが重要です。バックアップは、MySQL インストールのアップグレード前の保護手段としても不可欠であり、それらを使用して、MySQL インストールを別のシステムに転送したり、レプリケーションスレーブサーバーをセットアップしたりできます。

MySQL では、多様なバックアップ戦略を提供しており、それらからインストールの要件にもっとも適合する方法を選択できます。この章では、熟知しておくべきであるいくつかのバックアップとリカバリのトピックについて説明します。

追加のリソース

バックアップまたはデータの可用性の維持に関連するリソースには次のものが含まれます。

7.1 バックアップとリカバリの種類

このセクションでは、さまざまな種類のバックアップの特性について説明します。

物理 (raw) バックアップと論理バックアップ

物理バックアップは、データベースの内容を格納するディレクトリとファイルの raw コピーから構成されます。この種類のバックアップは、問題の発生時に早急にリカバリさせる必要がある大規模で重要なデータベースに適しています。

論理バックアップは、論理データベース構造として表される情報 (CREATE DATABASECREATE TABLE ステートメント) と内容 (INSERT ステートメントまたは区切りテキストファイル) を保存します。この種類のバックアップは、ユーザーがデータ値やテーブル構造を編集したり、別のマシンアーキテクチャーにデータを再作成したりできる少量のデータに適しています。

物理バックアップ方法にはこれらの特性があります。

  • バックアップはデータベースディレクトリおよびファイルの正確なコピーから構成されます。一般的に、これは MySQL データディレクトリのすべてまたは一部のコピーです。

  • 物理バックアップ方法は、変換しないファイルコピーのみが含まれるため、論理より高速です。

  • 出力は論理バックアップの場合よりコンパクトです。

  • ビジーで、重要なデータベースには、バックアップの速度やコンパクトさが重要であるため、MySQL Enterprise Backup 製品は物理バックアップを実行します。MySQL Enterprise Backup 製品の概要については、セクション25.2「MySQL Enterprise Backup」を参照してください。

  • バックアップとリストアの粒度は、データディレクトリ全体のレベルから個々のファイルのレベルまでの範囲になります。これは、ストレージエンジンに応じて、テーブルレベルの粒度を提供する場合としない場合があります。たとえば、InnoDB テーブルは、それぞれ個別のファイルにしたり、ほかの InnoDB テーブルとファイルストレージを共有したりできます。各 MyISAM テーブルはファイルのセットに一意に対応します。

  • データベースに加えて、バックアップにはログファイルや構成ファイルなどの関連ファイルを含めることができます。

  • MEMORY テーブルの内容はディスクに格納されないため、それらのデータをこの方法でバックアップすることは困難です。(MySQL Enterprise Backup 製品には、バックアップ中に MEMORY テーブルからデータを取得できる機能があります。)

  • バックアップは、同一か類似のハードウェア特性を持つほかのマシンにのみ移植可能です。

  • バックアップは MySQL サーバーが実行していない間に実行できます。サーバーが実行中の場合は、バックアップ中にサーバーがデータベースの内容を変更しないように、適切なロックを実行する必要があります。MySQL Enterprise Backup は、このロックが必要なテーブルに対して、自動的にロックを実行します。

  • 物理バックアップツールには、InnoDB またはその他のテーブル用の MySQL Enterprise Backup の mysqlbackup、ファイルシステムレベルのコマンド (cpscptarrsync など)、または MyISAM テーブル用の mysqlhotcopy が含まれます。

  • リストアの場合:

    • MySQL Enterprise Backup はバックアップした InnoDB およびその他のテーブルをリストアします。

    • ndb_restoreNDB テーブルをリストアします。

    • ファイルシステムレベルまたは mysqlhotcopy によってコピーされたファイルは、ファイルシステムコマンドによってそれらの元の場所にコピーできます。

論理バックアップ方法にはこれらの特性があります。

  • バックアップは、MySQL サーバーをクエリーし、データベース構造と内容情報を取得して実行されます。

  • サーバーがデータベース情報にアクセスし、それを論理フォーマットに変換する必要があるため、バックアップは物理方法より遅くなります。クライアント側で出力が書き込まれた場合、サーバーはそれをバックアッププログラムに送信する必要もあります。

  • 出力は特にテキストフォーマットで保存された場合に物理バックアップより大きくなります。

  • バックアップとリストアの粒度は、サーバーレベル (すべてのデータベース)、データベースレベル (特定のデータベースのすべてのテーブル)、またはテーブルレベルで利用できます。これはストレージエンジンに関係なく当てはまります。

  • バックアップには、ログファイルや構成ファイル、またはデータベースの一部ではないその他のデータベース関連ファイルは含まれません。

  • 論理フォーマットで格納されているバックアップはマシンに依存せず、高度に移植可能です。

  • 論理バックアップは MySQL サーバーの実行中に実行されます。サーバーはオフラインにされません。

  • 論理バックアップツールには、mysqldump プログラムと SELECT ... INTO OUTFILE ステートメントが含まれます。これらは MEMORY でも、すべてのストレージエンジンで機能します。

  • 論理バックアップをリストアするには、mysql クライアントを使用して、SQL フォーマットダンプファイルを処理できます。区切りテキストファイルをロードするには、LOAD DATA INFILE ステートメントまたは mysqlimport クライアントを使用します。

オンラインバックアップとオフラインバックアップ

オンラインバックアップは、データベース情報をサーバーから取得できるように、MySQL サーバーが実行中に行われます。オフラインバックアップは、サーバーが停止中に行われます。この区別は、ホットバックアップとコールドバックアップとして表すこともできます。ウォームバックアップは、サーバーが実行したままですが、外部からデータベースファイルにアクセスしている間のデータの変更に対してロックされます。

オンラインバックアップ方法にはこれらの特性があります。

  • このバックアップはほかのクライアントの邪魔になりにくく、クライアントはバックアップ中に MySQL サーバーに接続でき、実行する必要がある操作に応じて、データにアクセスできます。

  • バックアップの完全性を損なう可能性のあるデータの変更が行われないように、適切なロックを適用する場合は、注意を払う必要があります。MySQL Enterprise Backup 製品はそのようなロックを自動的に実行します。

オフラインバックアップ方法にはこれらの特性があります。

  • バックアップ中にサーバーを使用できないため、クライアントは影響を受ける可能性があります。そのため、そのようなバックアップは、多くの場合、可用性を損なうことなくオフラインにできるレプリケーションスレーブサーバーから行われます。

  • バックアップ手順は、クライアントのアクティビティーからの干渉の可能性がないため単純になります。

オンラインとオフラインの同様の違いは、リカバリ操作にも当てはまり、同様の特性が当てはまります。ただし、リカバリにはより強力なロックが必要であるため、オンラインリカバリではオンラインバックアップより、クライアントが影響を受ける可能性が高くなります。バックアップ時、クライアントはバックアップ中にデータを読み取ることができます。リカバリはデータを変更し、読み取るだけでないため、データのリストア中は、クライアントのデータへのアクセスを妨げる必要があります。

ローカルバックアップとリモートバックアップ

ローカルバックアップは MySQL サーバーが実行している同じホストで実行され、リモートバックアップは別のホストから実行されます。特定の種類のバックアップでは、出力がサーバーホストにローカルで書きこまれる場合でも、バックアップをリモートホストから開始できます。

  • mysqldump はローカルまたはリモートサーバーに接続できます。SQL 出力 (CREATE および INSERT ステートメント) の場合、ローカルまたはリモートダンプを実行でき、クライアント上に出力が生成されます。区切りテキスト出力 (--tab オプションを使用して) の場合、サーバーホスト上にデータファイルが作成されます。

  • mysqlhotcopy はローカルバックアップのみを実行します。それはサーバーに接続し、データ変更に対してそれをロックしてから、ローカルテーブルファイルをコピーします。

  • SELECT ... INTO OUTFILE はローカルまたはリモートクライアントホストから起動できますが、出力ファイルはサーバーホスト上に作成されます。

  • 物理バックアップ方法は一般に、サーバーをオフラインにできるように、MySQL サーバーホスト上でローカルに開始されますが、コピーされるファイルの宛先はリモートにすることができます。

スナップショットバックアップ

一部のファイルシステム実装では、スナップショットを取得できます。これらは、ファイルシステム全体の物理コピーを必要とせずに、特定の時点のファイルシステムの論理コピーを提供します。(たとえば、実装では、スナップショット取得時間後に変更されたファイルシステムの部分のみがコピーされるように、コピーオンライト (copy-on-write) 技法を使用することがあります。)MySQL 自体はファイルシステムスナップショットを取得するための機能を提供していません。これは Veritas、LVM、または ZFS などのサードパーティーソリューションから使用できます。

完全バックアップと増分バックアップ

完全バックアップには、特定の時点の MySQL サーバーによって管理されるすべてのデータが含まれます。増分バックアップは、特定の期間 (ある時点から別の時点まで) 中にデータに行われた変更から構成されます。MySQL では、このセクションで先述したものなど、完全バックアップを実行するためのさまざまな方法があります。増分バックアップは、サーバーのバイナリログを有効にすることによって可能になります。サーバーはそれをデータの変更を記録するために使用します。

完全リカバリとポイントインタイム (増分) リカバリ

完全リカバリでは、完全バックアップからすべてのデータをリストアします。これは、サーバーインスタンスをバックアップが行われたときのその状態にリストアします。その状態が十分に最新でない場合、完全リカバリのあとに、完全バックアップ以降に行われた増分バックアップのリカバリを行なって、サーバーをより新しい状態にすることができます。

増分リカバリは、特定の期間中に行われた変更のリカバリです。これは、サーバーの状態を特定の時点の最新にするため、ポイントインタイムリカバリとも呼ばれます。ポイントインタイムリカバリは、バイナリログに基づき、一般にバックアップが行われたときの状態にサーバーをリストアするバックアップファイルからの完全リカバリのあとに行われます。バイナリログファイルに書き込まれたデータの変更が増分リカバリとして適用され、データの変更が元に戻され、サーバーが目的の時点の状態になります。

テーブルの保守

テーブルが破損した場合、データの完全性が損なわれる可能性があります。InnoDB テーブルの場合、これはよくある問題ではありません。MyISAM テーブルをチェックし、問題が見つかった場合にそれらを修復するプログラムについては、セクション7.6「MyISAM テーブルの保守とクラッシュリカバリ」を参照してください。

バックアップのスケジューリング、圧縮、および暗号化

バックアップスケジューリングはバックアップ手順の自動化に役立ちます。バックアップ出力の圧縮によって、領域要件が縮小し、出力の暗号化により、バックアップされたデータの権限のないアクセスに対するセキュリティーが強化されます。MySQL 自体はこれらの機能を提供していません。MySQL Enterprise Backup 製品によって InnoDB バックアップを圧縮し、バックアップ出力の圧縮や暗号化は、ファイルシステムユーティリティーを使用して実現できます。その他のサードパーティーソリューションも利用できます。

7.2 データベースバックアップ方法

このセクションでは、バックアップを作成する場合の一般的な方法をまとめています。

MySQL Enterprise Backup によるホットバックアップの作成

MySQL Enterprise Edition のお客様は MySQL Enterprise Backup 製品を使用して、インスタンス全体または選択されたデータベース、テーブル、またはその両方の物理バックアップを実行できます。この製品には、増分および圧縮バックアップの機能が含まれます。物理データベースファイルのバックアップは、リストアが mysqldump コマンドなどの論理技法よりはるかに高速になります。InnoDB テーブルはホットバックアップメカニズムを使用してコピーされます。(理想的には InnoDB テーブルでデータの大部分を表しているべきです。)ほかのストレージエンジンのテーブルは、ウォームバックアップメカニズムを使用してコピーされます。MySQL Enterprise Backup 製品の概要については、セクション25.2「MySQL Enterprise Backup」を参照してください。

mysqldump または mysqlhotcopy によるバックアップの作成

mysqldump プログラムと mysqlhotcopy スクリプトでバックアップを作成できます。mysqldump はあらゆる種類のテーブルをバックアップできるため、より汎用的です。mysqlhotcopy は一部のストレージエンジンでのみ機能します。(セクション7.4「バックアップへの mysqldump の使用」およびセクション4.6.10「mysqlhotcopy — データベースバックアッププログラム」を参照してください。)

InnoDB テーブルの場合、mysqldump--single-transaction オプションを使用して、テーブルをロックしないオンラインバックアップを実行できます。セクション7.3.1「バックアップポリシーの確立」を参照してください。

テーブルファイルのコピーによるバックアップの作成

独自のファイルを使用して、各テーブルを表すストレージエンジンの場合、テーブルはそれらのファイルをコピーしてバックアップできます。たとえば、MyISAM テーブルはファイルとして格納されるため、ファイル (*.frm*.MYD、および *.MYI ファイル) をコピーして、簡単にバックアップを行うことができます。一貫したバックアップを取得するには、サーバーを停止するか、関連するテーブルをロックしてフラッシュします。

FLUSH TABLES tbl_list WITH READ LOCK;

読み取りロックのみが必要です。これにより、データベースディレクトリ内のファイルのコピー中に、ほかのクライアントが引き続きテーブルをクエリーすることができます。バックアップを開始する前に、すべてのアクティブインデックスページがディスクに書き込まれるようにするため、フラッシュが必要です。セクション13.3.5「LOCK TABLES および UNLOCK TABLES 構文」およびセクション13.7.6.3「FLUSH 構文」を参照してください。

サーバーが何も更新していないかぎり、すべてのテーブルファイルをコピーすることによって、バイナリバックアップを簡単に作成することもできます。mysqlhotcopy スクリプトはこの方法を使用します。(ただし、テーブルファイルコピー方法は、データベースに InnoDB テーブルが含まれている場合、機能しません。InnoDB では必ずしもデータベースディレクトリにテーブルの内容を格納しないため、mysqlhotcopyInnoDB テーブルで機能しません。さらに、サーバーがアクティブにデータを更新していない場合、InnoDB は変更されたデータをまだメモリー内にキャッシュしており、ディスクにフラッシュしていないことがあります。)

区切りテキストファイルバックアップの作成

テーブルのデータを含むテキストファイルを作成するには、SELECT * INTO OUTFILE 'file_name' FROM tbl_name を使用できます。このファイルはクライアントホストではなく、MySQL サーバーホスト上に作成されます。このステートメントの場合、ファイルの上書きを許可すると、セキュリティーリスクになるため、出力ファイルがすでに存在していてはなりません。セクション13.2.9「SELECT 構文」を参照してください。この方法はあらゆる種類のデータファイルに機能しますが、テーブルデータのみ保存し、テーブル構造は保存しません。

テキストデータファイル (バックアップされたテーブルの CREATE TABLE ステートメントを含むファイルに加えて) を作成する別の方法は、mysqldump--tab オプションを使用することです。セクション7.4.3「mysqldump による区切りテキストフォーマットでのデータのダンプ」を参照してください。

区切りテキストデータファイルをリロードするには、LOAD DATA INFILE または mysqlimport を使用します。

バイナリログを有効にすることによる増分バックアップの作成

MySQL は増分バックアップをサポートします。--log-bin オプションでサーバーを起動し、バイナリロギングを有効にする必要があります。セクション5.2.4「バイナリログ」を参照してください。バイナリログファイルは、バックアップを実行した時点のあとに行われたデータベースへの変更のレプリケートする必要がある情報を提供します。増分バックアップ (最後の完全バックアップまたは増分バックアップ以降に発生したすべての変更を含む) を作成しようとするときは、FLUSH LOGS を使用して、バイナリログをローテーションしてください。これが完了したら、最後の完全または増分バックアップの瞬間から最後の 1 つ前の範囲のすべてのバイナリログをバックアップの場所にコピーする必要があります。これらのバイナリログは増分バックアップで、リストア時に、セクション7.5「バイナリログを使用したポイントインタイム (増分) リカバリ」に説明するように、それらを適用します。次回に完全バックアップを行うときも、FLUSH LOGSmysqldump --flush-logs、または mysqlhotcopy --flushlog を使用してバイナリログをローテーションしてください。セクション4.5.4「mysqldump — データベースバックアッププログラム」およびセクション4.6.10「mysqlhotcopy — データベースバックアッププログラム」を参照してください。

レプリケーションスレーブを使用したバックアップの作成

バックアップの作成中に、マスターサーバーにパフォーマンスの問題がある場合、役に立つ可能性のある 1 つの戦略は、マスターではなくスレーブでレプリケーションをセットアップし、バックアップを実行することです。セクション17.3.1「バックアップ用にレプリケーションを使用する」を参照してください。

スレーブレプリケーションサーバーをバックアップする場合、選択したバックアップ方法に関係なく、スレーブのデータベースをバックアップする際に、そのマスター情報とリレーログ情報のリポジトリをバックアップしてください (セクション17.2.2「レプリケーションリレーおよびステータスログ」を参照してください)。これらの情報ファイルは、スレーブのデータをリストアしたあとに、レプリケーションを再開するために常に必要です。スレーブが LOAD DATA INFILE ステートメントをレプリケートする場合、スレーブがこのために使用するディレクトリ内に存在する SQL_LOAD-* ファイルもバックアップしてください。スレーブは、中断した LOAD DATA INFILE 操作のレプリケーションを再開するためにこれらのファイルを必要とします。このディレクトリの場所は --slave-load-tmpdir オプションの値です。そのオプションでサーバーを起動しなかった場合、ディレクトリの場所は tmpdir システム変数の値になります。

破損したテーブルのリカバリ

破損した MyISAM テーブルをリストアする必要がある場合、まず REPAIR TABLE または myisamchk -r を使用して、それらのリカバリを試みます。それは、すべてのケースの 99.9% で機能するはずです。myisamchk が失敗した場合は、セクション7.6「MyISAM テーブルの保守とクラッシュリカバリ」を参照してください。

ファイルシステムスナップショットを使用したバックアップの作成

Veritas ファイルシステムを使用している場合、次のようにバックアップを作成できます。

  1. クライアントプログラムから、FLUSH TABLES WITH READ LOCK を実行します。

  2. 別のシェルから、mount vxfs snapshot を実行します。

  3. 最初のクライアントから、UNLOCK TABLES を実行します。

  4. スナップショットからファイルをコピーします。

  5. スナップショットをアンマウントします。

同様のスナップショット機能は、LVM や ZFS などのほかのファイルシステムでも利用できます。

7.3 バックアップおよびリカバリ戦略の例

このセクションでは、いくつかの種類のクラッシュ後にデータをリカバリできるようにするバックアップを実行するための手順について説明します。

  • オペレーティングシステムのクラッシュ

  • 停電

  • ファイルシステムのクラッシュ

  • ハードウェアの問題 (ハードドライブ、マザーボードなど)

コマンド例には、mysqldump および mysql クライアントプログラム用の --user--password などのオプションは含まれていません。クライアントプログラムが MySQL サーバーに接続できるようにする必要に応じて、それらのオプションを含めてください。

データは、トランザクションと自動クラッシュリカバリをサポートする InnoDB ストレージエンジンに格納されているとします。さらに、MySQL サーバーはクラッシュ時に負荷がかかっているとします。そうでなければ、リカバリは必要ないことがあります。

オペレーティングシステムのクラッシュや停電の場合、再起動後、MySQL のディスクデータを使用できるものと考えることができます。InnoDB データファイルにはクラッシュのために一貫したデータが格納されていない可能性がありますが、InnoDB はそのログを読み取り、データファイルにフラッシュされていないコミット保留中のトランザクションやコミットされていないトランザクションのリストを見つけます。InnoDB はまだコミットされていないトランザクションを自動的にロールバックし、コミットされたものはデータファイルにフラッシュします。このリカバリプロセスに関する情報は、MySQL エラーログによってユーザーに伝えられます。次はログの抜粋の例です。

InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

ファイルシステムのクラッシュやハードウェアの問題の場合、再起動後、MySQL ディスクデータを使用できないものと考えることができます。これは、ディスクデータの一部のブロックが読み取り不可能になったため、MySQL が正常な起動に失敗することを意味します。この場合、ディスクを再フォーマットするか、新しいディスクをインストールするか、または根本的な問題を修正する必要があります。さらに、バックアップから MySQL データをリカバリする必要があります。これはバックアップがすでに行われていることを意味します。それが確実に当てはまるようにするには、バックアップポリシーを設計し、実装します。

7.3.1 バックアップポリシーの確立

役に立つように、バックアップは定期的にスケジュールする必要があります。完全バックアップ (特定の時点でのデータのスナップショット) は、MySQL でいくつかのツールを使用して実行できます。たとえば、MySQL Enterprise Backup は、InnoDB データファイルのバックアップ時にオーバーヘッドを最小にし、中断を防ぐ最適化を伴うインスタンス全体の物理バックアップを実行できます。mysqldump はオンライン論理バックアップを提供します。この説明では mysqldump を使用します。

負荷が少ない日曜日の午後 1 時に、次のコマンドを使用して、すべてのデータベースのすべての InnoDB テーブルの完全バックアップを作成するとします。

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

mysqldump によって生成される結果の .sql ファイルには、あとでダンプしたテーブルのリロードに使用できる SQL INSERT ステートメントのセットが含まれます。

このバックアップ操作では、ダンプの最初ですべてのテーブルに対するグローバル読み取りロックを取得します (FLUSH TABLES WITH READ LOCK を使用して)。このロックが取得されるとすぐに、バイナリログの座標が読み取られ、ロックが解除されます。FLUSH ステートメントが発行されたときに長い更新ステートメントが実行中の場合、バックアップ操作はそれらのステートメントが終了するまで停止する可能性があります。その後、ダンプはロックフリーとなり、テーブルの読み取りと書き込みを妨げません。

先に、バックアップするテーブルは InnoDB テーブルであるとしたため、--single-transaction は、一貫性読み取りを使用し、mysqldump によって表示されたデータが変更されないことを保証します。(ほかのクライアントによる InnoDB テーブルへの変更は、mysqldump プロセスによって表示されません)。バックアップ操作に非トランザクションテーブルが含まれる場合、一貫性には、バックアップ中にそれらが変更されない必要があります。たとえば、mysql データベース内の MyISAM テーブルの場合、バックアップ中に、MySQL アカウントへの管理上の変更があってはなりません。

完全バックアップが必要ですが、それらを作成するために常に都合がよいとは限りません。それらは大きなバックアップファイルを生成し、生成に時間がかかります。それらは、連続した各完全バックアップに、前回の完全バックアップから変更されていない部分でもすべてのデータが含まれるという点で、最適ではありません。初期完全バックアップを作成し、次に増分バックアップを作成するほうが効率的です。増分バックアップは小さく、生成にかかる時間が少なくなります。このトレードオフは、リカバリ時に、完全バックアップをリロードするだけではデータをリストアできないことです。増分バックアップを処理して、増分の変更もリカバリする必要があります。

増分バックアップを作成するには、増分の変更を保存する必要があります。MySQL では、これらの変更はバイナリログで表されるため、MySQL サーバーを常に --log-bin オプションで起動して、そのログを有効にしてください。バイナリロギングが有効にされていると、サーバーはデータの更新中に、各データの変更をファイルに書き込みます。--log-bin オプションで起動し、数日間実行していた MySQL サーバーのデータディレクトリを調べると、これらの MySQL バイナリログファイルが見つかります。

-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index

MySQL サーバーは再起動するたびに、シーケンスの次の番号を使用して、新しいバイナリログファイルを作成します。サーバーが実行している間、FLUSH LOGS SQL ステートメントを発行するか、mysqladmin flush-logs コマンドによって、手動で、それに現在のバイナリログファイルをクローズし、新しいファイルを開始するように伝えることもできます。mysqldump にはログをフラッシュするオプションもあります。データディレクトリ内の .index ファイルには、ディレクトリ内のすべての MySQL バイナリログのリストが含まれます。

MySQL バイナリログは、増分バックアップのセットを形成するため、リカバリに重要です。完全バックアップの作成時にログをフラッシュさせる場合、その後作成されるバイナリログファイルには、バックアップ以降に行われたすべてのデータの変更が含まれます。ここで、前述の mysqldump コマンドを少し修正して、完全バックアップの時点で MySQL バイナリログをフラッシュするようにし、ダンプファイルに新しい現在のバイナリログの名前が含まれるようにします。

shell> mysqldump --single-transaction --flush-logs --master-data=2 \--all-databases > backup_sunday_1_PM.sql

このコマンドの実行後、--flush-logs オプションによって、サーバーにそのログをフラッシュさせるため、データディレクトリには新しいバイナリログファイル gbichot2-bin.000007 が格納されます。--master-data オプションは mysqldump でその出力にバイナリログ情報を書き込ませるため、結果の .sql ダンプファイルにはこれらの行が含まれます。

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

mysqldump コマンドで完全バックアップを作成しているため、これらの行は 2 つのことを意味します。

  • ダンプファイルには、gbichot2-bin.000007 以降のバイナリログファイルに書き込まれた変更の前に行われたすべての変更が含まれます。

  • バックアップ後にログに記録されたすべてのデータの変更は、ダンプファイルに存在しませんが、gbichot2-bin.000007 以降のバイナリログファイルに存在します。

月曜日の午後 1 時に、ログをフラッシュし、新しいバイナリログファイルを開始することによって、増分バックアップを作成できます。たとえば、mysqladmin flush-logs コマンドを実行すると、gbichot2-bin.000008 が作成されます。日曜日の午後 1 時の完全バックアップから月曜日の午後 1 時までのすべての変更は、gbichot2-bin.000007 ファイル内にあります。この増分バックアップは重要であるため、それを安全な場所にコピーすることをお勧めします。(たとえば、それをテープや DVD にバックアップするか、別のマシンにコピーします。)火曜日の午後 1 時に、さらに mysqladmin flush-logs コマンドを実行します。月曜日の午後 1 時から火曜日の午後 1 時までのすべての変更が、gbichot2-bin.000008 ファイル内にあります (これもどこか安全な場所にコピーするべきです)。

MySQL バイナリログはディスク領域を占有します。領域を解放するため、ときどきそれらをパージします。これを実行する 1 つの方法は、完全バックアップを作成したときなど、必要なくなったバイナリログを削除することです。

shell> mysqldump --single-transaction --flush-logs --master-data=2 \--all-databases --delete-master-logs > backup_sunday_1_PM.sql
注記

サーバーがレプリケーションマスターサーバーである場合、スレーブサーバーでまだバイナリログの内容を完全に処理していない可能性があるため、mysqldump --delete-master-logs で MySQL バイナリログを削除することは危険な場合があります。PURGE BINARY LOGS ステートメントの説明では、MySQL バイナリログを削除する前に確認すべきことを説明しています。セクション13.4.1.1「PURGE BINARY LOGS 構文」を参照してください。

7.3.2 リカバリへのバックアップの使用

ここで、水曜日の午前 8 時に、バックアップからのリカバリを必要とする致命的なクラッシュがあったとします。リカバリするには、まず存在する最後の完全バックアップ (日曜日の午後 1 時のもの) をリストアします。完全バックアップファイルは一連の SQL ステートメントにすぎないため、そのリストアはきわめて簡単です。

shell> mysql < backup_sunday_1_PM.sql

この時点で、データは日曜日の午後 1 時現在の状態にリストアされます。それ以降に行われた変更をリストアするには、増分バックアップを使用する必要があります。つまり、gbichot2-bin.000007gbichot2-bin.000008 バイナリログファイルです。必要に応じて、バックアップされた場所からファイルをフェッチして、次のようにそれらの内容を処理します。

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

これで、データを火曜日の午後 1 時現在の状態にリカバリしましたが、まだその日からクラッシュの日までの変更が不足しています。それらを失わないために、MySQL サーバーにその MySQL バイナリログを、そのデータファイルを格納している場所と異なる安全な場所 (RAID ディスク、SAN など) に保存させ、これらのログが破損したディスク上にないようにする必要がありました。(つまり、データディレクトリが存在する場所と別の物理デバイス上の場所を指定する --log-bin オプションでサーバーを起動できます。このようにすると、ディレクトリを格納するデバイスが失われてもログは安全です。)これを実行していた場合、gbichot2-bin.000009 ファイル (および任意の後続のファイル) が手元にあるため、mysqlbinlogmysql を使用して、それらを適用し、クラッシュの瞬間まで損失なく、最新のデータの変更をリストアできます。

shell> mysqlbinlog gbichot2-bin.000009 ... | mysql

mysqlbinlog を使用して、バイナリログファイルを処理する詳細については、セクション7.5「バイナリログを使用したポイントインタイム (増分) リカバリ」を参照してください。

7.3.3 バックアップ戦略サマリー

オペレーティングシステムのクラッシュまたは停電の場合、InnoDB 自体がデータのリカバリのすべてのジョブを実行します。ただし、安心のため、次のガイドラインを参照してください。

  • 常に MySQL サーバーを --log-bin オプションまたは --log-bin=log_name で実行します。ここでログファイル名は、データディレクトリが存在するドライブと異なる安全なメディア上に配置します。そのような安全なメディアがある場合、この技法は、ディスクの負荷分散にも役立ちます (その結果パフォーマンスも向上します)。

  • セクション7.3.1「バックアップポリシーの確立」で先に示した、オンラインのブロックしないバックアップを作成する mysqldump コマンドを使用して、定期的な完全バックアップを作成します。

  • FLUSH LOGS または mysqladmin flush-logs を使用して、ログをフラッシュして、定期的な増分バックアップを作成します。

7.4 バックアップへの mysqldump の使用

このセクションでは、mysqldump を使用して、ダンプファイルを生成する方法およびダンプファイルをリロードする方法について説明します。ダンプファイルはいくつかの方法で使用できます。

  • データ損失の場合にデータリカバリを可能にするためのバックアップとして。

  • レプリケーションスレーブをセットアップするためのデータのソースとして。

  • 実験用のデータのソースとして。

    • 元のデータを変更せずに使用できるデータベースのコピーを作成する場合。

    • アップグレードの非互換性の可能性をテストする場合。

mysqldump--tab オプションを指定するかどうかに応じて、2 種類の出力を生成します。

  • --tab がないと、mysqldump は SQL ステートメントを標準出力に書き込みます。この出力は、ダンプされるオブジェクト (データベース、テーブル、ストアドルーチンなど) を作成する CREATE ステートメントとデータをテーブルにロードする INSERT ステートメントから構成されます。出力はファイルに保存して、あとで mysql を使用してリロードし、ダンプされたオブジェクトを再作成できます。SQL ステートメントのフォーマットを変更し、ダンプされるオブジェクトを制御するためにオプションを使用できます。

  • --tab を付けると、mysqldump はダンプされるテーブルごとに 2 つの出力ファイルを生成します。サーバーは、テーブル行ごとに 1 行ずつ、タブ区切りテキストとして 1 つのファイルを書き込みます。このファイルは出力ディレクトリ内で tbl_name.txt という名前が付けられます。サーバーはテーブルの CREATE TABLE ステートメントも mysqldump に送信し、それは tbl_name.sql という名前のファイルとしてそれを出力ディレクトリに書き込みます。

7.4.1 mysqldump による SQL フォーマットでのデータのダンプ

このセクションでは、mysqldump を使用して、SQL フォーマットのダンプファイルを作成する方法について説明します。そのようなダンプファイルのリロードについては、セクション7.4.2「SQL フォーマットバックアップのリロード」を参照してください。

デフォルトで、mysqldump は情報を SQL ステートメントとして標準出力に書き込みます。出力をファイルに保存できます。

shell> mysqldump [arguments] > file_name

すべてのデータベースをダンプするには、--all-databases オプションを付けて mysqldump を呼び出します。

shell> mysqldump --all-databases > dump.sql

特定のデータベースのみをダンプするには、コマンド行でそれらを指定し、--databases オプションを使用します。

shell> mysqldump --databases db1 db2 db3 > dump.sql

--databases オプションによって、コマンド行上のすべての名前がデータベース名として扱われます。このオプションを使用しないと、mysqldump は最初の名前をデータベース名として、そのあとに続く名前をテーブル名として扱います。

--all-databases または --databases を使用すると、mysqldump は、各データベースのダンプ出力の前に、CREATE DATABASE および USE ステートメントを書き込みます。これにより、ダンプファイルがリロードされると、それが各データベースが存在しなければ作成して、デフォルトのデータベースにするため、データベースの内容がそれらの作成元の同じデータベースにロードされます。ダンプファイルに、各データベースを再作成する前にその削除を強制する場合、--add-drop-database オプションも使用します。この場合、mysqldump は各 CREATE DATABASE ステートメントの前に、DROP DATABASE ステートメントを書き込みます。

単一のデータベースをダンプするには、コマンド行でそれを指定します。

shell> mysqldump --databases test > dump.sql

単一のデータベースの場合、--databases オプションを省略できます。

shell> mysqldump test > dump.sql

2 つの先述のコマンドの違いは、--databases を付けないと、ダンプの出力に CREATE DATABASE または USE ステートメントが含まれません。これにはいくつかの問題があります。

  • ダンプファイルをリロードする場合、サーバーがリロードするデータベースを認識するように、デフォルトのデータベース名を指定する必要があります。

  • リロードする場合、元の名前と異なるデータベース名を指定でき、これにより、データを別のデータベースにリロードできます。

  • リロードするデータベースが存在しない場合、まずそれを作成する必要があります。

  • 出力には CREATE DATABASE ステートメントが含まれないため、--add-drop-database オプションは無効です。それを使用しても DROP DATABASE ステートメントは生成されません。

データベースから特定のテーブルのみをダンプするには、コマンド行でデータベース名に続いてそれらを指定します。

shell> mysqldump test t1 t3 t7 > dump.sql

7.4.2 SQL フォーマットバックアップのリロード

SQL ステートメントから構成される mysqldump によって書き込まれたダンプファイルをリロードするには、それを mysql クライアントへの入力として使用します。--all-databases または --databases オプションを使用して、mysqldump によってダンプファイルが作成された場合、それには CREATE DATABASE および USE ステートメントが含まれ、データをロードするデフォルトのデータベースを指定する必要がありません。

shell> mysql < dump.sql

または、mysql 内から、source コマンドを使用します。

mysql> source dump.sql

ファイルが CREATE DATABASE および USE ステートメントを含まない単一データベースダンプである場合、まずデータベースを作成します (必要に応じて)。

shell> mysqladmin create db1

次に、ダンプファイルをロードする場合、データベース名を指定します。

shell> mysql db1 < dump.sql

または mysql 内から、データベースを作成し、それをデフォルトのデータベースとして選択し、ダンプファイルをロードします。

mysql> CREATE DATABASE IF NOT EXISTS db1;mysql> USE db1;mysql> source dump.sql

7.4.3 mysqldump による区切りテキストフォーマットでのデータのダンプ

このセクションでは、mysqldump を使用して、区切りテキストのダンプファイルを作成する方法について説明します。そのようなダンプファイルのリロードについては、セクション7.4.4「区切りテキストフォーマットバックアップのリロード」を参照してください。

--tab=dir_name オプションを付けて、mysqldump を呼び出した場合、それは dir_name を出力ディレクトリとして使用し、テーブルごとに 2 つのファイルを使用して、そのディレクトリに個別にテーブルをダンプします。テーブル名はこれらのファイルのベース名です。t1 という名前のテーブルの場合、ファイルには t1.sql および t1.txt という名前が付けられます。.sql ファイルにはテーブルの CREATE TABLE ステートメントが含まれます。.txt ファイルにはテーブル行ごとに 1 行のテーブルデータが含まれます。

次のコマンドは db1 データベースの内容を /tmp データベース内のファイルにダンプします。

shell> mysqldump --tab=/tmp db1

テーブルデータを格納する .txt ファイルはサーバーによって書き込まれるため、それらはサーバーの実行に使用されるシステムアカウントによって所有されます。サーバーは SELECT ... INTO OUTFILE を使用して、ファイルを書き込むため、この操作を実行するために FILE 権限が必要であり、指定した .txt ファイルがすでに存在する場合はエラーが発生します。

サーバーはダンプされるテーブルの CREATE 定義を mysqldump に送信し、それはそれらを .sql ファイルに書き込みます。そのためこれらのファイルは、mysqldump を実行するユーザーによって所有されます。

--tab はローカルサーバーのダンプにのみ使用することをお勧めします。それをリモートサーバーに使用する場合、--tab ディレクトリがローカルホストとリモートホストの両方に存在する必要があり、.txt ファイルはサーバーによってリモートディレクトリ (サーバーホスト上) に書き込まれ、.sql ファイルは mysqldump によってローカルディレクトリ (クライアントホスト上) に書き込まれます。

mysqldump --tab の場合、サーバーはデフォルトでテーブルデータを、カラム値間にタブを、カラム値を引用符で囲まず、行ターミネータとして改行を使用して、1 行あたり 1 行で .txt ファイルに書き込みます。(これらは、SELECT ... INTO OUTFILE の場合と同じデフォルトです。)

別のフォーマットを使用して、データファイルを書き込めるようにするため、mysqldump はこれらのオプションをサポートしています。

  • --fields-terminated-by=str

    カラム値を区切るための文字列 (デフォルト: タブ)。

  • --fields-enclosed-by=char

    カラム値を囲む文字 (デフォルト: 文字なし)。

  • --fields-optionally-enclosed-by=char

    数値以外のカラム値を囲む文字 (デフォルト: 文字なし)。

  • --fields-escaped-by=char

    特殊文字をエスケープするための文字 (デフォルト: エスケープなし)。

  • --lines-terminated-by=str

    行終了文字列 (デフォルト: 改行)。

これらのオプションに指定する値に応じて、コマンド行で、コマンドインタプリタに合わせて値を引用符で囲むかエスケープする必要がある場合があります。または、16 進表記を使用して、値を指定します。mysqldump にカラム値を二重引用符で囲ませたいとします。そうするには、--fields-enclosed-by オプションの値として、二重引用符を指定します。ただし、この文字は多くの場合コマンドインタプリタに特有であるため、特別に扱う必要があります。たとえば、Unix ではこのように二重引用符を表すことができます。

--fields-enclosed-by='"'

どのプラットフォームでも 16 進で値を指定できます。

--fields-enclosed-by=0x22

複数のデータフォーマットオプションを一緒に使用することもよくあります。たとえば、行を改行文字/復帰改行ペア (\r\n) で終了させたカンマ区切り値フォーマットでテーブルをダンプするには、このコマンドを使用します (1 行で入力します)。

shell> mysqldump --tab=/tmp --fields-terminated-by=,--fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

テーブルデータをダンプするために、いずれかのデータフォーマットオプションを使用する場合、あとでデータファイルをリロードするときに、ファイルの内容が正しく解釈されるように、同じフォーマットを指定する必要があります。

7.4.4 区切りテキストフォーマットバックアップのリロード

mysqldump --tab によって生成されるバックアップの場合、各テーブルは出力ディレクトリに、テーブルの CREATE TABLE ステートメントを含む .sql ファイルと、テーブルデータを含む .txt ファイルで表されます。テーブルをリロードするには、まず場所を出力ディレクトリに変更します。次に、mysql.sql ファイルを処理し、空のテーブルを作成し、.txt ファイルを処理して、データをテーブルにロードします。

shell> mysql db1 < t1.sqlshell> mysqlimport db1 t1.txt

mysqlimport を使用してデータファイルをロードする代替の方法は、mysql クライアント内から LOAD DATA INFILE ステートメントを使用することです。

mysql> USE db1;mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

テーブルを最初にダンプしたときに、mysqldump で何らかのデータフォーマットオプションを使用した場合、mysqlimport または LOAD DATA INFILE で同じオプションを使用して、データファイルの内容が正しく解釈されるようにする必要があります。

shell> mysqlimport --fields-terminated-by=,--fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt

または:

mysql> USE db1;mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1 -> FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"' -> LINES TERMINATED BY '\r\n';

7.4.5 mysqldump のヒント

このセクションでは、mysqldump を使用して特定の問題を解決できる技法を調査します。

  • データベースのコピーの作成方法

  • サーバー間でデータベースをコピーする方法

  • ストアドプログラム (ストアドプロシージャーおよび関数、トリガー、およびイベント) をダンプする方法

  • 定義とデータを個別にダンプする方法

7.4.5.1 データベースのコピーの作成

shell> mysqldump db1 > dump.sqlshell> mysqladmin create db2shell> mysql db2 < dump.sql

mysqldump コマンド行に --databases を使用すると、ダンプファイルに USE db1 が含まれ、それによって mysql コマンド行の db2 の指定の効果がオーバーライドされるため、使用しないでください。

7.4.5.2 サーバー間でのデータベースのコピー

サーバー 1 で:

shell> mysqldump --databases db1 > dump.sql

サーバー 1 からサーバー 2 にダンプファイルをコピーします。

サーバー 2 で:

shell> mysql < dump.sql

mysqldump コマンド行で --databases を使用すると、それが存在する場合にデータベースを作成し、それをリロードされるデータのデフォルトのデータベースにする CREATE DATABASE および USE ステートメントがダンプファイルに含まれます。

または、mysqldump コマンドから --databases を省略できます。次に、必要に応じて、サーバー 2 にデータベースを作成し、それをダンプファイルのリロード時のデフォルトのデータベースとして指定する必要があります。

サーバー 1 で:

shell> mysqldump db1 > dump.sql

サーバー 2 で:

shell> mysqladmin create db1shell> mysql db1 < dump.sql

この場合、別のデータベース名を指定できるため、mysqldump コマンドから --databases を省略すると、あるデータベースからデータをダンプし、別のデータベースにそれをロードすることができます。

7.4.5.3 ストアドプログラムのダンプ

いくつかのオプションは、mysqldump がストアドプログラム (ストアドプロシージャーおよび関数、トリガー、およびイベント) を処理する方法を制御します。

  • --events: イベントスケジューラのイベントのダンプ

  • --routines: ストアドプロシージャーおよびストアドファンクションのダンプ

  • --triggers: テーブルのトリガーのダンプ

テーブルがダンプされるときに、それらにそれらが持ついずれかのトリガーが伴うように、--triggers オプションはデフォルトで有効にされています。ほかのオプションはデフォルトで無効にされ、対応するオブジェクトをダンプするために明示的に指定する必要があります。これらのオプションのいずれかを明示的に無効にするには、そのスキップフォーム --skip-events--skip-routines、または --skip-triggers を使用します。

7.4.5.4 テーブル定義と内容の個別のダンプ

--no-data オプションは mysqldump にテーブルデータをダンプしないように伝えるため、ダンプファイルにはテーブルを作成するステートメントのみが含まれます。逆に、--no-create-info オプションは、ダンプファイルにテーブルデータのみが含まれるように、mysqldump に出力から CREATE ステートメントを抑制するように伝えます。

たとえば、test データベースのテーブル定義とデータを別々にダンプするには、これらのコマンドを使用します。

shell> mysqldump --no-data test > dump-defs.sqlshell> mysqldump --no-create-info test > dump-data.sql

定義のみのダンプの場合、ストアドルーチンとイベントの定義も含めるには、--routines および --events オプションを追加します。

shell> mysqldump --no-data --routines --events test > dump-defs.sql

7.4.5.5 mysqldump を使用したアップグレードの非互換性のテスト

MySQL のアップグレードを検討する場合、新しいバージョンを現在の本番バージョンとべつにインストールすることが賢明です。これによって、本番サーバーからデータベースとデータベースオブジェクト定義をダンプし、新しいサーバーにロードして、それらが正しく処理されることを確認できます。(これはダウングレードのテストの場合にも役立ちます。)

本番サーバーで:

shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql

アップグレードされたサーバーで:

shell> mysql < dump-defs.sql

ダンプファイルにはテーブルデータが含まれないため、すばやく処理できます。これにより、長いデータロード操作を待つことなく、可能性のある非互換性を見分けることができます。ダンプファイルの処理中の警告やエラーを探します。

定義が正しく処理されていることを確認したら、データをダンプし、それをアップグレードしたサーバーにロードしてみます。

本番サーバーで:

shell> mysqldump --all-databases --no-create-info > dump-data.sql

アップグレードされたサーバーで:

shell> mysql < dump-data.sql

ここでテーブルの内容を確認し、いくつかのテストクエリーを実行します。

7.5 バイナリログを使用したポイントインタイム (増分) リカバリ

ポイントインタイムリカバリは、特定の時点以降に行われたデータ変更のリカバリを表します。一般に、この種類のリカバリは、サーバーをバックアップが行われた時点の状態にする完全バックアップのリストア後に実行されます。(完全バックアップは、セクション7.2「データベースバックアップ方法」に示すものなど、いくつかの方法で行うことができます。)さらに、ポイントインタイムリカバリは、完全バックアップの時点からより最近の時点まで、増分的にサーバーを最新にします。

ポイントインタイムリカバリは、これらの原則に基づきます。

  • ポイントインタイムリカバリの情報のソースは、完全バックアップ操作のあとに生成されたバイナリログファイルによって表される一連の増分バックアップです。そのため、サーバーを --log-bin オプションで起動して、バイナリロギングを有効にする必要があります (セクション5.2.4「バイナリログ」を参照してください)。

    バイナリログからデータをリストアするには、現在のバイナリログファイルの名前と場所を知っている必要があります。デフォルトで、サーバーはデータディレクトリにバイナリログファイルを作成しますが、--log-bin オプションでパス名を指定して、別の場所にファイルを配置できます。セクション5.2.4「バイナリログ」

    すべてのバイナリログファイルのリストを表示するには、次のステートメントを使用します。

    mysql> SHOW BINARY LOGS;

    現在のバイナリログファイルの名前を判断するには、次のステートメントを発行します。

    mysql> SHOW MASTER STATUS;
  • mysqlbinlog ユーティリティーは、バイナリログファイル内のイベントを、実行したり、表示したりできるように、バイナリフォーマットからテキストに変換します。mysqlbinlog には、イベント時間やログ内のイベントの位置に基づいて、バイナリログのセクションを選択するためのオプションがあります。セクション4.6.8「mysqlbinlog — バイナリログファイルを処理するためのユーティリティー」を参照してください。

  • バイナリログからイベントを実行すると、それらが表すデータの変更が再実行されます。これにより、特定の期間のデータの変更のリカバリが可能です。バイナリログからイベントを実行するには、mysql クライアントを使用して、mysqlbinlog 出力を処理します。

    shell> mysqlbinlog binlog_files | mysql -u root -p
  • ログの内容を表示すると、イベントを実行する前に、イベントの時間や位置を特定して、ログの内容の一部を選択する必要がある場合に役立つことがあります。ログからイベントを表示するには、mysqlbinlog 出力をページングプログラムに送信します。

    shell> mysqlbinlog binlog_files | more

    または、出力をファイルに保存し、テキストエディタでファイルを表示します。

    shell> mysqlbinlog binlog_files > tmpfileshell> ... edit tmpfile ...
  • ファイルに出力を保存することは、予期しない DROP DATABASE など、特定のイベントが削除されたログの内容を実行する場合の予備として役立ちます。ファイルの内容を実行する前に、実行されないステートメントをファイルから削除できます。ファイルの編集後、次のように内容を実行します。

    shell> mysql -u root -p < tmpfile

MySQL サーバーに実行する複数のバイナリログがある場合、安全な方法は、サーバーへの 1 つの接続を使用して、それらすべてを処理することです。これは、安全でない可能性があることを示す例です。

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

最初のログファイルに CREATE TEMPORARY TABLE ステートメントが含まれており、2 番目のログに一時テーブルを使用するステートメントが含まれている場合、サーバーへの異なる接続を使用して、このようにバイナリログを処理すると問題が発生します。最初の mysql プロセスが終了すると、サーバーは一時テーブルを削除します。2 番目の mysql プロセスでテーブルの使用を試みると、サーバーは不明なテーブルと報告します。

このような問題を回避するには、1 つの接続を使用して、処理するすべてのバイナリログの内容を実行します。これはそれを実行する 1 つの方法です。

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

もう 1 つのアプローチは、すべてのログを 1 つのファイルに書き込み、次にそのファイルを処理することです。

shell> mysqlbinlog binlog.000001 > /tmp/statements.sqlshell> mysqlbinlog binlog.000002 >> /tmp/statements.sqlshell> mysql -u root -p -e "source /tmp/statements.sql"

GTID (セクション17.1.3「グローバルトランザクション識別子を使用したレプリケーション」を参照) を含むバイナリログから読み取りながら、ダンプファイルに書き込む場合、次のように、mysqlbinlog--skip-gtids オプションを使用します。

shell> mysqlbinlog --skip-gtids binlog.000001 > /tmp/dump.sqlshell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sqlshell> mysql -u root -p -e "source /tmp/dump.sql"

7.5.1 イベント時間を使用したポイントインタイムリカバリ

リカバリの開始時間と終了時間を指示するには、mysqlbinlog--start-datetime および --stop-datetime オプションを DATETIME フォーマットで指定します。例として、2005 年 4 月 20 日午前 10 時ちょうどに、大きなテーブルを削除する SQL ステートメントが実行されたとします。テーブルとデータをリストアするには、前夜のバックアップをリストアして、次のコマンドを実行します。

shell> mysqlbinlog --stop-datetime="2005-04-20 9:59:59" \/var/log/mysql/bin.123456 | mysql -u root -p

このコマンドは、すべてのデータを --stop-datetime オプションで指定された日時までリカバリします。数時間たって、入力された誤った SQL ステートメントを検出しなかった場合、その後発生したアクティビティーもリカバリする必要がある可能性があります。これに基づいて、次のように開始日時で mysqlbinlog を再度実行できます。

shell> mysqlbinlog --start-datetime="2005-04-20 10:01:00" \/var/log/mysql/bin.123456 | mysql -u root -p

このコマンドでは、午前 10:01 から記録された SQL ステートメントが再実行されます。前夜のダンプファイルのリストアと 2 つの mysqlbinlog コマンドの組み合わせでは、午前 10:00 の 1 秒前までのすべてのものと午前 10:01 からのすべてのものをリストアします。

ポイントインタイムリカバリのこの方法を使用するには、ログを調べて、コマンドに指定する正確な時間を確認してください。ログファイルの内容を実行せずに表示するには、次のコマンドを使用します。

shell> mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

次に、テキストエディタで /tmp/mysql_restore.sql ファイルを開き、それを調べます。

mysqlbinlog に時間を指定して、特定の変更を除外することは、除外されるステートメントと同時に複数のステートメントが実行された場合、うまく機能しません。

7.5.2 イベントの位置を使用したポイントインタイムリカバリ

日時を指定する代わりに、ログ位置を指定するために、mysqlbinlog--start-position および --stop-position オプションを使用できます。それらは、日付ではなく、ログの位置番号を指定することを除いて、開始日および停止日オプションと同じように機能します。位置を使用することで、特に損害を与える SQL ステートメントと同じころに多くのトランザクションが発生している場合に、リカバリするログの部分をより正確に把握できます。位置番号を確認するには、予期していないトランザクションが実行された時間付近の期間で mysqlbinlog を実行しますが、結果を調査のためにテキストファイルにリダイレクトします。これは次のように実行できます。

shell> mysqlbinlog --start-datetime="2005-04-20 9:55:00" \--stop-datetime="2005-04-20 10:05:00" \/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

このコマンドは、/tmp ディレクトリに、有害な SQL ステートメントが実行された時間付近の SQL ステートメントを含む小さなテキストファイルを作成します。テキストエディタでこのファイルを開き、繰り返したくないステートメントを探します。リカバリを停止し、再開するバイナリログの位置を特定し、それらを書きとめます。位置は、うしろに番号が付いた log_pos とラベル付けされます。前のバックアップファイルのリストア後、位置番号を使用して、バイナリログファイルを処理します。たとえば、これらのようなコマンドを使用します。

shell> mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \| mysql -u root -pshell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \| mysql -u root -p

最初のコマンドは、指定した停止位置まで、すべてのトランザクションをリカバリします。2 番目のコマンドは、バイナリログの最後まで、指定された開始位置からのすべてのトランザクションをリカバリします。mysqlbinlog の出力には、各 SQL ステートメントが記録される前に、SET TIMESTAMP ステートメントが含まれるため、リカバリされたデータおよび関連する MySQL ログは、トランザクションが実行された元の時間を反映します。

7.6 MyISAM テーブルの保守とクラッシュリカバリ

このセクションでは、myisamchk を使用して、MyISAM テーブル (データとインデックスを格納するための .MYD ファイルおよび .MYI ファイルのあるテーブル) をチェックまたは修復する方法について説明します。myisamchk の一般的な背景に関しては、セクション4.6.3「myisamchk — MyISAM テーブルメンテナンスユーティリティー」を参照してください。その他のテーブル修復情報については、セクション2.11.4「テーブルまたはインデックスの再作成または修復」にあります。

myisamchk を使用して、データベーステーブルをチェック、修復、または最適化できます。次のセクションでは、これらの操作を実行する方法と、テーブル保守スケジュールをセットアップする方法について説明します。myisamchk を使用して、テーブルに関する情報を取得することについては、セクション4.6.3.5「myisamchk によるテーブル情報の取得」を参照してください。

myisamchk によるテーブルの修復はきわめてセキュアですが、テーブルに対して多くの変更を行う可能性のある修復または保守操作を実行する前に、常にバックアップを作成することをお勧めします。

インデックスに影響する myisamchk 操作により、MyISAMFULLTEXT インデックスが、MySQL サーバーで使用されている値と互換性がない全文パラメータで再構築される可能性があります。この問題を回避するには、セクション4.6.3.1「myisamchk の一般オプション」のガイドラインに従ってください。

MyISAM テーブルの保守は、myisamchk が実行するものと似た操作を実行する SQL ステートメントを使用しても実行できます。

  • MyISAM テーブルをチェックするには、CHECK TABLE を使用します。

  • MyISAM テーブルを修復するには、REPAIR TABLE を使用します。

  • MyISAM テーブルを最適化するには、OPTIMIZE TABLE を使用します。

  • MyISAM テーブルを分析するには、ANALYZE TABLE を使用します。

これらのステートメントの詳細については、セクション13.7.2「テーブル保守ステートメント」を参照してください。

これらのステートメントは、直接または mysqlcheck クライアントプログラムを利用して使用できます。myisamchk に勝るこれらのステートメントの利点の 1 つは、サーバーがすべての作業を行うということです。myisamchk では、myisamchk とサーバー間で不要なやり取りがないように、サーバーが同時にテーブルを使用しないことを確認する必要があります。

7.6.1 クラッシュリカバリへの myisamchk の使用

このセクションでは、MySQL データベースのデータの破損をチェックし、処理する方法について説明します。テーブルが頻繁に破損する場合は、その理由を見つけるようにしてください。セクションB.5.4.2「MySQL が繰り返しクラッシュする場合の対処方法」を参照してください。

MyISAM テーブルがどのように破損する可能性があるかについての説明は、セクション15.2.4「MyISAM テーブルの問題点」を参照してください。

外部ロックを無効にして mysqld を実行した (これはデフォルトです) 場合、mysqld が同じテーブルを使用中に、myisamchk を使用して、テーブルを確実にチェックすることはできません。myisamchk を実行している間、mysqld 経由でテーブルにだれもアクセスしないことを確信できる場合は、テーブルのチェックを開始する前に、mysqladmin flush-tables を実行する必要があるだけです。これを保証できない場合は、テーブルのチェック中に、mysqld を停止する必要があります。myisamchk を実行して、mysqld が同時に更新しているテーブルをチェックすると、テーブルが破損していなくても、破損しているという警告を受け取ることがあります。

外部ロックを有効にしてサーバーを実行する場合は、myisamchk を使用していつでもテーブルをチェックできます。この場合に、サーバーが myisamchk で使用しているテーブルを更新しようとする場合、サーバーは myisamchk が終了するまで待ってから、続行します。

myisamchk を使用して、テーブルを修復または最適化する場合は、mysqld サーバーがそのテーブルを使用していないことを常に確認する必要があります (これは外部ロックが無効にされている場合にも適用されます)。mysqld を停止しない場合、myisamchk を実行する前に、少なくとも mysqladmin flush-tables を実行してください。サーバーと myisamchk が同時にテーブルにアクセスすると、テーブルが破損する可能性があります

クラッシュリカバリを実行する場合、データベース内の各 MyISAM テーブル tbl_name が次の表に示すデータベースディレクトリ内の 3 つのファイルに対応することを理解しておくことが重要です。

ファイル目的
tbl_name.frm定義 (フォーマット) ファイル
tbl_name.MYDデータファイル
tbl_name.MYIインデックスファイル

これらの 3 つのファイルの種類はそれぞれさまざまに破損することがありますが、ほとんどの場合に問題はデータファイルとインデックスファイルで発生します。

myisamchk は、.MYD データファイルのコピーを行ごとに作成することによって機能します。これは、古い .MYD ファイルを削除し、新しいファイルを元のファイル名に変更して、修復ステージを終了します。--quick を使用した場合、myisamchk は一時 .MYD ファイルを作成せず、代わりに .MYD ファイルが正しいとみなし、.MYD ファイルに手を加えずに新しいインデックスファイルだけを生成します。myisamchk.MYD ファイルが破損しているかどうかを自動的に検出し、破損している場合は修復を中止するため、これは安全です。myisamchk--quick オプションを 2 回指定することもできます。この場合、myisamchk は一部のエラー (重複キーエラーなど) で中止せず、.MYD ファイルを修正して、それらを解決しようとします。通常、2 つの --quick オプションの使用は、通常の修復を実行するためにディスクの空き容量が少なすぎる場合にのみ役立ちます。その場合、少なくとも myisamchk を実行する前に、テーブルのバックアップを作成してください。

7.6.2 MyISAM テーブルのエラーのチェック方法

MyISAM テーブルをチェックするには、次のコマンドを使用します。

  • myisamchk tbl_name

    これはすべてのエラーの 99.99% を発見します。これで発見できないエラーは、データファイルのみに関連する破損です (きわめてまれです)。テーブルをチェックする場合、通常、myisamchk をオプションなし、または -s (サイレント) オプションで実行してください。

  • myisamchk -m tbl_name

    これはすべてのエラーの 99.999% を発見します。それは最初にすべてのインデックスエントリでエラーをチェックし、次にすべての行を読み取ります。それは行内のすべてのキー値のチェックサムを計算し、チェックサムがインデックスツリー内のキーのチェックサムと一致することを確認します。

  • myisamchk -e tbl_name

    これはすべてのデータの完全で徹底的なチェックを実行します (-e拡張チェックを意味します)。それは各行のすべてのキーのチェック読み取りを実行し、それらが実際に正しい行を指していることを確認します。これは、多数のインデックスを持つ大きなテーブルの場合に長い時間がかかることがあります。通常、myisamchk は見つかった最初のエラーのあとで停止します。詳細情報を取得する場合は、-v (verbose) オプションを追加できます。これにより、myisamchk は最大 20 のエラーまで続行します。

  • myisamchk -e -i tbl_name

    これは、前述のコマンドと同様ですが、-i オプションは myisamchk に追加の統計情報を出力するように伝えます。

ほとんどの場合、テーブルをチェックするためには、テーブル名以外の引数なしの単純な myisamchk コマンドで十分です。

7.6.3 MyISAM テーブルの修復方法

このセクションの説明では、MyISAM テーブル (拡張子 .MYI および .MYD) に対し myisamchk を使用する方法について説明します。

さらに、CHECK TABLE および REPAIR TABLE ステートメントを使用して、MyISAM テーブルをチェックして修復することもできます。セクション13.7.2.2「CHECK TABLE 構文」およびセクション13.7.2.5「REPAIR TABLE 構文」を参照してください。

破損したテーブルの兆候として、予期せずに中止するクエリーや次のような観察可能なエラーが含まれます。

  • tbl_name.frm が変更に対してロックされます

  • ファイル tbl_name.MYI が見つかりません (エラーコード: nnn)。

  • 予期しないファイルの終わり

  • レコードファイルがクラッシュしました

  • テーブルハンドラからエラー nnn を取得します

エラーの詳細を取得するには、perrornnn を実行します。ここで、nnn はエラー番号です。次の例は、perror を使用して、テーブルの問題を示すもっとも一般的なエラー番号の意味を見つける方法を示しています。

shell> perror 126 127 132 134 135 136 141 144 145MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired

エラー 135 (レコードファイルに空きがない) およびエラー 136 (インデックスファイルに空きがない) は、単純な修復で修正できるエラーではありません。この場合、ALTER TABLE を使用して、MAX_ROWS および AVG_ROW_LENGTH テーブルオプションの値を増やす必要があります。

ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;

現在のテーブルオプション値が不明な場合は、SHOW CREATE TABLE を使用します。

その他のエラーの場合は、テーブルを修復する必要があります。myisamchk は通常発生するほとんどの問題を検出し、修正できます。

修復プロセスには、ここに示す最大 4 つのステージがあります。始める前に、場所をデータベースディレクトリに変更し、テーブルファイルの権限をチェックしてください。Unix では、mysqld を実行するユーザーによって (およびチェックするファイルにアクセスする必要があるため、チェックするユーザーにも)、それらが読み取り可能であることを確認します。ファイルを変更する必要があることが分かったら、それらに書き込みできる必要もあります。

このセクションでは、テーブルチェックが失敗した (セクション7.6.2「MyISAM テーブルのエラーのチェック方法」 で説明しているものなど) 場合、または myisamchk が提供する拡張機能を使用する場合について説明します。

テーブル保守に使用される myisamchk オプションについては、セクション4.6.3「myisamchk — MyISAM テーブルメンテナンスユーティリティー」で説明しています。myisamchk には、パフォーマンスを向上できるメモリー割り当てを制御するために設定できる変数もあります。セクション4.6.3.6「myisamchk メモリー使用量」を参照してください。

コマンド行からテーブルを修復する場合は、まず mysqld サーバーを停止する必要があります。リモートサーバーで mysqladmin shutdown を実行すると、mysqladmin が戻ったあとに、すべてのステートメント処理が停止し、すべてのインデックス変更がディスクにフラッシュされるまで、しばらくの間 mysqld サーバーがまだ使用できることに注意してください。

ステージ 1: テーブルのチェック

myisamchk *.MYI または時間があれば myisamchk -e *.MYI を実行します。-s (サイレント) オプションを使用すると、不要な情報を抑制します。

mysqld サーバーが停止している場合は、--update-state オプションを使用して、myisamchk にテーブルをチェック済みとマークするように指示してください。

myisamchk がエラーを報告しているテーブルだけを修復する必要があります。そのようなテーブルの場合、ステージ 2 に進みます。

チェック時に、予期しないエラー (out of memory エラーなど) を受け取った場合、または myisamchk がクラッシュした場合、ステージ 3 へ進みます。

ステージ 2: 簡単で安全な修復

まず myisamchk -r -q tbl_name を試します (-r -qクイックリカバリモードを意味します)。これは、データファイルにアクセスせずに、インデックスファイルを修復しようとします。データファイルに、必要なすべてのものが含まれ、削除リンクがデータファイル内の正しい場所を指している場合、これは機能するはずであり、テーブルが修正されます。次のテーブルの修復を開始します。そうでない場合は、次の手順を使用します。

  1. 続行する前に、データファイルのバックアップを作成します。

  2. myisamchk -r tbl_name を使用します (-rリカバリモードを意味します)。これによって、正しくない行と削除された行がデータファイルから削除され、インデックスファイルが再構築されます。

  3. 先述のステップが失敗した場合、myisamchk --safe-recover tbl_name を使用します。安全なリカバリモードでは、通常のリカバリモードで扱われないわずかなケースを処理する古いリカバリ方法を使用します (ただし遅くなります)。

注記

修復操作を大幅に高速化する場合、sort_buffer_size および key_buffer_size 変数の値をそれぞれ、myisamchk の実行時に使用可能なメモリーの約 25% に設定してください。

修復時に、予期しないエラー (out of memory エラーなど) を受け取った場合、または myisamchk がクラッシュした場合、ステージ 3 へ進みます。

ステージ 3: 困難な修復

このステージに到達するのは、インデックスファイル内の最初の 16K バイトのブロックが破損しているか、誤った情報が含まれている場合、またはインデックスファイルが失われている場合に限られるはずです。この場合、新しいインデックスファイルを作成する必要があります。次のように実行します。

  1. データファイルを安全な場所に移動します。

  2. テーブル記述ファイルを使用して、新しい (空の) データファイルとインデックスファイルを作成します。

    shell> mysql db_namemysql> SET autocommit=1;mysql> TRUNCATE TABLE tbl_name;mysql> quit
  3. 古いデータファイルを新しく作成したデータファイルにコピーします。(古いファイルを新しいファイルに単に移動しないでください。何か異常があった場合に備えて、コピーを保持する必要があります。)

重要

レプリケーションを使用している場合、それにはファイルシステム操作が含まれ、これらは MySQL によって記録されないため、上記の手順を実行する前に、それを停止してください。

ステージ 2 に戻ります。myisamchk -r -q が機能するはずです。(これは無限ループにならないはずです。)

手順全体を自動的に実行する REPAIR TABLE tbl_name USE_FRM SQL ステートメントを使用することもできます。REPAIR TABLE を使用すると、サーバーがすべての作業を実行するため、ユーティリティーとサーバー間の不要なやり取りの可能性もなくなります。セクション13.7.2.5「REPAIR TABLE 構文」を参照してください。

ステージ 4: きわめて困難な修復

.frm 記述ファイルもクラッシュしている場合のみ、このステージに到達するはずです。記述ファイルはテーブルが作成されたあとに変更されないため、これが発生することはないはずです。

  1. バックアップから記述ファイルをリストアし、ステージ 3 に戻ります。インデックスファイルをリストアし、ステージ 2 に戻ることもできます。後者の場合、myisamchk -r で起動してください。

  2. バックアップがないが、テーブルの作成方法が正確にわかっている場合は、別のデータベースにテーブルのコピーを作成します。新しいデータファイルを削除して、ほかのデータベースから .frm 記述ファイルと .MYI インデックスファイルを、クラッシュしたデータベースへ移動します。これにより、新しい記述ファイルとインデックスファイルが得られますが、.MYD データファイルはそのまま残ります。ステージ 2 に戻り、インデックスファイルの再構築を試みます。

7.6.4 MyISAM テーブルの最適化

断片化した行を結合し、行の削除または更新の結果発生した無駄な領域を削除するには、myisamchk をリカバリモードで実行します。

shell> myisamchk -r tbl_name

OPTIMIZE TABLE SQL ステートメントを使用して、同様にテーブルを最適化することができます。OPTIMIZE TABLE はテーブルの修復とキー分析を行い、さらに、キーのルックアップが速くなるように、インデックスツリーをソートします。OPTIMIZE TABLE を使用すると、サーバーがすべての作業を実行するため、ユーティリティーとサーバー間の不要なやり取りの可能性もなくなります。セクション13.7.2.4「OPTIMIZE TABLE 構文」を参照してください。

myisamchk には、テーブルのパフォーマンスを向上させるために使用できる多数のその他オプションがあります。

  • --analyze または -a: キー分布分析を実行します。これは、結合オプティマイザが、テーブルを結合する順番と、それが使用するインデックスをより適切に選択できるようにすることで、結合パフォーマンスを向上させます。

  • --sort-index または -S: インデックスブロックをソートします。これはシークを最適化し、インデックスを使用するテーブルスキャンを高速化します。

  • --sort-records=index_num または -R index_num: 特定のインデックスに従って、データ行をソートします。これにより、データが大幅に局所に集中化されるため、このインデックスを使用する、範囲に基づいた SELECT または ORDER BY 操作が高速化する可能性があります。

利用可能なすべてのオプションの完全な説明については、セクション4.6.3「myisamchk — MyISAM テーブルメンテナンスユーティリティー」を参照してください。

7.6.5 MyISAM テーブル保守スケジュールのセットアップ

問題が発生するのを待つより、テーブルチェックを定期的に実行することをお勧めします。MyISAM テーブルをチェックまたは修復する 1 つの方法は、CHECK TABLE および REPAIR TABLE ステートメントを使用することです。セクション13.7.2「テーブル保守ステートメント」を参照してください。

テーブルをチェックする別の方法は、myisamchk を使用することです。保守の目的には、myisamchk -s を使用できます。-s オプション (--silent の短縮形) により、サイレントモードで myisamchk が実行され、エラーが発生した場合のみ、メッセージが出力されます。

自動 MyISAM テーブルチェックを有効にすることをお勧めします。たとえば、マシンが更新の途中で再起動を実行した場合、通常、影響を受けた可能性のある各テーブルが使用される前に、それをチェックする必要があります。(これらは予期されるクラッシュしたテーブルです。) サーバーにMyISAM テーブルを自動的にチェックさせるには、それを --myisam-recover-options オプションを付けて起動します。セクション5.1.3「サーバーコマンドオプション」を参照してください。

通常のシステム操作時にも定期的にテーブルをチェックしてください。たとえば、crontab ファイル内の次のような行を使用して、週 1 回 cron ジョブを実行し、重要なテーブルをチェックします。

35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir*.MYI
関連キーワード:  バックアップ,テーブル,します,サーバー,7,mysqldump,MySQL,,データ,myisamchk