Privileges Necessary for MySQLDump

I recently setup a backup process to dump a MySQL database to a file for backup. With this database, our DBA group has been using the ‘root’ account setup the by software vendor for administration. This server is used for internal system administration and sending performance data off to our software vendor. So, other than being bad form to use the ‘root’ ID, there’s probably no regulatory responsibility to use user or role specific IDs.

That’s all well and good, but I’m not comfortable putting the ‘root’ ID password in scripts or backup products. And, I need to ensure the mysqldump command is run and completes before the backup begins, so the natural thing to do is make the backup software run mysqldump as a pre-backup job with a dedicated mysql user ID. While I’m at it, we really should give the backup user ID the minimum privileges necessary. So, first I create a user:

create user 'backup_user'@'localhost' identified by 'somepassword';

Now what privileges do we need? Here’s a list of privileges we may need:

select This is a given, without select we won’t get very far
show view We need this if we want to backup views
trigger If we have triggers to backup, we’ll need this
lock tables This is needed so mysqldump can lock the tables. Don’t need it if using –single-transaction
reload We need this if using –flush-logs
file We would need this if we were writing the files with mysqldump, and not redirecting the output to a file with ‘>’

So, we can grant these privileges to all the schemas, or just the schema’s we want to backup:

grant select, show view, trigger, lock tables, reload, file on *.* to 'backup_user'@'localhost';
flush privileges;

Leave a Reply

Your email address will not be published. Required fields are marked *