Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

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

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

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

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

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

manipulation-1.jpg

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

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

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

  • -–single-transaction
  • --quick

の2つがあります。

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

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

例えば・・・

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

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

具体的には・・・

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

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

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

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

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

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

ですが・・・

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

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

ですので・・・

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

オプション②: --quick

また・・・

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

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

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

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

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

※追記

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

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

https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html

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

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

artistic-black-background-bloom-1.jpg

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

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

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

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

リストア(復元)方法【2種類】

assembly-1.jpg

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

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

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

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

例えば・・・

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

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

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

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

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

https://dev.mysql.com/doc/refman/5.6/ja/mysqldump-sql-format.html

--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
yaaabu51
明石高専 → 某重工で4年間ガスタービンの生産技術とか → 退職 →1年勉強(ふらふら)→ IT業界2年生
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away