14

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
4

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?

| improve this answer | |
1

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.

| improve this answer | |
0

Create user

GRANT USAGE ON *.* TO 'dump'@'localhost' IDENTIFIED BY 'plaintext-pass';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `my-db`.* TO 'dump'@'localhost';

Check privileges

mysql> SHOW GRANTS FOR dump@'localhost';
+-----------------------------------------------------------------------------------------------+
| Grants for dump@localhost                                                                     |
+-----------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dump'@'localhost'                                                      |
| GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `my-db`.* TO 'dump'@'localhost' |
+-----------------------------------------------------------------------------------------------+

Use yor favorite editor to create file ~/.my.cnf with chmod 400

[client]
user=dump
password=plaintext-pass

Create folder for dumps, just as example

mkdir ~/db-dumps

Check if it works

mysqldump -u dump --hex-blob --routines --triggers my-db | gzip > ~/db-dumps/manual-my-db-dump-`date +%F`.sql.gz

Optionally you can make dumps daily and weekly then remove all daily older then month

#m h  dom mon dow   command
0  3  *   *   0,2-6 /usr/bin/mysqldump -u dump --hex-blob --routines --triggers my-db | gzip > ~/db-dumps/daily-my-db-dump-`date +%F`.sql.gz;
0  3  *   *   1     /usr/bin/mysqldump -u dump --hex-blob --routines --triggers my-db | gzip > ~/db-dumps/weekly-my-db-dump-`date +%F`.sql.gz;
0  4  *   *   *     /usr/bin/find ~/db-dumps/ -name "daily-*" -type f -mtime +30 -exec rm -f {} \;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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