MySQLデータベースを5つの異なる方法でバックアップする方法は？

データベースに保存されるデータが増加するにつれて、データのバックアップはますます重要になっています。データベースのバックアップは、企業や個人がデータ損失により甚大な被害を受けることのないよう保証するための非常に重要な作業です。MySQLは人気のあるオープンソースのリレーショナルデータベース管理システムであり、企業で広く使用されています。本記事では、MySQLデータベースのバックアップ方法について説明します。

MySQLデータベースバックアップの種類

データベースのサイズによって、次の4種類のバックアップ方法があり、それぞれ異なる用途に使用されます：

完全バックアップ

これは多くの人が使用する一般的な方法です。ユーザー テーブル、システム テーブル、インデックス、ビュー、ストアド プロシージャなど、すべてのデータベース オブジェクトを含む、データベース全体をバックアップできます。ただし、より多くの時間とスペースを必要とするため、通常は週に一度の完全バックアップが推奨されます。

トランザクション ログ バックアップ

トランザクションログはデータベースへの変更を記録する個別のファイルです。バックアップでは、前回のバックアップ以降にデータベースに加えられた変更のみをコピーする必要があるため、非常に短時間で完了します。データベースの堅牢性を高めるために、時間単位またはそれより頻繁なトランザクションログのバックアップが推奨されます。

差分バックアップ

これはデータベースの一部のみをバックアップする別の方法です。トランザクションログは使用せず、代わりにデータベース全体の新しいイメージを使用します。差分バックアップは、最後のフルバックアップ以降に変更されたデータベースのみを含むため、元のフルバックアップより小さくなります。高速な保存および復元が可能なのが利点です。毎日差分バックアップを行うことをお勧めします。

ファイルのバックアップ

データベースはハードディスク上の多数のファイルで構成される場合があります。データベースが非常に大規模で一晩でバックアップを取ることができない場合、ファイル単位のバックアップを使用して、毎晩データベースの一部をバックアップすることができます。ただし、データベースが一般に複数のファイルストアを使用するほど大規模ではないため、このようなタイプのバックアップはあまり一般的ではありません。

データベースの状態によって、以下の3つのタイプに分けることができます:

1. コールドバックアップ

コールドバックアップとは、データベースがオフラインでユーザーがアクセスできない状態のときにデータに対して実行されるバックアップです。バックアップ処理がオフライン状態で行われるため、コールドバックアップはオフラインバックアップと呼ばれることもあります。コールドバックアップはリソース消費が少なく、新しいデータを追加することができない（データベースがオフラインであるため）ため、すべてのデータを一度でバックアップできます。もちろんコールドバックアップにも制限があり、バックアップ処理中はデータベースにアクセスできないという点が挙げられます。

2. ホットバックアップ

ホットバックアップとは、データベースがオンラインで利用可能になっている状態でデータに対して実行されるバックアップのことです。ホットバックアップは、ダウンタイムを必要とせず、OSの運用をほとんど妨げないため、非常に便利なソリューションであることが多いですが、一方でいくつかの欠点もあります。バックアップ作業の最中にバックアップ対象のデータが何らかの形で変更された場合、そのバックアップはデータベース内のデータの最終版と同じにならない可能性があります。

3. 論理的バックアップ

論理的バックアップはソフトウェアを使用してデータベースからデータを抽出し、その結果をファイルに書き込みます。

MySQLデータベースをバックアップする方法は？

バックアップ復元方法に応じて、MySQLには論理バックアップと物理バックアップがあります。論理バックアップとは、SQLステートメントをバックアップし、復元時にそれらを実行してデータベースのデータを再現する方法です。物理バックアップは、データベースファイルをディスクに保存されているのと同じ形式でコピーします。

これらの2種類のバックアップにはそれぞれ長所と短所があります。一般的に、物理バックアップは復元速度が速いですが、より多くのスペースを占有します。一方、論理バックアップは速度が遅いですが、より少ないスペースしか使用しません。

1. mysqldumpの使用

