MySQLdump without password in cronjob

Because I recently was setting up backups for a NextCloud instance, I thought it would be a good idea to write an extra tutorial on how to do a MySQLdump without password in Cronjob.

The problem with MySQL dumps is, if you run the command without providing a password file, it will always ask you to enter your password. This would be difficult if you want to run that command in a Cronjob.

The process is fairly quick and easy, so I will guide you through each step.

Step 1 - Creating / Editing the my.cnf file

In some cases this file is already in place, if not you just create it.

sudo nano ~/.my.cnf

And now simply paste the following lines, adjusting them to hold your username and password, of course.

[mysqldump]
user = mysqluser
password = yourpassword

In case the file already exists and has data in it, just paste those 3 lines right below it.

Step 2 - Adjusting permissions

Now we need to adjust the permissions.

chmod 600 ~/.my.cnf

Step 3 - Creating the Cronjob

Now we are going to create the Cronjob. If you want to learn the basics of how Cronjobs work, check out my comprehensive tutorial on it.

To edit the Crontab file run:

crontab -e

And to perform the MySQL Dump every day at 21:00 add the following line at the bottom. To learn how times work, check out my other tutorial.

0 21 * * * mysqldump -u mysqluser -h localhost --all-databases | gzip -9  > mysqldbbackup.sql.gz > /dev/null

Step 4 (Optional) - Manually running the MySQL Dump

If you want to run the MySQL Dump directly, simply run:

mysqldump --single-transaction -h localhost -u mysqluser > /usr/share/mysqlbackups/mysqldbbackup.sql

And there you have it. This is how you run MySQLdump without password in Cronjob.