Setting up a MySQL backup user

 I came across a situation where I wanted to backup specific databases using mysqldump and didn't want to use the existing user on the database extended with the privilege PROCESS.

Lets recap on setting up a user, database and how to grant them privileges on a database / all databases.

CREATE DATABASE my_database;

CREATE USER my_database_user;

GRANT ALL PRIVILEGES ON my_database.* TO my_database_user;

For simplicity here I have excluded the 'username' @ '%' usernames which you can replace the username with as required. Also note that I have deliberately not set a password. This is again, for simplicity.

Now into the reason for this post, how to setup a backup user with the minimum required privileges for backing up database tables.

So, we have a user and a database but not set the privileges yet (first 2 statements from above).

The privileges I found to be required for a standard backup are the following:

• LOCK_TABLES
• SELECT
• PROCESS

LOCK_TABLES is so that the user can take a snapshot of a table

SELECT is so that the user can access the data of a table

PROCESS is so that mysqldump can be run under that users scope

Putting it all together we end up with a GRANT statement like this:

GRANT PROCESS, SELECT, LOCK_TABLES ON my_database.* TO my_database_user;