To setup mysqldump without password in Cronjob, follow the steps below:-
- Start your terminal and login to your server
- Create a .my.cnf file in the home directory
vi ~/.my.cnf
- Copy the content below and replace with your mysql username and password.
[mysqldump] user = mysqluser password = secret
- Now change the file permission to 600 to prevent other user from reading it
chmod 600 ~/.my.cnf
- Now you can try to execute mysqldump and system will not prompt you for password by now
- To setup the cronjob
crontab -e
Append the line below to your crontab and it will run mysqldump every night at 12:00am
0 0 * * * mysqldump -u mysqluser -h localhost --all-databases | gzip -9 > alldb.sql.gz > /dev/null 0 0 * * * mysqldump -u mysqluser -h localhost --all-databases | gzip -9 > alldb.sql.gz 0 0 * * * mysqldump -u mysqluser -h localhost --all-databases | gzip -9 > alldb.sql.gz 0 0 * * * mysqldump -u mysqluser -h localhost --all-databases > alldb.sql