仕事で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} <strong>--lock-tables=false</strong> {DATABASE} > dump.sql

ユーザに権限を付与する

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

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