Chinaunix首页 | 论坛 | 博客
  • 博客访问: 268896
  • 博文数量: 42
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 534
  • 用 户 组: 普通用户
  • 注册时间: 2012-02-26 19:11
文章分类
文章存档

2014年(42)

我的朋友

分类: 数据库开发技术

2014-06-07 14:06:44

-- =========================================================================
-- 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 -pcsfdp

Welcome 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呢?
阅读(4818) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~