Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

2 Answers

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?

share|improve this answer
add comment
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.

share|improve this answer
    
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
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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