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