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.


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';



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.

Rate this tutorial
[Total: 5 Average: 4]

3 Responses to “mysqldump – Create a Separate User to Back Up MariaDB Databases

  • how about for restore user? What privileges are required for restoring the data from the sqldump file?

  • Christian-W. Budde
    3 years ago

    It might be worth noting that (at least on MySQL) the GRANT in combination with IDENTIFIED BY is deprecated and not supported anymore with version 8.0 and later. It’s probably better to split the creation of a user and granting the rights into two different commands like:

    CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'secret-password';
    GRANT LOCK TABLES, SELECT ON *.* TO 'backupuser'@'localhost';
  • You also need the SHOW VIEW privilege in case you are using views in your databases

Leave a Comment

  • Comments with links are moderated by admin before published.
  • Your email address will not be published.
  • Use <pre> ... </pre> HTML tag to quote the output from your terminal/console.
  • Please use the community ( for questions unrelated to this article.
  • I don't have time to answer every question. Making a donation would incentivize me to spend more time answering questions.

The maximum upload file size: 2 MB. You can upload: image. Links to YouTube, Facebook, Twitter and other services inserted in the comment text will be automatically embedded. Drop file here