Running a MySQL Query with Cron

Use mysql -e "SQL" for a one-liner, or mysql < file.sql to run a script. Keep credentials out of the crontab by putting them in a ~/.my.cnf defaults file, which mysql reads automatically.

Cron expression (minute hour day-of-month month day-of-week)

Run an inline query

0 1 * * * /usr/bin/mysql --defaults-file=/root/.my.cnf mydb -e "DELETE FROM sessions WHERE expires < NOW()" >> /var/log/sql-cron.log 2>&1

Keep the password out of cron

Passing -p on the command line leaks the password to ps. Instead create ~/.my.cnf:

[client]
user=cronjob
password=secret

Then chmod 600 ~/.my.cnf and mysql reads it automatically.

Run a .sql file

0 1 * * * /usr/bin/mysql --defaults-file=/root/.my.cnf mydb < /opt/app/cleanup.sql

FAQ

How do I run a MySQL query from cron?

Use mysql -e: 0 1 * * * /usr/bin/mysql mydb -e "DELETE FROM sessions WHERE expires < NOW()". Add --defaults-file for credentials.

How do I avoid putting my MySQL password in the crontab?

Store it in ~/.my.cnf under [client] with chmod 600, and pass --defaults-file. mysql reads it without exposing the password to ps.

How do I run a .sql file on a schedule?

Redirect it into mysql: mysql --defaults-file=/root/.my.cnf mydb < /opt/app/cleanup.sql.