dannyman.toldme.com

MySQL: Backup User Privileges

Q: What privileges must I grant to a MySQL user to allow them to run mysqldump?

A: LOCK TABLES, SELECT seems to do the trick.

Example: GRANT LOCK TABLES, SELECT ON mydatabase.* TO 'backup'@'backuphost' IDENTIFIED BY 'somecoolpassword';

Principle of least-privilege: don’t entrust your backup host with the power to hurt the database if you don’t have to. SELECT allows the user to read data, and LOCK TABLES allows the user to lock the tables while running a “snapshot” . . . and of course, narrow the privileges to a specific user-host-password tuple.