31

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?

Improve this question

5 Answers 5

Reset to default
28

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.

Improve this answer
3
  • Is this still valid answer in 2017?
    – Kyslik
    Dec 8, 2017 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, 2020 at 20:47
  • For MySQL 8.0.21 and up, you also need PROCESS permission for the user, OR you need use the the --no-tablespaces option with mysqldump. See here: dba.stackexchange.com/a/273040/6235
    – Buttle Butkus
    Oct 28, 2021 at 3:43
11

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

Improve this answer
10

For those wondering, here's the exact command:

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

It seems that you need RELOAD too. So:

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

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .