mysqldump: 1044 Access denied when using LOCK TABLES

本文转载自：http://www.michaelrigart.be/en/blog/mysqldump-1044-access-denied-when-using-lock-tables.html

A few days ago, I tried to make a backup using the mysqldump command and got the following error:

mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

A quick workaround is to pass the –-single-transaction option to mysqldump:

$ mysqldump –-single-transaction -u user -p DBNAME > backup.sql

Another one would be to grant LOCK TABLES to the user you are using to take the dump

$ mysql -u root -p
mysql> GRANT SELECT,LOCK TABLES ON DBNAME.* TO 'username'@'localhost';

Reading through the man pages, these pasages sheld some light on the issue at hand:

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the –single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with READ LOCAL to permit concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, –single-transaction is a much better option than –lock-tables because it does not need to lock the tables at all.

Because –lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.