Making a cron-scheduled database backup (dump) without exposing the password in /etc/crontab

Question

I use the following /etc/crontab code to create daily backups of my database in limit of the last 30 days:

0 8 * * * mysqldump -u root -PASSWORD --all-databases > /root/backups/mysql/db.sql
1 8 * * * zip /root/backups/mysql/db-$(date +\%F-\%T-).sql.zip /root/backups/mysql/db.sql
2 8 * * * rm /root/backups/mysql/db.sql
2 8 * * * find /root/backups/mysql/* -mtime +30 -exec rm {} \;

My problem:

I must put my password where -PASSWORD. This way my password is exposed if by mistake I entered to change something in this file and someone near to me saw it.

Is it possible to use the script without putting the password there? Alternatively, do you know a similar syntax that won't force me to write the password there for a cronjob?

Answer 1

1. Create a script to do the complete dump, backup and cleanup.

2. Schedule the script.

Additionally, the password to mysql may also be stored in a protected file and does not need to be given on the command line.

MySQL has a "End-User Guidelines for Password Security" document that you may want to consult.

To summarize that document:

Create .my.cnf in your home directory and add the password to it like this:

[client]
password=your_pass

Then remove read-permissions on the file for other users:

$ chmod 600 .my.cnf

or, equivalently,

$ chmod u=rw,go-rwx .my.cnf

This file, if named .my.cnf and placed in you home directory, will automatically be used by the mysql client program (as well as by mysqldump).

Still, do put the backup etc. into its own script and schedule that instead. That will be a whole lot easier to maintain than a number of cron jobs.