― Web Technology and Life ―

開発者として覚えておきたいmysqldumpのオレオレ実践Tips

2013-08-23
mysqldumpってバックアップ使うあれでしょっていう感じですが、突然入ったプロジェクトや、有償/無償のシステムを持ってきたときなど、DBのスキーマ構造やマスターデータの管理に使うと意外と開発者でも役立つなと思いましたので、その内容をば。

逆引き実践Tips

とりあえず、釣りっぽいタイトルを「オレオレ」でごまかすの術から始まっていますが、使えねーよって人がいたら無視してくださいw

で、まずは実践的なワンライナー的なコードから。

対象のサービスのDB構造の一覧がほしいよ!

mysqldump --compact -d -uroot mydbname | egrep -v "(^SET|^/\*\!)" | perl -ple 's/CREATE TABLE /\nCREATE TABLE /g' > ./schema.sql

mysqlオプションは以下にまとめて解説を載せますが、それ以外のshellのTipsとしては以下です。

  • mydbnameでデータベースを指定
  • 「egrep~」で残ったコメントアウトも対象外に
  • 「perl~」でテーブル毎に一行あけて見やすく

これを使えば、オートインクリメントなども適宜削除してしまえますね。

現在のマスターデータがほしいよ!

開発者がDBにいれるならINSERT分とか作ると思うんですが、マスター的なデータを管理画面から追加できちゃってディレクターの方がごにょごにょすると、本番反映時に困ったくんなので、マスターデータ抽出のために

  • データだけで
  • コメントなしで
  • マスターデータ以外のテーブルのデータ

がほしいと思うので、そのTips。

none_master_table1・・・3以外がマスターテーブルという場合

mysqldump 
    --ignore-table=eccbe_db.none_master_table1 
    --ignore-table=eccbe_db.none_master_table2 
    --ignore-table=eccbe_db.none_master_table3 
    --compact 
    --skip-tz-utc
    -t 
    -uroot 
    mydbname | egrep -v "(^SET|^/\*\!)" | perl -ple 's/CREATE TABLE /\nCREATE TABLE /g' > ./masterdata.sql

master_table1,master_table2がマスターテーブルという場合

mysqldump 
    --compact 
    --skip-tz-utc 
    -t 
    -uroot 
    mydbname master_table1 master_table2 | egrep -v "(^SET|^/\*\!)" | perl -ple 's/CREATE TABLE /\nCREATE TABLE /g' > ./masterdata.sql

--ignore-tableオプションまとめて指定したいな・・・と思うのですがここがもやっとです。。。

あと、ややっこしいのですが、「--skip-tz-utc」については注意が必要なので以下をよく読んでください。

特定のテーブルの特定のデータだけをdumpしたい

--whereオブションを使いますが、注意が必要です。

普通の場合

mysqldump -uroot mydbname "--where=sex='male'" user_table 

省略形の場合

mysqldump -uroot mydbname "-wsex='male'" user_table

オプションをクォーテーションで囲むのがキモイですね。。。

dumpしたファイルからテーブ名を抜き出したい

これはmysqldumpじゃないんですが、--ignore-table使ってブラックリスト方式でマスターデータ管理しているのだけど、マスターテーブル名のリストがほしいときに便利です。


grep -o "INSERT INTO .+ VALUES" masterdata.sql | perl -ple 's/INSERT INTO `(.+)` VALUES/$1/'

運用時はスクリプトに保存

とりあえず、マスターテーブルとか増えたりするし、いろいろ事情変わるし、そもそも?オプションは忘れるものなので、これらのようなスクリプトをget_schema.shとかにして保存しておいて毎回それを実行するようにするといい気がします。

オプション別の詳細解説

オプションの説明は以下の通りです。

オプション名 内容
-t データのみ取得する
-d schemaのみ取得する
--compact 不要なコメントなどを減らす(※以下にさらに詳細)
--ignore-table 特定のテーブルを除く
--skip-tz-utc 異なるタイムゾーン間で扱う用のSETオプションをなしに(※以下に詳細)
--tz-utc 異なるタイムゾーン間で扱う用のSETオプションをつける(デフォルト有効)
--where 特定のテーブルに対するwhere指定、クオーテーションをオプションまるっと囲む点に注意

【追記:20130827】-tオプションと-dオプションの説明が逆になっていました。@jpug_studyさんにご指摘いただきました、ありがとうございます。

さらに、注意/説明が必要な内容は以下の通りです。

特定のDBや特定のテーブルを指定したいとき

素直に以下のように後ろにつければいいので簡単ですね。

mysqldump -uroot mydbname table1 table2 

コメント系を減らす「--compact」の詳細

optオプションのようにいろんなオプションをまとめたオプションで実際には、次のものと同じ。

  • --skip-add-drop-table
  • --skip-add-locks
  • --skip-comments
  • --skip-disable-keys
  • --skip-set-charset

