mysqldump – Create a Separate User to Back Up MariaDB Databases

As you may already know, mysqldump is one of the best utilities you can use to back up MariaDB databases. This tutorial shows how to back up MariaDB database in a secure manner with the help of a special backup user.

mysqldump

Most MariaDB/MySQL users are familiar with the following three backup commands.

Back up a single database

mysqldump -u root -p db_name > db_backup.sql

Back up multiple databases

mysqldump -u root -p --databases db1_name db2_name > multi_db_backup.sql

Back up all databases

mysqldump -u root -p --all-databases > all_db_backup.sql

What’s Bad about Using Root User to Do Backup?

The above 3 commands require you to enter the MariaDB root user password interactively. To automate MariaDB backup, you probably need to set up a cron job and also provide the password after the -p option like below.

0 4 * * * mysqldump -u root -proot-password db_name > db_backup.sql

Putting the MariaDB root user’s plain-text password in the crontab file or any other file is a bad idea. The MariaDB root user has all privileges of the databases. It can do anything on the databases. It’s better to create a separate user for backup only rather than using the MariaDB root user.

To create a backup user, first log into MariaDB monitor by running the following command. Enter the MariaDB root user’s password when asked.

mysql -u root -p

To back up databases, the user just needs to be able to lock tables and read data from all the databases and tables. Create the backup user and grant lock tables and select permissions to it with the following MariaDB commands. This tutorial will name this separate user “backupuser” (without quotation marks).

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'secret-password';

GRANT LOCK TABLES, SELECT, PROCESS ON *.* TO 'backupuser'@'localhost';

FLUSH PRIVILEGES;

EXIT;

Now you can set up a cron job to back up database with this user like below:

0 4 * * * mysqldump -u backupuser -pbackupuser-password db_name > db_backup.sql

Comments, questions, or suggestions are always welcome. If you think this post is useful, please share it with your friends on social media! Stay tuned for more tutorials.