MySQLのダンプファイルから任意のテーブルのみリストアする

はじめに

mysqldumpコマンドで取得したダンプファイルから任意のテーブルのみリストアする方法を記載します。

データベース全体のバックアップはあるもののリストアしたいのは一部のテーブルのみといった場合に対応できます。

環境

• CentOS 6.4
• MySQL 5.1

ダンプファイルを分割する

cspilt コマンドを使用します。

$ csplit dumpfile '/DROP TABLE IF EXISTS/' {*}

※ gzipで圧縮されている場合

$ zcat /path/to/dumpfile | csplit - '/DROP TABLE IF EXISTS/' {*}

標準入力から読み込む場合は、対象ファイル名に 「-」 に指定します。

csplit により、「xxNNN」(NNNはゼロ埋めされた連番)のようなファイルに分割されるので、

$ grep -il 'create table `tablename`'

復元したいテーブルが含まれるファイルを特定して、

$ mysql --user=username --password=password databasename < xxNNN

特定のテーブルのみリストアすることができます。

ダンプファイルのサイズが大きい場合やメモリに余裕がない場合などは、
以下のようなエラーが出力されファイルを分割できないことがあります。

csplit: memory exhausted

ダンプファイルから必要な部分のみ抽出する

「DROP TABLE IF EXISTS XXXXX」のダンプファイル内の行数を取得します。

$ egrep -in '^drop table if exists' dumpfile

sed コマンドで特定行のみ抽出します。

$ sed -n '開始行,終了行p' dumpfile > tablename.dump

復元したいテーブルのダンプデータのみが含まれたファイルを使用して、

$ mysql --user=username --password=password database < tablename.dump

特定のテーブルのみ復元することができます。

※ gzipで圧縮されている場合

$ zegrep -in '^drop table if exists' dumpfile.gz
$ zcat dumpfile.gz | sed -n '開始行,終了行p' > tablename.dump
$ mysql --user=username --password=password databasename < tablename.dump

実際に復元してみる

今回は、mysqlデータベース全体のダンプファイルからdbテーブルのみtestデータベースに復元してみます。

mysqlデータベース全体のダンプファイルを取得します。

$ mysqldump --user=username --password=password mysql > mysql.dump

開始行と終了行を確認します。

$ egrep -in '^drop table if exists' mysql.dump
59:DROP TABLE IF EXISTS columns_priv;
87:DROP TABLE IF EXISTS db;
132:DROP TABLE IF EXISTS event;
166:DROP TABLE IF EXISTS func;
191:DROP TABLE IF EXISTS help_category;
218:DROP TABLE IF EXISTS help_keyword;
243:DROP TABLE IF EXISTS help_relation;
267:DROP TABLE IF EXISTS help_topic;
296:DROP TABLE IF EXISTS host;
337:DROP TABLE IF EXISTS ndb_binlog_index;
365:DROP TABLE IF EXISTS plugin;
388:DROP TABLE IF EXISTS proc;
429:DROP TABLE IF EXISTS procs_priv;
459:DROP TABLE IF EXISTS servers;
489:DROP TABLE IF EXISTS tables_priv;
519:DROP TABLE IF EXISTS time_zone;
542:DROP TABLE IF EXISTS time_zone_leap_second;
565:DROP TABLE IF EXISTS time_zone_name;
588:DROP TABLE IF EXISTS time_zone_transition;
612:DROP TABLE IF EXISTS time_zone_transition_type;
638:DROP TABLE IF EXISTS user;

sed コマンドでdbテーブルのみのダンプデータを抽出します。

$ sed -n '87,131p' mysql.dump > db.dump

終了行は、dbテーブルの次のeventテーブルのDROP文の行数(132)の1行前を指定しています。

testデータベースにdbテーブルを復元します。

$ mysql --user=username --password=password test < db.dump

それぞれのレコードの件数を確認しておきます。

mysql> select count(*) from mysql.db;
+----------+
| count(*) |
+----------+
| 12       |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test.db;
+----------+
| count(*) |
+----------+
| 12       |
+----------+
1 row in set (0.00 sec)

参考

• [mysqldumpのデータから一部のテーブルだけ復旧するにはl]
  (http://a9b.hatenablog.com/entry/20100729/1280394118)

• [csplitコマンド の使い方 csplit は区切り単語でファイル分割]
  (http://takuya-1st.hatenablog.jp/entry/20110823/1314108412)

• [逆引きUNIXコマンド/指定した範囲の行を取得する方法]
  (http://linux.just4fun.biz/%E9%80%86%E5%BC%95%E3%81%8DUNIX%E3%82%B3%E3%83%9E%E3%83%B3%E3%83%89/%E6%8C%87%E5%AE%9A%E3%81%97%E3%81%9F%E7%AF%84%E5%9B%B2%E3%81%AE%E8%A1%8C%E3%82%92%E5%8F%96%E5%BE%97%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95.html)