Grant rights to a backup user.

Posts regarding the MySQL database server
Post Reply
Peter_w
Posts: 28
Joined: Tue Aug 19, 2008 9:28 am

Grant rights to a backup user.

Post by Peter_w »

Hello,

I want to create a backup user account with only read access,
to use with mysqldump.

what are the minimum rights I have to grant to this user?
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: Grant rights to a backup user.

Post by chris »

I think the minimum right mysql dump needs is SELECT and LOCK TABLES

Code: Select all

GRANT SELECT, LOCK TABLES  ON database.* TO ‘backup’@’hostname’ IDENTIFIED BY ‘password';
Don't forget to flush command:

Code: Select all

flush privileges;
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: Grant rights to a backup user.

Post by chris »

PROCESS privilege is new as of MySQL 5.7.31 and MySQL 8.0.21 and is also needed if you don't use --no-tablespaces.

privilege needed for mysqldump backup user:
  • SELECT privilege for dumped tables
  • SHOW VIEW for dumped views
  • TRIGGER for dumped triggers
  • LOCK TABLES if you don’t use the --single-transaction option
  • PROCESS if you don’t use the --no-tablespaces option
Post Reply