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.

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

#shorts Backup a PostgreSQL Databas...
#shorts Backup a PostgreSQL Database - Ansible module postgresql_db

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.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share via
Copy link
Powered by Social Snap