mysqldumpの使い方 バックアップ&リストア編 基本の章


20140731_01

こんにちわ。システムエンジニアのリョータです。

前回は、オプションをずらーっと並べて紹介しました。

mysqldumpの使い方 オプション編

mysqldumpのオプションが多すぎて、どこをどうすればいいのかわかりません。
ということで、今回はもうすこし実用的な形で進めたいと思います。

今回は、テーブルやデータベースをバックアップ&リストアをする、基本的な方法をまとめてみました!

テーブル

まずは、テーブルをバックアップ&リストアする方法です。

1.テーブルのダンプ

mysqldump -uroot -p -hlocalhost database_name table_name > filename

テーブルの定義とデータをダンプする方法です。
テーブルは、1つだけでなく複数指定することもできます。

mysqldump -uroot -p -hlocalhost database_name table_name1 [table_name2...] > filename

テーブル定義のみダンプしたい場合は、no-data(d)オプションを使います。

mysqldump -uroot -p -hlocalhost -d database_name table_name > filename

逆にデータのみダンプしたい場合は、no-create-info(t)オプションを使います。

mysqldump -uroot -p -hlocalhost -t database_name table_name > filename

さらに、データの中でも条件をつけてダンプしたい場合は、where(w)オプションを使います。

mysqldump -uroot -p -hlocalhost -w="id = 1" database_name table_name > filename

条件を複数指定する場合は、この通り。

mysqldump -uroot -p -hlocalhost -w="id >= 100 AND flag = 1" database_name table_name > filename

コマンドラインの変数を使って、条件を指定することもできます。
指定した日付以降のデータを取得する場合はこの通り。

Linux

mysqldump -uroot -p -hlocalhost -w="date_column >= DATE('${ymd}')" database_name table_name > filename

Windows

mysqldump -uroot -p -hlocalhost -w="date_column >= DATE('%ymd%')" database_name table_name > filename

※変数ymdには、mysqlのDATE関数が解析できる形式の日付が入ってるとします。

whereオプションの条件は、全てのテーブルを対象にしたり複数テーブル指定したりした場合、対象テーブル全てに適用されます。
全てに共通したカラムがある場合は利用できるかもしれません。

ちなみに、このwhereオプション、単純にSELECT文のWHERE句、WHEREの後ろに文字列を結合しているだけらしく、EXISTSを使ったサブクエリやORDER BY句、はたまたGROUP BY句まで記述できます。
※EXISTSでサブクエリを使った場合、「サブクエリのテーブルがLOCKされていない」となってエラーになりますが、そもそもロックしないようにskip-lock-tablesオプションを付けてダンプするとエラーが発生しません。single-transactionオプションと組み合わせれば、利用できそうです。




2.テーブルのリストア

mysql -uroot -p -hlocalhost target_database < filename

続いて、テーブルのリストアです。
先程とったダンプからデーブルをリストアします。

リストアではmysqlコマンドを使用します。
mysqldumpで作成したファイルには、SQL文が出力されていますので、mysqlで実行できるのです。

MySQLにログインして実行することもできます。
私は、手動でリストアする場合、手順やデータベース内容を確認しながら対応できるので、以下のようにやっています。

mysql -uroot -p -hlocalhost

USE target_database
SOURCE filename

tablesオプションを指定した場合、または、databases(B)やall-databases(A)オプションを指定しない場合には、USE文が出力されないので、必ず対象のデータベースを指定します。

データベース

次に、データベースのバックアップ&リストアです。

1.データベースのダンプ

mysqldump -uroot -p -hlocalhost -B database_name1 [database_name2 ...] > filename

databases(B)オプションを使うと、複数のデータベースをダンプすることができます。
データベース内の全てのテーブルをダンプします。

全データベースをダンプしたいときは、all-databases(A)オプションを使います。

mysqldump -uroot -p -hlocalhost -A > filename

all-databases(A)オプションは、databases(B)オプションで全てのデータベースを指定したのと同じ、と公式のリファレンスに記載がありますので、動作も同じようになっていると思われます。

データベース定義とテーブル定義のみダンプしたい場合は、no-data(d)オプションを使います。

mysqldump -uroot -p -hlocalhost -A -d > filename

データベース定義も不要な場合(テーブル定義のみ)は、no-create-db(n)オプションを使います。

mysqldump -uroot -p -hlocalhost -A -d -n > filename

逆にデータベース定義のみダンプしたい場合は、さらにno-create-info(t)オプションを使います。

mysqldump -uroot -p -hlocalhost -A -d -t > filename

テーブル定義とデータのダンプは、no-create-db(n)オプションを使います。

mysqldump -uroot -p -hlocalhost -A -n > filename

そして、データのみのダンプは、さらにno-create-info(t)オプションを使います。

mysqldump -uroot -p -hlocalhost -A -n -t > filename

2.データベースのリストア

mysql -uroot -p -hlocalhost < filename

データベースのリストアの場合、リストア先のデータベースはダンプ元と同じデータベースが作成されるので、データベースを指定する必要はありません。

注意したいのは、既に同じ名前のデータベースがある場合は、CREATE DATABASE文がスキップされ、テーブルをDROP&CREATEしてしまいます。

便利なオプション

1.トランザクション

mysqldump -uroot -p -hlocalhost --single-transaction database_name > filename

single-transactionオプションで、トランザクションを開始してからダンプをします。

