mysqlデータベースのバックアップ

mysql|バックアップ|データ|データベース

データベース テーブルが失われたり破損したりした場合に備えて、データベースをバックアップすることが重要です。システム クラッシュが発生した場合、データ損失を最小限に抑えながらテーブルをクラッシュ発生時の状態に復元できるようにしたいと考えるのは当然です。場合によっては、MySQL 管理者が大混乱を引き起こすこともあります。管理者はテーブルが破損していることをすでに知っており、vi や Emacs などのエディタを使用してテーブルを直接編集しようとします。これはテーブルにとって決して良いことではありません。データベースをバックアップする 2 つの主な方法は、mysqldump プログラムを使用するか、データベース ファイルを直接コピーする (cp、cpio、tar などを使用する) ことです。 各方法には長所と短所があります。mysqldump は MySQL サーバーと連携して動作します。直接コピー方式はサーバーの外部で実行されるため、コピーしているテーブルがクライアントによって変更されないように措置を講じる必要があります。ファイル システム バックアップを使用してデータベースをバックアップする場合も、同じ問題が発生します。ファイル システムのバックアップ プロセス中にデータベース テーブルが変更されると、バックアップされたテーブル ファイルのサブジェクトが不一致になり、テーブルが今後の回復には無意味です。ファイル システムのバックアップとファイルの直接コピーの違いは、後者の場合はバックアップ プロセスを完全に制御できるため、サーバーがテーブルをそのままにしておくための措置を講じることができることです。 mysqldump は直接コピーするよりも遅くなります。 mysqldump は、ハードウェア アーキテクチャが異なる場合でも、他のマシンに移植可能なテキスト ファイルを生成します。コピーしているテーブルが MyISAM ストレージ形式を使用していない限り、ファイルを直接コピーすることは他のマシンに移植できません。 ISAM テーブルは、同様のハードウェア構造を持つマシン上でのみコピーできます。 MySQL 3.23 で導入された MyISAM テーブル ストレージ形式は、この形式がマシンに依存しないため、この問題を解決します。そのため、ファイルを直接コピーして、異なるハードウェア構造を持つマシンに移植できます。 2 つの条件が満たされている限り、もう一方のマシンも MySQL 3.23 以降を実行している必要があり、ファイルは ISAM 形式ではなく MyISAM 形式で表されている必要があります。

どのバックアップ方法を使用する場合でも、データベースを復元する必要がある場合、最良の結果を確実に得るために従うべきいくつかの原則があります:

バックアップを定期的に実行します。計画を立ててそれを守りましょう

サーバーに変更ログを実行させます。変更ログは、クラッシュ後にデータを回復する必要がある場合に役立ちます。バックアップ ファイルを使用してデータをバックアップ時の状態に復元した後、更新ログでクエリを実行することで、バックアップ後に行われた変更を再適用できます。これにより、データベース内のテーブルが復元されます。衝突が起きたときの状態。 ファイル システム バックアップの用語では、データベース バックアップ ファイルはフル ダンプを表し、更新ログは増分ダンプを表します。

backup1、buckup2 などの、統一されたわかりやすいバックアップ ファイル命名メカニズム

の使用は、特に意味がありません。リカバリを実行するとき、ファイルの内容を把握するのに時間を無駄にすることになります。データベース名と日付を使用してバックアップ ファイル名を形成すると便利な場合があります。例:

%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
%mysqldump menagerie >/usr/archives/mysql/menagerie.1999-10-02

バックアップ後、圧縮してください。通常、バックアップは巨大です。ログ ファイルを期限切れにするのと同じように、バックアップ ファイルも期限切れにして、ディスクがいっぱいになるのを防ぐ必要があります。ファイル システム バックアップを使用して、バックアップ ファイルをバックアップします。データ ディレクトリだけでなく、データベースのバックアップが含まれているディスク ドライブもクリアされるような完全なクラッシュが発生した場合は、大きな問題に直面することになります。変更ログもバックアップしてください。バックアップ ファイルは、データベースに使用されているファイル システムとは異なるファイル システムに配置します。これにより、バックアップの生成によってデータ ディレクトリを含むファイル システムがいっぱいになる可能性が低くなります。

バックアップの作成に使用されるテクニックは、データベースを別のマシンにコピーする場合にも役立ちます。最も一般的には、データベースは別のホストで実行されているサーバーに移動されますが、データを同じホスト上の別のサーバーに移動することもできます。

