mysqldump: Got error: 1044

原创 2015年11月17日 16:35:05

.版本

1)操作系统

 cat /etc/issue
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Kernel \r on an \m

 cat /proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014

2)mysql数据库版本

mysql --version
mysql  Ver 14.14 Distrib 5.6.27, for Linux (x86_64) using  EditLine wrapper


2.问题描述

  使用mysqldump 导出某张表时报如下错误:

mysqldump --default-character-set=utf8 -h127.0.0.1 -P3306 -utest -proot  shao test1 > test1.sql;
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 1044: Access denied for user 'test'@'127.0.0.1' to database 'shao' when doing LOCK TABLES
 ##从上面的报错中我们可以看出来是因为test用户没有lock tables权限,所以导出失败()

  查看test用户权限

mysql> show grants for test@127.0.0.1;
+-------------------------------------------------------------------------------------------------------------+
| Grants for test@127.0.0.1                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'127.0.0.1' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' |
| GRANT SELECT ON `shao`.`test1` TO 'test'@'127.0.0.1'                                                        |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
##'test'@'127.0.0.1'用户 只有USAGE和SLEECT权限

3.解决方案

  该问题可以通过如下方案解决:

1)给用户授予 lock tables权限

mysql> grant lock tables on shao.* to 'test'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> grant lock tables on shao.test1 to 'test'@'127.0.0.1';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
mysql> 
##在给某个表授予lock tables时会报ERROR 1144 (42000)错,授予锁定某个库下所有表的lock tables权限

2) 在mysqldump中加入--single-transaction参数

mysqldump --default-character-set=utf8 -h127.0.0.1 -P3306 -utest -proot --single-transaction shao test1 > test1.sql;

3) 在mysqldump中加入--single-transaction参数

mysqldump --default-character-set=utf8 -h127.0.0.1 -P3306 -utest -proot --skip-lock-tables shao test1 > test1.sql



##

  在使用mysqldump时,默认--lock-tables(-l)是打开的,当dump中使用--skip-lock-tables来关闭,使用--single-transaction也会自动关闭---lock-tables




版权声明:本文为博主原创文章,未经博主允许不得转载。

mysqldump: Got error: 1044

mysqldump: Got error: 1044

MySQL数据库迁移实践mysqldump,source,异常mysqldump: Got error: 1044解决方案

需求如下: 将数据库A中的表a以及a中的数据迁移到数据库B中,数据库A和数据库B部署在不同机器上 解决方案: 1.使用mysqldump从数据库A中导出表a的结构以及数据到文件a.sql ...

Mysqldump: Got error: 145错误

Mysql数据库导出时报错mysqldump : Got error : 145的解决方案 mysqldump: Got error: 145: Table './db_game_server/t_...

mysql数据库导出时报错mysqldump: Got error: 145的解决方法

在给mysql数据库备份时,报错:mysqldump: Got error: 145: Table ‘./meteo/avg’ is marked as crashed and should be r...

mysqldump执行时Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

解决办法登录mysql客户端mysql -hserverip -uroot -pmysql> update user set password=password(new password) where...

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when try

在使用mysqldump导出mysql数据库数据时,出现了如下错误:mysqldump: Got error: 1045: Access denied for user 'root'@'localho...

mysqldump: Got error: 2003: Can't connect to MySQL server on '127.0.0.1' (10061)

今天在用批处理进行mysql自动备份的过程中遇到一个问题,错误提示:mysqldump: Got error: 2003: Can't connect to MySQL server on '127....

mysql -- mysqldump错误:Got error: 1044: Access denied for user ... when using LOCK TABLES

mysql -- mysqldump错误:Got error: 1044: Access denied for user ... when using LOCK TABLES

mysqldump: Got error: 1356

mysqldump 备份遇到下述错误: mysqldump: Got error: 1356: View 'test.a_lookup' references invalid table(s) ...

mysqldump: Got error: 1146: Table 'performance_schema.cond_instances' doesn't exist when using LOCK

[root@node1bak opt]# mysqldump -uroot -p'evis123' -S /data/3306/mysql.sock  -A -B  --lock-tables=0 >...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysqldump: Got error: 1044
举报原因:
原因补充:

(最多只允许输入30个字)