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.

Read More

Categories: Technical

Discover more from dannyman.toldme.com

Subscribe now to keep reading and get access to the full archive.

Continue reading