mysql: Access denied to information_schema when using LOCK TABLES

We’re backing up all our mysql databases with a script similar to this script. But we do backup also the information_schema database.

When doing so we got the following error:

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

Note: on a system with Plesk, the admin user is just like the root user. If not using Plesk, you might also get the following message:

access denied for user 'root'@'localhost' to database 'information_schema'

The problem is basically that this user doesn’t have the right to LOCK TABLES.

So one solution is to grant this user the required rights:

mysql> GRANT SELECT,LOCK TABLES ON information_schema.* TO 'admin'@'localhost';

Another solution is to use the –skip-lock-tables option which will perform the dump without using LOCK TABLES (but tables may be dumped in completely different states):

#mysqldump -u admin -h localhost --skip-lock-tables -pxxxxxx information_schema

Or you can also use the –single-transaction so that a START TRANSACTION SQL statement is executed at the beginning and a consistent state is achieved without LOCK TABLES:

#mysqldump -u admin -h localhost --single-transaction -pxxxxxx information_schema

Leave a Reply

Your email address will not be published. Required fields are marked *