How do I create a read only MySQL user for backup purposes with mysqldump?
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
?
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.