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?

Matt Simmons
  • 20,396
  • 10
  • 68
  • 116

5 Answers5

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.

Mike Conigliaro
  • 3,195
  • 2
  • 25
  • 24
  • Is this still valid answer in 2017? – Kyslik Dec 08 '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
  • 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: https://dba.stackexchange.com/a/273040/6235 – Buttle Butkus Oct 28 '21 at 03:43
11

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

Nathan Friend
  • 429
  • 1
  • 6
  • 12
10

For those wondering, here's the exact command:

GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';
wrangler
  • 3,080
  • 5
  • 24
  • 20
5

It seems that you need RELOAD too. So:

GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';
Kenny Rasschaert
  • 9,045
  • 3
  • 42
  • 58
Ventz Petkov
  • 51
  • 1
  • 1
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!)

Jannes
  • 200
  • 4