Daily MySQL Backup with Cron
To dump a MySQL database every night at 2 AM, run mysqldump from cron and write to a dated file. Store credentials in ~/.my.cnf so the password never appears in the crontab or process list.
The crontab line
0 2 * * * /usr/bin/mysqldump mydb | /bin/gzip > /backups/mydb-$(date +\%F).sql.gz
Note the escaped \%: in crontab a bare % is turned into a newline, so every percent sign in a date format must be backslash-escaped.
Hide the password
Never put -pSECRET in the line — it shows in ps. Put credentials in ~/.my.cnf:
[client]
user=backup
password=secret
then chmod 600 ~/.my.cnf. mysqldump reads it automatically.
Prune old backups
Add a second job: 30 2 * * * /usr/bin/find /backups -name '*.sql.gz' -mtime +14 -delete to keep two weeks.
FAQ
What is the cron line for a daily MySQL backup?
0 2 * * * /usr/bin/mysqldump mydb | gzip > /backups/mydb-$(date +\%F).sql.gz runs a compressed dump at 2 AM daily. Escape every % as \% in crontab.
How do I avoid putting the MySQL password in cron?
Store user and password in ~/.my.cnf under a [client] section, chmod 600 it, and mysqldump reads the credentials automatically — nothing sensitive appears in the crontab or ps output.
Why does my mysqldump date filename break in cron?
Cron treats an unescaped % as a newline. Write $(date +\%F) with a backslash before each % so the date command receives its format string intact.