10

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?

1

3 Answers 3

8

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.

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

1
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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .