MySQL ERROR 1142 (42000) at line 22: DROP command denied

MySQLでSQLファイルを使ってリストアする時、ユーザー権限は結構たくさん必要そうだ。

MySQLでmysqlコマンドを使ってリストアしようとしたら以下のエラーが発生したので
メモる。

$ mysql --user=testuser --password=testuserps --database testdb < test_table.sql
ERROR 1142 (42000) at line 22: DROP command denied to user 'testuser'@'localhost' for table 'test_table'

（原因）
DROPの権限がtestuserにないかららしい。

（対策）
DROPの権限を付与。

$ GRANT DROP ON testdb.* to testuser@localhost IDENTIFIED BY 'testuserps';

次々にエラーがでる。

$ mysql --user=testuser --password=testuserps --database testdb < test_table.sql
ERROR 1142 (42000) at line 25: CREATE command denied to user 'testuser'@'localhost' for table 'test_table'
$ mysql --user=testuser --password=testuserps --database testdb < test_table.sql
ERROR 1142 (42000) at line 36: ALTER command denied to user 'testuser'@'localhost' for table 'test_table'
$ mysql --user=testuser --password=testuserps --database testdb < test_table.sql
ERROR 1142 (42000) at line 37: INSERT command denied to user 'testuser'@'localhost' for table 'test_table'

test_table.sqlにCREATE, ALTER, INSERT文があったためだ。

次々に権限追加。

mysql> GRANT CREATE ON testdb.* to testuser@localhost IDENTIFIED BY 'testuserps';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALTER ON testdb.* to testuser@localhost IDENTIFIED BY 'testuserps';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT INSERT ON testdb.* to testuser@localhost IDENTIFIED BY 'testuserps';
Query OK, 0 rows affected (0.01 sec)

これでやっとリストアできた。

MySQL Got error: 1044: Access denied

mysqldumpを使うユーザーにはテーブルロック権限(LOCK TABLES)が必要だそうだ。
それを知らないでmysqldumpすると以下のエラーが発生する。

(エラー再現)

$ mysqldump -u testuser -p -t testdb test_table >test_table.sql
Enter password:mysqldump: Got error: 1044: Access denied for user 'testuser'@'localhost' to database 'testdb' when doing LOCK TABLES

エラー発生。
(原因)

mysql> select User,Lock_tables_priv from user where User='testuser';
+---------+------------------+
| User    | Lock_tables_priv |
+---------+------------------+
| testuser | N                |
+---------+------------------+
1 row in set (0.00 sec)

テーブルロックの権限がないらしい。

(対策)
回避方法：

$ mysqldump --skip-lock-tables -u testuser -p -t testdb test_table > test_table.sql
または
$ mysqldump --single-transaction -u testuser -p -t testdb test_table > test_table.sql
Enter password: 

根本対策：
テーブルロック権限がないのでLock Tables権限を与える。

mysql> GRANT LOCK TABLES ON *.* TO testuser@localhost IDENTIFIED BY 'testuserps';
Query OK, 0 rows affected (0.00 sec)
mysql> select User,Lock_tables_priv from user where User='testuser';
+----------+------------------+
| User     | Lock_tables_priv |
+----------+------------------+
| testuser | Y                |
+----------+------------------+
1 row in set (0.00 sec)

これでOK

mysqldumpの使い方

mysqldumpの使い方をまとめておく。

使い方は

$ mysqldump --help

でだいたいわかるんだがいつも使うものをメモっておく。

$ mysqldump -u testuser -p -t testdb test_table >test_table.sql
Enter password:

シェルスクリプトなどでパスワード入力したくないときは以下の通りにすればよい。

$ mysqldump --user=testuser --password=testuserps --database testdb --tables test_table > test_table.sql

ちなみにリストアは

$ mysql --user=testuser --password=testuserps --database testdb < mydb_mytbl.sql

MySQL Grantで権限を操ってみる

MySQLの権限系って常に使わないため忘れてしまうのでメモ。

現在のユーザーの権限を確認

$ mysql -u root -p
> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*F4577927EA5072D92B14A443FF94FE1F06CF4598' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

rootなんで当然ALL PRIVILEGES。

実験的にユーザーを追加。コマンドは以下のとおり。
GRANT ＜操作名＞ ON ＜データベース名＞.＜テーブル名＞ TO ＜ユーザ名＞@＜ホスト名＞ IDENTIFIED BY '＜パスワード＞';
権限はlocalhostからのinsertとdeleteにして全てのDBに権限を付与。

mysql> GRANT INSERT,SELECT ON *.* TO testuser@localhost IDENTIFIED BY 'testuserps' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

ユーザーの権限はmysql.user テーブルに格納されている。
どのようなテーブルか見てみる。

mysql> describe mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
---- まだまだあるが省略。

つまり、ユーザーの権限を見たい場合は

mysql> select  User, Select_priv, Insert_priv, Update_priv, Delete_priv from mysql.user where User='testuser';

+----------+-------------+-------------+-------------+-------------+
| User     | Select_priv | Insert_priv | Update_priv | Delete_priv |
+----------+-------------+-------------+-------------+-------------+
| testuser | Y           | Y           | N           | N           |
+----------+-------------+-------------+-------------+-------------+
1 row in set (0.00 sec)

SELECT, INSERTがYになっていることが確認できる。

以下の方法でも確認できる。

mysql> show grants for testuser@localhost;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*33EE0B1068FF0F0268FB022343BB3FA45ABE7308' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

INSERT権限を削除する。

mysql> REVOKE INSERT ON *.* FROM testuser@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> select  User, Select_priv, Insert_priv, Update_priv, Delete_priv from mysql.user where User='testuser';
mysql> FLUSH PRIVILEGES;  # 更新
+----------+-------------+-------------+-------------+-------------+
| User     | Select_priv | Insert_priv | Update_priv | Delete_priv |
+----------+-------------+-------------+-------------+-------------+
| testuser | Y           | N           | N           | N           |
+----------+-------------+-------------+-------------+-------------+
1 row in set (0.00 sec)

データが更新されているのがわかる。

MySQLの日付操作

MySQLの日付を使ったクエリーが必要になったのでメモっておく。

MySQLには基本timestamp型で日時を格納している。
しかし、今回抽出したいデータは日付のみを使ったデータ。

日時から日付だけ使ってクエリーを書くときは

現在日時を出す。
mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2012-06-09 12:56:34 |
+---------------------+
1 row in set (0.00 sec)

date関数を使って日付だけ取得する。
mysql> select date(current_timestamp);
+-------------------------+
| date(current_timestamp) |
+-------------------------+
| 2012-06-09              |
+-------------------------+
1 row in set (0.00 sec)
これは、current_dateを同じです。

日付どおしの引き算がしたい場合
DATE_SUBとto_daysを使う。

mysql> select to_days(DATE_SUB( CURRENT_DATE, INTERVAL 1 DAY )) - to_days(day_login) as days from time_table limit 1;
+------+
| days |
+------+
|   46 |
+------+
1 row in set (0.00 sec)