しょ〜うぃん広場

おもにTech系なブログ、ときどき個人的なブログ

任意のSQL文で mysqldump できるツールを作った

Go プログラミング Linux Mac

現状

$ mysqldump --query '任意のSQL文' > mydump.sql

のような事をしたかったのだけど、mysqldump では --where オプションで

$ mysqldump mydatabase users --where 'id IN (SELECT user_id FROM hoge WHERE huga > 10) AND id > 1000'

ぐらいのことしかできず、キーが1つの場合の JOIN を IN に書き直せるぐらいが限界になる。

別の方法としては mysqldump を使わずに

$ mysql -e "SELECT * FROM users" mydatabase --xml > mydump.xml
$ mysql --local-infile -e "LOAD XML LOCAL INFILE 'mydump.xml' INTO TABLE users;" mydatabase

とすると任意のSQL文が使えるが、XML形式でしか出力できないため大量のレコードを出力する際にはダンプのファイスサイズがかなり大きくなってしまう。中身はこんな感じ。

$ cat mydump.xml
<?xml version="1.0"?>
<resultset statement="select * from users
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="id">1</field>
    <field name="first_name">sato</field>
    <field name="last_name">huga</field>
    <field name="age">19</field>
    <field name="country">JP</field>
    <field name="created_at">2016-08-13 08:45:58</field>
  </row>

myquerydump 作った

そこで showwin/myquerydump をつくった。 これを使うと

$ myquerydump mydatabase "SELECT * FROM users" > mydump.sql
$ mysql mydatabase < mydump.sql

のように export/import できる。 ダンプファイルの中身も mysqldump と同じように SQL で書かれているのでファイルサイズも大きくならない。

$ cat mydump.sql
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES ('1','sato','huga','19','JP','2016-08-13 08:45:58'),(…
UNLOCK TABLES;

ファイルサイズを比較してみると、このようなテーブルの300万レコードで以下のようになる。

mysql> desc users;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(64) | YES  |     | NULL    |                |
| last_name  | varchar(64) | YES  |     | NULL    |                |
| age        | int(11)     | YES  | MUL | NULL    |                |
| country    | varchar(64) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
mysql> SELECT COUNT(*) FROM users;
+----------+
| count(*) |
+----------+
|  3000000 |
+----------+

$ ls -lh
104M  users.mysqldump
104M  users.myquerydump
551M  users.xml

XMLで出力した場合と比べて20%弱ぐらいのサイズになり、当然だが mysqldump と同じぐらいになる。

便利オプションの紹介

例えば

$ myquerydump mydatabase "SELECT users.name, prefectures.name FROM users INNER JOIN prefectures ON users.prefecture_id = prefecture.id ORDER BY users.name" > my.dump
$ cat my.dump
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES …
UNLOCK TABLES;

のように複数テーブルのカラムを SELECT してダンプを取る時には大抵 INSERT 先は users テーブルでなかったりするので、勝手に INSERT INTO 'users' とされてしまうと困る場合がある。 そういうときには -t オプションでテーブル名を指定すると

$ myquerydump -t user_prefecture mydatabase "SELECT …" > my.dump
$ cat my.dump
LOCK TABLES `user_prefecture` WRITE;
INSERT INTO `user_prefecture` VALUES …
UNLOCK TABLES;

となって便利。

INSERT 前にテーブルを空にしたい場合には -add-delete-table オプションを付けると DELETE FROM が追加される。

$ myquerydump -add-delete-table -t user_prefecture mydatabase "SELECT …" > my.dump
$ cat my.dump
LOCK TABLES `user_prefecture` WRITE;
DELETE FROM `user_prefecture`;
INSERT INTO `user_prefecture` VALUES …
UNLOCK TABLES;

残りの細かなオプションは README#Usage を見て頂けると良いと思う。 基本的には mysqldump と同じオプション名になるように気をつけている。

問題点

速度が遅い！！ 上の300万件ある users テーブルでこれぐらいの速度差がある。

$ mysql -e "SELECT * FROM users" mydatabase --xml > users.xml
=> 14 sec

$ myquerydump mydatabase "SELECT * FROM users" > users.dump
=> 78 sec

ファイスサイズが5倍になる mysql -e --xml を使うか、ダンプに5倍の時間がかかる myquerydump を使うかという選択をしなければいけない感じでイマイチ…

もう少し高速化して2倍ぐらいの時間でダンプが取れるようにしたい。