-- =========================================================================
-- mysqldump: Got error: 1045: Access denied for user when using LOCK TABLES
-- =========================================================================
[root@csfdpdev ~]# mysqldump -ucsfdp -pcsfdp sync_cdp > sync_cdp_26_20110915_2.sql
mysqldump: Got error: 1045: Access denied for user [email=]
'csfdp'@'%'[/email]
(using password: YES) when using LOCK TABLES验证用户账号权限:
一:Shell下用这个账户登录,是有lock tables权限的。
[root@csfdpdev ~]#
mysql -ucsfdp -pcsfdpWelcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2228960
Server version: 5.5.8-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> lock table stock read;
ERROR 1046 (3D000): No database selected
mysql> use sync_cdp;
Database changed
mysql> lock table stock read;
Query OK, 0 rows affected (0.00 sec)
mysql>
二:
show grants for [email=]
'csfdp'@'%'[/email]
; GRANT RELOAD, LOCK TABLES ON *.* TO [email=]'csfdp'@'%'[/email] IDENTIFIED BY PASSWORD '*B13D3DA1A8037763ED485FDFD0C966C22114971E'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW ON `fdp`.* TO
[email=]'csfdp'@'%'[/email]
GRANT ALL PRIVILEGES ON `cdp`.* TO [email=]'csfdp'@'%'[/email]
GRANT ALL PRIVILEGES ON `mdp`.* TO [email=]'csfdp'@'%'[/email]
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW ON `test`.* TO
[email=]'csfdp'@'%'[/email]
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW ON `um`.* TO
[email=]'csfdp'@'%'[/email]
GRANT SELECT ON `reuters_interface`.* TO [email=]'csfdp'@'%'[/email]
GRANT SELECT ON `reuters_dss`.* TO [email=]'csfdp'@'%'[/email]
GRANT SELECT ON `reuters_rkd`.* TO [email=]'csfdp'@'%'[/email]
GRANT ALL PRIVILEGES ON `sync_fdp`.* TO [email=]'csfdp'@'%'[/email]
GRANT ALL PRIVILEGES ON `sync_cdp`.* TO [email=]'csfdp'@'%'[/email]
GRANT ALL PRIVILEGES ON `web_cdp`.* TO [email=]'csfdp'@'%'[/email]
GRANT SELECT, LOCK TABLES, SHOW VIEW ON `web_cam`.* TO [email=]'csfdp'@'%'[/email]
GRANT SELECT, LOCK TABLES, SHOW VIEW ON `web_fdp`.* TO [email=]'csfdp'@'%'[/email]
两种方法都表明[email=csfdp@%]csfdp@%[/email]账号有lock tables的权限啊,而且密码也是csfdp,为什么用mysqldump的时候还会报lock tables的错误呢?
google了很多资料以及在qq群里面众多朋友的协助,最后加了一个参数--single-transaction才算dump成功了。
[root@mysql var]#
mysqldump -ucsfdp -pcsfdp --single-transaction sync_cdp > sync_cdp_26_20110915.sql
[root@mysql var]#
用mysqldump --help 查看了下这个参数的英文资料:
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
大概就是这个参数就是独占mysql资源,在mysqldump的时候,lock了全部的表,别的用户线程不能执行ddl操作了。但是能不能做dml操
作呢,没有说明,估计是可以吧。
一直没有搞明白为什么需要加--single-transaction参数才能mysqldump呢?