Home / server / Questions / 178266
Accepted
tom
Asked: 2010-09-06 06:51:35 +0800 CST

mysqldump error LOCK TABLES

  • 772

When I execute a mysqldump I get following error:

mysqldump -u root -ppassword --databases information_schema > test.sql
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

How can I fix this?

mysql database

3 Answers

  1. Best Answer

    I had to add --single-transaction

    According to the MySQL manual this option guarantees only a consistent backup if you are using InnoDB or BDB. In contrast to MyISAM and Heap they support transactions.

    • 8

  2. If you must dump the information_schema database then add --skip-lock-tables to it.

    mysqldump does not dump the INFORMATION_SCHEMA database by default. As of MySQL 5.1.38, mysqldump dumps INFORMATION_SCHEMA if you name it explicitly on the command line, although currently you must also use the --skip-lock-tables option. Before 5.1.38, mysqldump silently ignores INFORMATION_SCHEMA even if you name it explicitly on the command line.

    http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

    • 6

  3. Got same problem yesterday.

    The database was migrated from other server and when we deleled few users, there was VIEW which had nonexistent DEFINER and that caused the problem.

    It doesn't show propper error when it was dumped with user grant SELECT, LOCK TABLES, but under root user correct error was shown.

    • 0