【MySQL】mysqldumpコマンドまとめ｜バックアップ・リストア

この記事では、《 mysqldumpコマンド 》について、
業務を通して学習した内容をまとめています。

• データベースのバックアップ方法
• データベースのリストア（復元）方法

こういった内容についてまとめています。

※本記事は、自分で学習したことのまとめ用として書いています。
尚、解説で誤った点があれば、スローして頂ければ喜んでキャッチしますのでお願い致します。

【基礎】押さえておきたい知識

『MySQL』のバックアップ及びデータの復元には、mysqldumpコマンドを利用します。

mysqldumpコマンドは、MySQLをインストールすると使えるようになるコマンドで、コマンドプロンプトやターミナルなどから実行できます。

mysqldumpコマンドを使用する前に押さえておきたいコマンドオプションに…

• -–single-transaction
• --quick

の２つがあります。

オプション①： –-single-transaction

–-single-transactionオプションは、トランザクションを利用してバックアップを取得します。

例えば・・・

バックアップの対象となるデータベースのデータ量が膨大で、バックアップに３０分以上かかるとします。

そこで発生するのが、整合性の問題です。

具体的には・・・

常時運用しているデータベースでは…

dumpを開始したタイミング と dumpを終了したタイミング

でデータベースの内容に相違がある可能性があります。

これを防ぐために、例えば…

dumpを行っている間データベースをロックし、変更を許さない状態にする

という手もありますが…
常時運用しているデータベースでは、dumpの間データベースが使用不可の状態になるため使えません。

ですが・・・

–-single-transactionオプションは、トランザクションを利用してくれるので…

dumpを開始した時点のデータと、dumpが終了した時点のデータの整合性を保証できます。

ですので・・・

–-single-transactionオプションは、つけておくのがベストです。

オプション②： --quick

また・・・

–-single-transactionと併用して使いたいのが、--quickオプションです。

データ量が膨大なデータベースをダンプする場合、メモリーへのバッファリングが問題になる場合があります。

--quickオプションを利用すると・・・

テーブルの全レコードをメモリにバッファせず、１行ずつダンプを行うので…

メモリを圧迫せずに、ダンプを行うことができます。

※追記

--quickオプションは、デフォルトで『有効』になっているため、使用する必要はありません。

↓↓↓以下、公式リファレンスより

テーブルを 1 行ずつダンプする場合、--quick オプションを使用してください (または --opt を指定すれば --quick が有効になります)。--opt オプションは (したがって --quick も) デフォルトで有効なため、メモリーへのバッファリングを有効にするには、--skip-quick を使用します。

バックアップ方法【３種類】

この記事では、３通りのバックアップ方法について解説します。

1.  『１つ』のデータベースをバックアップ方法
2.  『複数』のデータベースをバックアップ
3.  『全て』のデータベースをバックアップ

『１つ』のデータベースをバックアップ

mysqldump --single-transaction -u ユーザー名 -p データベース名 > 出力先のファイル

例えば・・・

• ユーザー名： root
• データベース名： sample_db
• 出力先のファイル： /bak/mysqldump_sample.dump

という内容でバックアップしたい場合は、下記のようになります。

mysqldump --single-transaction -u root -p sample_db > /bak/mysqldump_sample.dump

『複数』のデータベースをバックアップ

mysqldump --single-transaction -u root -p -B データベース名 データベース名…> 出力先のファイル

例えば・・・

• ユーザー名： root
• データベース名： sample1_db、sample2_db、sample3_db
• 出力先のファイル： /bak/mysqldump_sample.dump

という内容でバックアップしたい場合は、下記のようになります。

mysqldump --single-transaction -u root -p -B sample1_db、sample2_db、sample3_db > /bak/mysqldump_sample.dump

『全て』のデータベースをバックアップ

mysqldump --single-transaction -u ユーザー名 -p -A > 出力先のファイル

例えば・・・

• ユーザー名： root
• 出力先のファイル： /bak/mysqldump_sample.dump

という内容でバックアップしたい場合は、下記のようになります。

mysqldump --single-transaction -u root -p -A > /bak/mysqldump_sample.dump

リストア（復元）方法【２種類】

続いて、２通りのリストアの方法について解説します。

1.  『１つ』のデータベースをリストアする方法
2.  『全て』のデータベースをリストアする方法

『１つ』のデータベースをリストアする方法

mysql -u ユーザー名 -p データベース名 < dumpファイル名

例えば・・・

• ユーザー名： root
• データベース名： sample_db
• dumpファイル： /bak/mysqldump_sample.dump

という内容でバックアップしたい場合は、下記のようになります。

mysql -u root -p sample_db < /bak/mysqldump_sample.dump

※尚、データの投入先となるデータベースは、先に作成しておく必要があります！

↓↓↓以下、公式リファレンスより

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

つまり・・・

--databasesオプションを使用して作成したdumpファイルには、データベース名がsampleの場合、以下の記述が含まれています。

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sample_db`;

USE `sample_db`;

そのため・・・

データベースを作成する必要はありません。

しかし・・・

--databasesオプションを使用せずにdumpファイルを作成した場合は、上記の記述がないため…

予めデータの投入先となるデータベースを作成しておく必要があります。

『全て』のデータベースをリストアする方法

mysql -u ユーザー名 -p < dumpファイル名

例えば・・・

• ユーザー名： root
• dumpファイル： /bak/mysqldump_sample.dump

という内容でバックアップしたい場合は、下記のようになります。

mysql -u root -p < /bak/mysqldump_sample.dump