トランザクションを開始することで、テーブルをロックせずにデータを取得でき、他のトランザクションからのコミットでファジーリード・ファントムリードが発生するのを防ぎます。(MySQLはデフォルトでトランザクションレベルがREPEATABLE-READですが、ファントムリードも防いでくれます)
トランザクションが有効なInnoDBをダンプするのに便利です。

注意する点は、MyISAMやMEMORYなどトランザクションが無効なデータベースには効果がないこと、ダンプ中にALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE文などのDDLが他のトランザクションから実行されると読み取り一貫性が崩れてしまうことです。

lock-tables(l)オプションとは互いに排他で、自動的にオフにします。

2.コメントの出力をやめる

mysqldump -uroot -p -hlocalhost --skip-comments database_name > filename

commentsオプションはデフォルトで有効になっているので、mysqldumpのバージョン、サーバのMySQLのバージョン、DB名やテーブル名、出力日時などのコメントが出力されていると思います。
skip-commentsオプションを使用すると、これらコメントが出力されなくなります。
データバックアップだけでリストアする時も中身を見ないからコメントはいらない場合に使いましょう。

さらに「バージョンやDB名、テーブル名は残しておきたいけど、出力日時はいらない!」というわがままなあなたにはこちら。

mysqldump -uroot -p -hlocalhost --skip-dump-date database_name > filename

skip-dump-dateオプションで、出力日時だけ出力しないようにできます。
これで、ファイルの差分を取ったときに出力日時で引っかからずにすみます。

出力日時はファイル名やファイルの作成日時でわかればいい。むしろ、どこが変わったかファイルの差分を掛けて知りたい。という人におすすめです。

3.特定のテーブルのバックアップをしない

mysqldump -uroot -p -hlocalhost database_name --ignore-table=database_name.table_name > filename

ignore-tableオプションを使用すると、指定したテーブルをダンプしなくなります。
テーブルの指定を行う際、データベース名から記述しなければいけないので、気をつけましょう。

このオプションは何回も使えるので、ダンプしないテーブルを複数指定できます。

mysqldump -uroot -p -hlocalhost database_name --ignore-table=database_name.table_name1 --ignore-table=database_name.table_name2 > filename

バックアップを取得する際、リファレンスなど毎回は変わらないデータを除外しておくのに役立つと思います。

4.MySQL独自のCREATE TABLEオプションを削除する

mysqldump -uroot -p -hlocalhost database_name --skip-create-options > filename

create-options(a)オプションはデフォルトで有効になっているので、AUTO_INCREMENTやENGINEなどのMySQL独自のテーブルオプションが出力されます。
他のDBMSに移行する際には、このオプションによってエラーが発生するため、邪魔になってしまいます。
テーブルオプションを削除したい場合は、skip-create-optionsオプションを使って出力しないようにできます。

なお、オプションを無効化するskip~には、オプションの省略形は使えません。

5.データの文字コードを変更する

mysqldump -uroot -p -hlocalhost database_name --default-character-set utf8 > filename

default-character-setオプションで、テーブルデータの文字コードを変更することができます。
注意したいのは、データの文字コードは変わりますが、テーブル定義は変わらないことです。
いくらデータを文字コード変換して出力しても、テーブルの文字コードに変換されてリストアされます。
データベースで保持するデータの文字コードを変換するには、先んじてのテーブル定義の変更など、工夫が必要になります。

6.出力先

mysqldump -uroot -p -hlocalhost database_name -r filename

さて、私一押しのオプションの紹介がきました。

result-file(r)オプションで、ファイルの出力先を指定できます。
今までは、標準出力に出力されたデータを直接ファイル出力していました。

Windowsでは、標準出力をコマンドプロンプトでファイルへ出力すると、改行が\n(newline)から\r\n(carriage returnとnewline)に変換されてしまいます。
それを防ぐためにこちらのオプションが使えます。

ルート配下のbackupディレクトリに出力するならこんな感じになります。

Linux

mysqldump -uroot -p -hlocalhost database_name -r /backup/filename.sql

Windows(Cドライブ)

mysqldump -uroot -p -hlocalhost database_name -r C:\backup\filename.sql

verbose(v)オプションを併用すると、ファイルを出力中にダンプの開始終了が標準出力(画面)に出力されます。
verbose(v)オプションだけですと、ファイル出力されて経過が見れないのですが、result-file(r)オプションと併用することで、有用なオプションになってくれます。

mysqldump -uroot -p -hlocalhost database_name -v -r filename

大きなデータベースをダンプしながら、経過が確認できて便利です。

最後に

今回は、バックアップとリストアの基本的な方法をまとめてみました。
役に立ちそうなオプションの使い方は見つかりましたでしょうか?

オプションを組み合わせて、適したバックアップをみつけてみましょう。

次回は、ダンプ時にデータベースとテーブルの作成のDDLとデータを分割して出力するオプションの使い方をまとめたいと思います。

株式会社ライズウィル

株式会社ライズウィル

〒110-0016
東京都台東区台東1-27-11 やわらぎビル2F
TEL : 03-4590-3200
FAX : 03-4590-3201
E-Mail : info@risewill.co.jp
URL : http://www.risewill.co.jp

海上コンテナ輸送管理システム「海コン魂!」
海コン魂!
面接予約・スタッフ管理システム「WebWORKS.」
WebWORKS.