タイムゾーンにまつわる問題 「--tz-utc」「--skip-tz-utc」の詳細

個人的にはこれが本題ですねw

全然知らなかったんですが、普通に「mysqldump -uroot > ./data.sql」ってすると、実際DB上は以下のようなデータなんですが、

+-----------+-----------+---------------------+---------------------+
| id        | name      | create_date         | update_date         |
+-----------+-----------+---------------------+---------------------+
|         1 | hiro      | 2013-04-11 03:07:37 | 2013-04-11 03:07:37 |
|         2 | bane      | 2013-04-11 03:09:48 | 2013-06-19 02:38:48 |
|         3 | hoge      | 2013-06-21 17:38:35 | 2013-06-21 17:41:19 |
|         4 | moge      | 2013-06-21 17:39:20 | 2013-06-21 17:39:20 |
|         5 | fuga      | 2013-06-21 17:39:55 | 2013-06-21 17:41:13 |
+-----------+-----------+---------------------+---------------------+

dumpファイル以下のようになっていて

INSERT INTO `user` VALUES 
    (1,'hiro','2013-04-10 18:07:37','2013-04-10 18:07:37'),
    (2,'bane','2013-04-10 18:09:48','2013-06-18 17:38:48'),
    (3,'hoge','2013-06-21 08:38:35','2013-06-21 08:41:19'),
    (4,'moge','2013-06-21 08:39:20','2013-06-21 08:39:20'),
    (5,'fuga','2013-06-21 08:39:55','2013-06-21 08:41:13');

時間ずれるんですね!!

「なんじゃこりゃ!!!」って感じなんですが、実はこれはデフォルトでは「--tz-utc」というオプションが有効になっていて、これは「異なるタイムゾーン間でのダンプデータの共有を可能にする」もので、「mysqldump -uroot > ./data.sql」したファイルの最初のほうをよくよく見るとわかりますが、以下の構文をいれるのです。

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;

ダンプファイルからリストアするときは、この構文がINSERT構文の前に入るので、時間を調整してから入れるので結果的に同じになるのであります。

最初から混乱しないようにタイムゾーンをまたがないサービスを運営している場合は、--skip-tz-utcという対応もありだなーと思って、ぼくはskipしているんですが、今更ですが、逆にskipしないほうがわかりやすいので、スキーマのdumpは--compactしてもいいですが、master_dataとかは--compactしないほうがいいいですね(笑

mysql以外のハック

perlワンライナー

ぶっちゃっけぼくPerlしか使えないんですが、ワンライナーよくわかっていません。毎度オプション忘れて使う気なくしますw なのでここで今回載せた分はメモっておきます。

  • -e : perlのワンライナーの実行オプション
  • -p : ワンライナーを標準入力のループ処理内の処理として扱う
  • -l : 標準入力を改行してデータをワンライナーにわたす(pオプションなどと組み合わせて使う)

未だにループ処理系ワンライナーで書くとうまく動かないので、結局ファイルにかいて、読み込んじゃったりするんですよねー。早くかっこいいワンライナー使いになりたいっす。。。

egrep,grep -o

egrepは正規表現使えるgrepで、grepの-oはgrepで正規表現が使えるようになるやつです。要はどっちも同じですね。-vは、マッチしないよう行を返すってやつですね。うーん、趣深い。grepの範囲ならPerlのワンライナーより個人的に使いやすいっす(苦笑

終わりに

「あれ、そもそもbackup用のmysqldumpってどう使うの???」ってニーズもありそうな気がしつつ、自分でも忘れそうなので、運用向けのmysqldumpのTipsとHowTOはそれでまとめておきたいと思いますが、また今後かなーw

あと、全然関係ないけど、最近、phpenvとか使おうと思っているのだけど、環境によってよくわからんけど、5.5のコンパイルに失敗してやる気なくしたり、xdebugのvimバージョンいれようとしたんだけど、動かないのばかりで、最終的にはvimの使い方がわかりづらく挫折みたいな失敗してなかったので、若干マイナイスモード(苦笑

あと、これは関係あるけど、moznion氏の職質テックトーク#02 (@songmu)聞いていたら、今回の記事の関連のネタをsongmuさんがYAPC::Asia2013で今時のカジュアルなデータベース関連開発と題して話すらしいので、とても楽しみです。

まぁとにかく、それでは、毎度、こんなオプション便利だよ!とか、こんなのあるよっ!これ認識間違いだよ!などありましたら、どしどしご指摘お待ちしておりますー!!

MySQL Perl update_at : 2013-08-27T11:52:52
hirobanex.netの更新情報の取得
 RSSリーダーで購読する   
blog comments powered by Disqus