しるてく

技術的な話をします

primary key以外のデータをdumpして他のサーバに突っ込んだりしたい

MySQL

かなり特殊な事例だと思いますが、

  • 月ごとのログデータは膨大なので本番環境には過去数ヶ月しか残さない
  • 削除したデータは念のためmysqldumpしておく
  • 月ごとのデータのサマリテーブルが欲しくなったので作った
  • 過去のデータのサマリもサマリテーブルに突っ込みたい

というようなときの話。

サマリテーブルが何も作られていない場合

はすごい単純で、別の環境にdumpしたファイルを突っ込んでそこでサマリテーブルを作ったあと、

mysqldump -uroot -p database summary_table > summary_table.sql

して、本番環境で

cat summary_table.sql |  mysql -uroot -p database

とかすれば良いわけですが、

既にサマリテーブルが作られている場合

は、上のやり方だと、テーブルがdrop&createされてしまうので、困ります。
mysqldumpのオプションに、--no-create-infoとかを入れておけば、drop&createされないのですが、今度はprimary keyが衝突してしまいます。

集計するときに、primary keyを1000000ぐらいからスタートとかにすれば一応この問題はクリアできますが、かっこわるいし、集計している間に、元のサマリテーブルが1000000まで到達してしまう可能性もある。

そこで、なんとかprimary key以外だけdumpしたいなーという要望に応えてくれるのが、次に説明するSELECT .. INTO OUTFILEです。

SELECT .. INTO OUTFILEを使う

こんなテーブルだったとして

CREATE TABLE `summary_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `count` int(10) unsigned NOT NULL,
  `target_month` char(7) NOT NULL,
  PRIMARY KEY (`id`)
);

過去のデータを集計したあと、

SELECT null, name, count, target_month INTO OUTFILE '/tmp/summary_table' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM summary_table;

でファイルに書き出したあと、scpとかでファイルを持っていって、

LOAD DATA INFILE '/tmp/summary_table' INTO TABLE summary_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

で入れてあげるとできます。

こんなふうに、primary keyの部分にnullを入れると、

N,"taro","1234","2012-05"
N,"jiro","5345","2012-05"
N,"saburo","84","2012-05"

みたいなファイルができあがるので、データを突っ込むときにidをよしなにしてくれます。