28

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?

26

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.

2
  • Is this still valid answer in 2017? – Kyslik Dec 8 '17 at 11:46
  • You don't need LOCK_TABLES permission if you aren't locking tables in the dump, for example if you use the --single-transaction flag. In fact, if you don't want the user to be able to lock tables by doing a dump, it's best to only give the SELECT permission. – Trevor Gehman Aug 17 '20 at 20:47
11

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

7

For those wondering, here's the exact command:

GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';
5

It seems that you need RELOAD too. So:

GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';
1

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!)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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