1. mysqldump を使用してデータベースをバックアップおよびコピーします

mysqldumo プログラムを使用してデータベース バックアップ ファイルを生成すると、デフォルトで、ファイルの内容にはダンプされるテーブルを作成する CREATE ステートメントと、次の内容を含む INSERT ステートメントが含まれます。テーブル内の行データ。つまり、mysqldump によって生成された出力は、後でデータベースを再構築するための mysql への入力として使用できます。 次のようにデータベース全体を 1 つのテキスト ファイルにダンプできます:

%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02

出力ファイルの先頭は次のようになります:

# MySQL ダンプ 6.0#
# ホスト: localhost データベース: samp_db
#---------------------------------- - ---
# サーバー バージョン 3.23.2-alpha-log
## テーブル 'absence' のテーブル構造#
CREATE TABLE names(student_id int(10)
unsigned DEFAULT '0' NOT NULL, date date DEFAULT '0000 - 00-00' NOT NULL, PRIMARY KEY (student_id,date));
## テーブル '欠席' のデータをダンプしています#
INSERT INTO 欠席 VALUES (3,'1999-09-03');
INSERT INTO 欠席 VALUES ( 5,'1999-09-03');
INSERT INTO 不在 VALUES (10,'1999-09-08');
......

ファイルの残りの部分には、さらに INSERT ステートメントと CREATE TABLE ステートメントがあります。
バックアップを圧縮したい場合は、次のようなコマンドを使用します:

%mysqldump samp_db │ gzip >/usr/archives/mysql/samp_db.1999-10-02.gz

巨大なデータベースが必要な場合は、出力ファイルも大きくなり、管理が困難になる可能性があります。必要に応じて、mysqldump コマンド ラインでデータベース名の後に個々のテーブル名をリストして、その内容をダンプすることができます。これにより、ダンプ ファイルがより小さく管理しやすいファイルに分割されます。次の例は、samp_db データベースのいくつかのテーブルを別のファイルにダンプする方法を示しています。

%mysqldump samp_db 学生スコア イベント欠席 >grapbook.sql
%mysqldump samp_db member President >hist-league.sql

If you generated To別のデータベースの内容を定期的に更新するバックアップ ファイルを準備する場合は、--add-drop-table オプションを使用するとよいでしょう。これにより、サーバーに DROP TABLE IF EXISTS ステートメントをバックアップ ファイルに書き込むように指示され、バックアップ ファイルを取得して 2 番目のデータベースにロードするときに、テーブルがすでに存在していてもエラーは発生しません。データベースを別のサーバーに移動できるようにデータベースをダンプする場合は、バックアップ ファイルを作成する必要さえありません。データベースが別のホストに存在することを確認し、mysql が mysqldump の出力を直接読み取ることができるように、パイプを使用してデータベースをダンプします。たとえば、データベース samp_db をホスト pig-viper.snake.net から boa.snake.net にコピーしたい場合、次のように簡単に実行できます:

%mysqladmin -h boa.snake.net create samp_db
% mysqldump samp_db │ mysql - h boa.snake.net samp_db

将来、boa.snake.net 上のデータベースを再度更新したい場合は、mysqladmin コマンドをスキップして、mysqldump に --add-drop-table を追加してください。テーブルが既に存在することを回避します。 エラー:

%mysqldump --add-drop-table samp_db │ mysql -h boa.snake.net samp_db

mysqldump のその他の便利なオプションには、次のものがあります:

