Creating a backup user

It is a bad idea to use a super user like root for making backups. One main reason is that backups often run automatically, and so the password has to be stored somewhere (for example, in the my.cnf file). If the user that is being used for backups has full access to the database, it could be abused, or an error in a backup script could cause all sorts of trouble.

In this recipe, we will create a backup user with the minimum permissions necessary to run both the mysqldump and XtraBackup programs.

How to do it…

Let's get started by following the ensuing steps:

1. Launch the mysql command-line client.

2. Create the backup user. For this recipe, we'll call the user backupuser and give the user the password p455w0rd. The user can be named anything we wish, and the password should definitely be changed to something unique:

   CREATE USER 'backupuser'@'localhost'
       IDENTIFIED BY 'p455w0rd';
   

3. Next, we will grant our new user a minimal set of permissions, just enough so that it can make backups as follows:

   GRANT SELECT, SHOW VIEW, LOCK TABLES, RELOAD,
       REPLICATION CLIENT
       ON *.* TO 'backupuser'@'localhost';
   

4. Lastly, we will use the FLUSH PRIVILEGES command to force MariaDB to reread the privileges table, which is always a good idea after granting new privileges to a user.

   FLUSH PRIVILEGES;
   

How it works...

There's no need for the user we use to make backups in order to have every privilege on our databases. They only need a specific subset. For example, they don't need the INSERT or ALTER TABLE privileges since backup users just need to read the tables in our databases. The set of privileges in this recipe are enough for both the XtraBackup and mysqldump programs, and will likely be sufficient for other backup programs as well.