Home / server / Questions / 244072
Accepted
stickmangumby
Asked: 2011-03-07 21:59:05 +0800 CST

How do I create a read only MySQL user for backup purposes with mysqldump?

  • 772

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.

backup mysql database-backup read-only

3 Answers

  1. 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?

    • 4
  2. Best Answer

    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.

    • 1

  3. 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 {} \;
    • 0