MySQLからDumpできないんだけど、っていう問い合わせ

闇雲にdumpなんかされてもこまるんだけどさ

「DBから勝手にデータ引っこ抜いて、どこへ売りに行くんだい？」っていう野暮なツッコミもしたくなるくらい、唐突に問い合わせを受けることがあります。

第一声では、「何（手段）でデータを取り出そうとしたか？」すら言ってこない。
当然、処理のどこで止まって、どんなメッセージが出たのかも言ってこない。

で、一方的に、「権限足りてない、付けるか、権限あるユーザよこせ！」とか言ってくるわけです。

まあ、大抵はmysqldumpで、パラメータの設定ミスなんだけど。

mysqldump使わなくても、リダイレクトでも取れるんだよなあ、とか、MySQL WorkbenchのResult Gridからでも、引っこ抜けるんだよなあとか思いつつ、そっちに話を広げると、ただでさえわかっていない相手の混乱に拍車をかけるだけで得るものがないので、そこは相手に合わせながら、相手の当初の目的と試行内容をヒアリングしていくわけです。

最低限「SELECT」権限さえあればデータのDumpはできるよ

一応、最低限のセキュリティとして、権限弱めのユーザを割り当てていたりはするんだけど、ことDUMPに関しては、防ぎようがないんだよね。

今読めているテーブルの中身を、外に保存したいだけなんだから、塞ぐとしたら「読めなくする」しかない。

ガチガチにやるなら、とある特定サーバ上で動く、特定の操作しかIFを用意していないアプリ経由のみDBへの操作を許す、みたいなこともできるわけだけど。

それじゃお仕事にならんでしょ。開発なら特に。

よく引っかかるのはコレ-「Lock Tableの権限がない」

ダンプを取ろうとすると、データの読み取り一貫性が重要になるわけで、mysqldumpはデフォルトでテーブルロックを獲得する仕組みになっているようです。

ですが、一般利用者が使うユーザににLock Table権限なんて付けないです。そんなことされても困るし。

で、テーブルをロックしなくても、読み取り一貫性を担保したいなら、トランザクションをちゃんと使えと。

いや、こんなところでクドクド言わなくても、マニュアル読めば、mysqldumpの解説の「冒頭」に書いてありますし。

mysqldump では、ダンプされるテーブルに対する SELECT 権限、ダンプされるビューに対する SHOW VIEW、ダンプされるトリガーに対する TRIGGER、および --single-transaction オプションが使用されない場合には LOCK TABLES が少なくとも必要です。オプションの説明に示すように、一部のオプションではその他の権限が必要な場合があります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

というわけで、Lockせずとも、「--single-transaction」使えばOKよ、と。

なお、

大規模なテーブルをダンプするには、--single-transaction オプションを --quick オプションと組み合わせてください。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

だそうです。

--quick オプションの解説にありますが、どうやらデフォルトの挙動では、dump対象テーブルのデータを一旦全部メモリ上に展開しようとするみたいですね。

で、--quickオプションつけると、実効速度が上がるのか？って話ですが、ちょっと実験した感じでは、よくわかりませんでした。
あとでガッツリ計測してみたいとは思うものの、もし誰か検証した数字を持っている人がいたら教えて下さい。

まとめ

・MySQLサーバーからデータをdumpする手段は複数用意されている。mysqldumpだけじゃない。
・mysqldumpで失敗しているときは「Lock Table権限の不足」という理由で躓く。
・そもそもmysqldumpは「--single-transaction」で起動すればLock Table権限いらない。
・マニュアルくらい読めよ。