mysqldumpで出力したsqlを実行するときにはLOCK TABLESオプションに注意する

仕事でMySQLを触る時間が増えてくると、いろいろ知らなかったことやハマりが出てくる。いろいろ勉強になって楽しいなぁと思いつつ、今週引っかかったネタをひとつご紹介。

概要

mysqldumpコマンドでダンプしたSQLは、デフォルトオプションではテーブルロックが付与されている。INSERTを高速にする効用があるが、投入するユーザに「LOCK TABLES」の権限がないと失敗する。

% mysql -u{username} -p{password} {database} < dump.sql
error: 1044: Access denied for user '{username}'@'localhost' to database '{database}' when using LOCK TABLES

詳細と対策

ダンプ時に対策するか、ユーザに権限を付与することで投入できるようになる。今動いてるデータベースなのでロックが必要という(恐ろしい)局面以外は、mysqldumpのオプションで対策するのがよいだろう。

ダンプ時にLOCK TABLESを出力しない

mysqldumpコマンドでダンプしたSQLには、デフォルトでLOCK TABLES句が追加されている。

LOCK TABLES `hoge_table` WRITE;
INSERT INTO `hoge_table` VALUES ....
UNLOCK TABLES;

オプション「–lock-tables」のdefaultがtrueなので、LOCK TABLESを出力したくないときはfalseを明示する。

% mysqldump -u{username} -p{password} -h{host} --lock-tables=false {DATABASE} > dump.sql
ユーザに権限を付与する

ユーザごとの権限に「LOCK TABLES」というのが存在する。普通に作った一般ユーザではoffになっているので、管理コマンドで権限を付与する。

mysql> grant LOCK TABLES on {database}.* to '{username}'@'localhost' ;

Links

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.1.3 GRANT 構文
http://dev.mysql.com/doc/refman/5.1/ja/grant.html

MySQL :: MySQL 5.1 リファレンスマニュアル :: 7.12 mysqldump — データベースバックアッププログラム
http://dev.mysql.com/doc/refman/5.1/ja/mysqldump.html

Leave a comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です