Minimum GRANTs for performing mysqldump

Minimum GRANTs for performing mysqldump

Recent World Backup Day is a good excuse to talk about MySQL database backups.

Usually it is a matter of executing the mysqldump with a bunch of parameters. Wrapping that up with a simple script will set you up for ages.

While it is tempting to pass root credentials to mysqldump it is a very bad idea. Generally use a dedicated account with minimal set of permissions.

Furthermore the backup user can have access to all databases, so dumping them at once won’t be a hassle. Creating such user is a matter of simple SQL query.

While SHOW VIEW, EVENT are not required, but having them will dump views and events in case they are present.

To further increase security pass a --defaults-extra-file parameter to the mysqldump. It specifies the path to a file that may contain login and password for a given user. With proper permissions the parameter offers better security than passing the login and password directly.

Related Posts

Leave a reply