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
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:
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.