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

(on a system with Plesk, the admin user is just like the root user).

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

mysql: Backup all databases in separate files

In order to backup all databases on a mysql server at once, you can use the following command:

# mysqldump --all-databases -u xxx --password="xxx" | gzip > full.backup.sql.gz

This will create an sql file with a dump of all databases and compress it. It's nice, fast and easy. But it's then not so easy to work with this one single file (especially if only one database needs to be restored).

But this can also be done with a one-liner (though a long one...):

# echo 'show databases' | mysql -u xxx --password="xxx" --skip-column-names | grep -v information_schema | xargs -I {} -t bash -c 'mysqldump -u xxx --password="xxx" {} | gzip > /backup/mysqldump-$(hostname)-{}-$(date +%Y-%m-%d-%H.%M.%S).sql.gz'

This first gets all databases in the local mysql server, dumps each of them and stores them compressed.