If you want to give a user permissions to back up a MySQL database, what do you do? The MySQL GRANT page didn’t tell me. Searching wasn’t helpful.
Here’s the answer, in the hopes it will help others: SELECT and LOCK TABLES.
mysql> grant select, lock tables on foo.* to bar@localhost indentified by 'ugh';
mysql> flush privileges;
Isn’t this wot StackOverflow is for?
@Tony, you need to run “GRANT SELECT, INSERT, UPDATE ON *.* TO helpful_source@* identified by ‘helpful_info’;” to fix your problem.
@Paul, thanks. I couldn’t remember. BTW, your site was #6 in Google for “mysqldump permissions”. Don’t know where StackOverflow was; but it didn’t make the top 50.
Very helpful, didn’t want to continue using my own admin account in our sh backup script anymore, but for anyone else interested, you should also give a user the “SHOW DATABASES” perm so the user can do all at once.
If you are using the –single-transaction and –master-data options, you need some additional global permissions:
grant reload, super, replication client on *.* to bar@localhost identified by 'ugh';
For this:
/usr/bin/mysqldump –opt –master-data=2 –no-data –databases foo –user=backup –password=bar > $BACKUP_DIR/$SCHEMA_FILE
I needed this:
GRANT SELECT, RELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘backup’@’localhost’ IDENTIFIED BY PASSWORD ‘…’