--flush-logs および --ロックとテーブルの組み合わせは機能します データベースのチェックポイントが役に立ちます。
--lock-tables は、ダンプしているすべてのテーブルをロックします。
--flush-logs は、更新ログ ファイルを閉じて再度開きますが、新しい更新ログには、バックアップ ポイントからデータベースを変更したクエリのみが含まれます。これにより、更新ログのチェックポイントのバックアップ時間が設定されます。 (ただし、更新を実行する必要があるクライアントがある場合、バックアップ中にクライアント アクセスに対してすべてのテーブルをロックするのは得策ではありません。) --flush-logs を使用してバックアップにチェックポイントを設定する場合は、次のようにするのが最善です。データベース全体をダンプします。個別のファイルをダンプすると、更新ログのチェックポイントをバックアップ ファイルと同期することがより困難になります。リカバリ中に、通常はデータベースごとに更新ログの内容を抽出します。個々のテーブルの更新を抽出するオプションはないため、自分で抽出する必要があります。デフォルトでは、mysqldump はテーブルの内容全体をメモリに読み込んでから書き込みます。通常、これは実際には不要であり、実際、大きなテーブルがある場合はほとんど失敗します。 --quick オプションを使用すると、mysqldump が行を取得するたびに各行を書き出すように指示できます。ダンプ プロセスをさらに最適化するには、--quick の代わりに --opt を使用します。 --opt オプションは、データのダンプとその読み取りを高速化する追加のオプションをオンにします。バックアップには速度の利点があるため、--opt を使用してバックアップを実装するのがおそらく最も一般的な方法です。ただし、--opt オプションにはコストがかかることに注意してください。--opt は、データベースへの他のクライアントのアクセスではなく、バックアップ プロセスを最適化します。 --opt オプションは、すべてのテーブルを一度にロックすることで、ダンプしているテーブルを誰も更新できないようにします。一般的なデータベース アクセスへの影響を簡単に確認できます。データベースが通常非常に頻繁に使用される場合は、1 日に 1 回バックアップを調整するだけで済みます。 --opt の逆の効果を持つオプションは --dedayed です。このオプションにより、mysqldump は INSERT ステートメントの代わりに INSERT DELAYED ステートメントを書き込みます。 --layed は、データ ファイルを別のデータベースにロードしており、この操作がそのデータベースに表示されるクエリに与える影響を最小限に抑えたい場合に役立ちます。 --compress オプションは、ネットワーク上で転送されるバイト数を減らすため、データベースを別のマシンにコピーするときに役立ちます。以下に例を示します。 --compress はリモート ホスト上のサーバーと通信するプログラムにのみ指定され、ローカル ホストに接続するプログラムには指定されないことに注意してください:

%mysqldump --opt samp_db │ mysql --compress -h boa.snake.net samp_db

mysqldump には多くのオプションがあります。詳細については、「MySQL リファレンス マニュアル」を参照してください。

2. データベースを直接コピーするバックアップとコピー方法を使用します

mysqldump を使用せずにデータベースとテーブルをバックアップするもう 1 つの方法は、データベース テーブル ファイルを直接コピーすることです。通常、これは cp、tar、cpio などのユーティリティを使用して行われます。この記事の例では cp を使用します。 直接バックアップ方法を使用する場合は、テーブルが使用されていないことを確認する必要があります。テーブルのコピー中にサーバーがテーブルを変更した場合、コピーは無意味になります。コピーの整合性を確保する最善の方法は、サーバーをシャットダウンし、ファイルをコピーしてからサーバーを再起動することです。サーバーをシャットダウンしたくない場合は、テーブルチェックの実行中にサーバーをロックしてください。サーバーが実行中の場合、ファイルのコピーにも同じ制限が適用されるため、同じロック プロトコルを使用してサーバーを「停止」する必要があります。 サーバーがダウンしているか、コピーするテーブルがロックされていると仮定して、samp_db データベース全体をバックアップ ディレクトリにバックアップする方法を以下に示します (DATADIR はサーバーのデータ ディレクトリを表します):

%cd DATADIR%cp -r samp_db /usr/archive/ mysql

単一のテーブルは次のようにバックアップできます:

%cd DATADIR/samp_db%cp member.* /usr/archive/mysql/samp_db%cp core.* /usr/archive/mysql/ samp_db....

バックアップが完了したら、サーバーを再起動するか (サーバーをシャットダウンした場合)、テーブルのロックを解放します (サーバーを実行したままにした場合)。 直接コピー ファイルを使用してあるマシンから別のマシンにデータベースをコピーするには、ファイルを他のサーバー ホスト上の適切なデータ ディレクトリにコピーするだけです。ファイルが MyIASM 形式であるか、両方のマシンが同じハードウェア構造であることを確認してください。そうしないと、データベースに他のマシン上の奇妙な内容が含まれてしまいます。また、データベース テーブルのインストール中に、別のマシン上のサーバーがデータベース テーブルにアクセスしないようにする必要もあります。

3. データベースのレプリケーション

レプリケーションは、データベースを別のサーバーにコピーすることに似ていますが、その正確な意味は、2 つのデータベースをリアルタイムで完全に同期させることです。この機能はバージョン 3.23 で登場しますが、まだ完成度が低いため、この記事では詳しく紹介しません。

