15

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.

stickmangumby
  • 526
  • 2
  • 5
  • 11
  • 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 07 '11 at 07:32
  • 1
    It's not a GRANT issue. You don't need to backup INFORMATION_SCHEMA (See: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html) – SmallClanger Mar 07 '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 07 '11 at 10:08

3 Answers3

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?

Shane Madden
  • 112,982
  • 12
  • 174
  • 248
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.

stickmangumby
  • 526
  • 2
  • 5
  • 11
  • It's not a GRANT issue. You don't need to backup INFORMATION_SCHEMA (See: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html) – SmallClanger Mar 07 '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 07 '11 at 10:08
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 {} \;
Vasilii Suricov
  • 101
  • 1