9

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?

6

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

3

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.