mysqldumpはMySQLに付属しているバックアップツールで、binディレクトリにあります：/usr/local/mysql/bin/mysqldump。これはinnoDBに基づくホットバックアップをサポートしています。ただし、論理バックアップであるため速度はあまり速くなく、バックアップデータが比較的少ないケースに適しています。mysqldumpによるフルバックアップとバイナリログを使用すれば、ポイントインタイムリカバリが可能です。

MyISAMストレージエンジンのテーブルの場合は、ホットバックアップのみを使用できます。このとき、データ書き込みを防止する必要があるため、まず読み取り専用ロックを追加する必要があります。このようにして、データベースに入って手動で読み取り専用ロックをかけることも可能ですが、手間がかかります。その代わりに、mysqldumpツールで直接ロックをかけるオプション--lock-all-tablesを使用すれば、例えば、mysqldump --databases test --lock-all-tables --flush-logs > /tmp/backup_test_`date +%F-%H-%M`.sql のように実行すれば、簡単に処理ができます。

単一のテーブルをバックアップする場合は、ライブラリ名testの後にテーブル名を追加してください。

InnoDBストレージエンジンのテーブルの場合、データベースをロックすることなくホットバックアップを実行できます。ホットバックアップを実行するためのオプション「--single-transaction」を追加します。例: mysqldump --databases test --single-transaction --flush-logs --master-data=2> /tmp/backup_test_`date +%F-%H-%M`.sql。

P.S. リストア時にバイナリログを閉じることを忘れないでください:

mysql> set sql_log_bin=0;

これは論理的なバックアップ方式に基づいているため、SQLを実行してデータを挿入し、バイナリログにも記録されます。これは復元処理であるため、挿入されるバイナリログをオフにすることで復元時間を短縮できます。

MySQLデータベースのバックアップと復元（Mysqldump使用／未使用） の詳細はこちらをクリックして確認してください。

2. LVMスナップショットに基づくバックアップ

物理バックアップに関しては、ファイルシステム（LVMスナップショット）に基づく物理バックアップがあり、tarなどのコマンドを使ってデータベースディレクトリ全体をパッケージングしてバックアップすることもできますが、これらはクoldバックアップでのみ使用できます。ストレージエンジンによってバックアップする内容が異なります。MyISAMは自動的にテーブルレベルまでバックアップしますが、InnoDBは個別テーブルスペースを有効にしていない場合、データベース全体しかバックアップできません。

LVMのスナップショット機能を使用する方法の紹介です。

1) 安全のため、まずデータベースに読み取りロックをかけます

mysql>FLUSH TABLES WITH READ LOCK

2) 特定時点復元のためにバイナリログをリフレッシュする

3) スナップショットボリュームを作成します

4) リードロックを解除するためのデータベースに入力します

5) バックアップのためにスナップショットボリュームをマウントする

6) 最後に、/mnt/snap 下のファイルをパッケージ化してバックアップする

復元用:

1) MySQLを終了する

2) 次にバイナリログをバックアップする

3) 元のバックアップファイルを復元する

4) その後、バイナリログを通じてエラーが発生した時点に復元します（バイナリログを利用して時刻の復元を行う際は、一時的にバイナリログを閉じることを忘れないでください）

2010年から2012年にかけて、MySQLデータベースがAmazonのクラウド環境に導入されました。LVMスナップショットが提供されており、これは非常に便利で高速です。LVMスナップショットを使用すると、Amazonの10Gネットワーク下でリカバリーも非常に高速になります。

3. Tar バックアップ

データベースからの最初のデータバックアップの準備を行い、すべてのテーブルを一時的にロックし、ウィンドウ1を開きます

mysql> テーブルを読み取りロックでフラッシュする;

クエリ OK、0 行が影響されました (0.00 秒)

mysql> show master status;

+++++
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |  
| mysql-bin.000003 | 194554 | | |  
1 row in set (0.00 sec)

このウィンドウは終了できません。tarパッケージの作成が完了するまで開いたままにする必要があります。

--Re-open a shell window, go to the data file directory tar package
open window 2
[root@myfstv_21_11 data]# tar -zcvf mysqla1.tar.gz mysqla
tar -zcvf mysqla1.tar.gz mysqla Compressed
Switch to window 1 and execute the unlock command

mysql> テーブルのロックを解除;