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?
GAH and now it works. I may not have run FLUSH PRIVILEGES previously.
As an aside, how often does this occur automatically?
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.