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