Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I want to start scheduling remote mysqldump crons, and I'd prefer to use a special account for that purposes. I want to grant that user the minimum permissions for getting a full dump, but I'm not sure the best way to go about that.

Is it as simple as

grant SELECT on *.* to '$username'@'backuphost' identified by 'password';

or am I missing a better way?

share|improve this question
add comment

5 Answers

up vote 19 down vote accepted

I believe the user just needs select permissions on the tables to be backed up.

Edit: This guy says to assign the "lock tables" permission too, which makes sense.

share|improve this answer
    
Terrific! Just what I was looking for. Thank you! –  Matt Simmons Jun 9 '09 at 16:22
add comment

Also you need SHOW_VIEW, if there are views in your DB.

share|improve this answer
add comment

It seems that you need RELOAD too. So:

GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';
share|improve this answer
add comment

For those wondering, here's the exact command:

GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';
share|improve this answer
add comment

And you need EVENT if you want to dump EVENTs too.

And you need TRIGGER if you want to dump TRIGGERs too. (despite what the manual says!)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.