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;