30

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?

5 Answers 5

27

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.

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. 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 Oct 28, 2021 at 3:43
11

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

10

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.