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 need 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).
MariaDB [(none)]> GRANT LOCK TABLES, SELECT ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'secret-password'; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec)
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.