4. バックアップを使用してデータを復元する

データベースの破損は、程度はさまざまですが、さまざまな理由で発生します。運が良ければ、破損するのは 1 つまたは 2 つのテーブルだけである可能性があります (停電など)。運が悪ければ、データ ディレクトリ全体を置き換えなければならない場合もあります (ディスクの破損など)。ユーザーが誤ってデータベースやテーブルを削除した場合など、特定の状況ではリカバリも必要になります。これらの不幸な出来事の原因に関係なく、何らかの回復を実行する必要があります。テーブルが破損しているが失われていない場合は、myisamchk または isamchk を使用して修復してみてください。そのような破損が修復可能な場合は、バックアップ ファイルを使用する必要がない可能性があります。テーブル修復のプロセスについては、「データベースの保守と修復」を参照してください。回復プロセスには、バックアップ ファイルと変更ログという 2 つの情報ソースが必要です。バックアップ ファイルはテーブルをバックアップ実行時の状態に復元しますが、通常、テーブルはバックアップから問題が発生するまでの間に変更されており、更新ログにはこれらの変更を行うために使用されたクエリが含まれています。ログ ファイルを mysql への入力として使用して、クエリを繰り返すことができます。まさにこれが、変更ログ
を有効にする必要がある理由です。回復プロセスは、回復する必要がある情報の量によって異なります。実際、更新ログを単一のテーブルに適用するよりもデータベースに適用する方が簡単であるため、単一のテーブルを復元するよりもデータベース全体を復元する方が簡単です。

4.1 データベース全体を復元する

まず、復元したいデータベースが許可テーブルを含む mysql データベースである場合は、--skip-grant-table オプションを使用してサーバーを実行する必要があります。そうしないと、認可テーブルが見つからないというメッセージが表示されます。テーブルを復元した後、mysqladmin flash-privileges を実行して、サーバーに認証トークンをロードして使用するように指示します。後で必要になる場合は、データベース ディレクトリの内容を別の場所にコピーします。最新のバックアップ ファイルを使用してデータベースを再インストールします。 mysqldump によって生成されたファイルを使用する場合は、それを mysql への入力として使用します。データベースから直接コピーしたファイルを使用している場合は、それらをデータベース ディレクトリに直接コピーして戻します。ただし、この場合、ファイルをコピーする前にデータベースを閉じて再起動する必要があります。更新ログを使用して、バックアップ後にデータベース テーブルを変更するクエリを繰り返します。該当する変更ログについては、入力として mysql に渡します。 --one-database オプションを指定すると、mysql は復元するデータベースに対してのみクエリを実行します。すべての更新ログ ファイルを使用する必要があることがわかっている場合は、ログが含まれるディレクトリで次のコマンドを使用できます:

% ls -t -r -1 update.[0-9]* │ xargs cat │ mysql --one -database db_name

ls コマンドは、サーバーが生成した順序に従ってソートされた、更新ログ ファイルの単一列リストを生成します (アイデア: ファイルのいずれかを変更すると、ソート順序が変更され、更新ログが間違った順序で使用される可能性があります。特定の変更ログを使用する可能性があります。たとえば、バックアップ後に生成された更新ログには、update.392、update.393 などの名前が付けられます。次のように再実行できます。

%mysql --one-database db_name < update.392
%mysql --one -データベース db_name < update.393
....
リカバリを実行しており、誤って推奨された DROP DATABASE、DROP TABLE、または DELETE ステートメントによって失われた情報をリカバリするために更新ログを使用している場合は、使用する前に必ず更新ログからこれらのステートメントを削除してください。

4.2 単一テーブルの復元

単一テーブルの復元はより複雑です。 mysqldump によって生成されたバックアップ ファイルを使用し、そのファイルに対象のテーブルのデータが含まれていない場合は、関連する行からデータを抽出し、mysql への入力として使用する必要があります。これは簡単な部分です。難しいのは、そのテーブルにのみ適用されるフラグメントを更新ログから取得することです。これには、変更ログから複数行のクエリを抽出する mysql_find_rows ユーティリティが役立つ場合があります。もう 1 つの方法は、別のサーバーを使用してデータベース全体を復元し、必要なテーブル ファイルを元のデータベースにコピーすることです。それは本当に簡単かもしれません！ファイルをデータベース ディレクトリにコピーして戻すときは、元のデータベース サーバーがシャットダウンされていることを確認してください。