Grant rights to a backup user.

[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]

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]

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