I'm using the automysqlbackup script to dump my mysql databases, but I want to have a read-only user to do this with so that I'm not storing my root database password in a plaintext file.

I've created a user like so:

grant select, lock tables on *.* to 'username'@'localhost' identified by 'password';

When I run mysqldump (either through automysqlbackup or directly) I get the following warning:

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

Am I doing it wrong? Do I need additional grants for my readonly user? Or can only root lock the information_schema table? What's going on?

Edit:

GAH and now it works. I may not have run FLUSH PRIVILEGES previously.

As an aside, how often does this occur automatically?

Edit:

No, it doesn't work. Running mysqldump -u username -p --all-databases > dump.sql manually doesn't generate an error, but doesn't dump information_schema. automysqlbackup does raise an error.

  • Oops... from the man page for mysqldump: mysqldump does not dump the INFORMATION_SCHEMA database. If you name that database explicitly on the command line, mysqldump silently ignores it Seems like either the man page is out of date (and it does raise a warning), or automysqlbackup is performing some additional checks on the dump for information_schema. Not sure which it is, but it's not related to user grants. – stickmangumby Mar 7 '11 at 7:32
  • 1
    It's not a GRANT issue. You don't need to backup INFORMATION_SCHEMA (See: dev.mysql.com/doc/refman/5.0/en/information-schema.html) – SmallClanger Mar 7 '11 at 10:02
  • 1
    To add to what SmallClanger said, the INFORMATION_SCHEMA is a virtual database, rebuilt each time MySQL is restarted, so there's no point in backing it up because you can't restore it anyway. – John Gardeniers Mar 7 '11 at 10:08

Those permissions should be all that's needed for the mysqldump.

Since you've granted LOCK TABLES, and it's erroring on LOCK TABLES, seems like the permissions are inconsistent. Have you run a FLUSH PRIVILEGES?

up vote 1 down vote accepted

Oops... from the man page for mysqldump:

mysqldump does not dump the INFORMATION_SCHEMA database. If you name that database explicitly on the command line, mysqldump silently ignores it

Seems like either the man page is out of date (and it does raise a warning), or automysqlbackup is performing some additional checks on the dump for information_schema.

Not sure which it is, but it's not related to user grants.

Edit

Yep, it's a bug in automysqlbackup version 2.5.1 (using MySQL 5.1.41 under Ubuntu 10.04) - it tries to backup information_schema when it shouldn't.

FIX: Add information_schema to to DBEXCLUDE on line 76 of the script.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.