mysqldump error LOCK TABLES

Question

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?

Answer 1

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

Answer 2

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.