Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 23 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

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

share|improve this answer

It seems that you need RELOAD too. So:

share|improve this answer

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

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

Your Answer


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.