データベースをバックアップするには、mysqldump プログラムを使用するか、データベース ファイルを直接コピーする (cp、cpio、tar などを使用する) という 2 つの主な方法があります。この記事では、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## テーブルが存在しない場合のテーブル構造
#CREATE TABLE 不在(student_id int(10) unsigned DEFAULT 0 NOT NULL、日付 date DEFAULT 0000-00-00 NOT NUL L、
PRIMARY KEY (student_id,date));## テーブル欠席のデータをダンプ #INSERT INTO 欠席 VALUES (3,1999-09-03);INSERT INTO 欠席 VALUE S (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 メンバー プレジデント >hist-league.sql
別のデータベースの内容を定期的に更新するために使用するバックアップ ファイルを生成している場合は、 --add-drop-table オプションを使用するとよいでしょう。これにより、サーバーに DROP TABLE IF EXISTS ステートメントをバックアップ ファイルに書き込むように指示され、バックアップ ファイルを取得して 2 番目のデータベースにロードするときに、テーブルがすでに存在していてもエラーは発生しません。
データベースを別のサーバーに移動できるようにデータベースをダンプする場合は、バックアップ ファイルを作成する必要さえありません。データベースが別のホストに存在することを確認し、mysql が mysqldump の出力を直接読み取ることができるように、パイプを使用してデータベースをダンプします。たとえば、データベース samp_db をホスト pig-viper.snake.net から boa.snake.net にコピーしたい場合は、次のように簡単に実行できます。
%mysqladmin -h boa.snake.net samp_db を作成
%mysqldump samp_db | mysql -h boa.snake.net samp_db
将来、boa.snake.net 上のデータベースを再度更新したい場合は、mysqladmin コマンドをスキップしますが、テーブルが既に存在するエラー: %mysqldump --add- が発生するのを避けるために --add-drop-table を mysqldump に追加してください。ドロップテーブル samp_db | mysql -h boa.snake.net samp_db
その他の便利な mysqldump オプションには、次のものがあります。 --flush-logs と --lock-tables の組み合わせは、データベースのチェックポイント作成に役立ちます。 --lock-tables はダンプしているすべてのテーブルをロックし、--flush-logs は更新ログ ファイルを閉じて再度開きます。新しい更新ログには、バックアップ ポイントからデータベースを変更したクエリのみが含まれます。これにより、更新ログのチェックポイントのバックアップ時間が設定されます。 (ただし、更新を実行する必要があるクライアントがある場合、バックアップ中のクライアント アクセスに関してすべてのテーブルをロックすることは得策ではありません。)
--flush-logs を使用してバックアップにチェックポイントを作成する場合は、おそらくデータベース全体をダンプするのが最善です。
個別のファイルをダンプすると、更新ログのチェックポイントをバックアップ ファイルと同期することがより困難になります。リカバリ中に、通常はデータベースごとに更新ログの内容を抽出します。個々のテーブルの更新を抽出するオプションはないため、自分で抽出する必要があります。
デフォルトでは、mysqldump はテーブルの内容全体をメモリに読み込んでから書き込みます。通常、これは実際には不要であり、実際、大きなテーブルがある場合はほとんど失敗します。 --quick オプションを使用すると、mysqldump が行を取得するたびに各行を書き出すように指示できます。注湯プロセスをさらに最適化するには、--quick の代わりに --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 /アーカイブ/mysql
単一のテーブルは次のようにバックアップできます。
%cd DATADIR/samp_db%cp メンバー.* /usr/archive/mysql/samp_db%cp スコア.* /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-privilege を実行して、サーバーに認証トークンをロードして使用するように指示します。
後で必要になる場合は、データベース ディレクトリの内容を別の場所にコピーします。
最新のバックアップ ファイルを使用してデータベースを再インストールします。 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-database db_name < update.393
……
リカバリを実行しており、誤って推奨された DROP DATABASE、DROP TABLE、または DELETE ステートメントによって失われた情報をリカバリするために更新ログを使用している場合は、使用する前に必ず更新ログからこれらのステートメントを削除してください。
4.2 単一テーブルの復元
単一テーブルの復元はより複雑です。 mysqldump によって生成されたバックアップ ファイルを使用し、そのファイルに対象のテーブルのデータが含まれていない場合は、関連する行からデータを抽出し、mysql への入力として使用する必要があります。これは簡単な部分です。難しいのは、そのテーブルにのみ適用されるフラグメントを更新ログから取得することです。これには、変更ログから複数行のクエリを抽出する mysql_find_rows ユーティリティが役立つ場合があります。
もう 1 つの方法は、別のサーバーを使用してデータベース全体を復元し、必要なテーブル ファイルを元のデータベースにコピーすることです。これは非常に簡単です。ファイルをデータベース ディレクトリにコピーして戻すときは、元のデータベース サーバーがシャットダウンされていることを確認してください。