Deleting old records from DB using crontab

I have a table in my MySql DB called "user_action". I want开发者_如何转开发 to delete old records from this table that are older then 30 days (according to the "action_date" datetime column) on a daily basis and I want to do it using crontab.

Any Thoughts?

Create /usr/local/bin/clear-out-old-records.sh and make it executable:

#!/bin/bash
SQL="DELETE FROM user_action WHERE action_date < DATE_SUB(CURDATE(),INTERVAL 30 DAY)"

MYSQL_USER="<your-user>"
MYSQL_PASS="<your-password>"
MYSQL_DB="<your-db>"

echo $SQL | /usr/bin/mysql --user=$MYSQL_USER --password=$MYSQL_PASS $MYSQL_DB

...and then put /usr/local/bin/clear-out-old-records.sh into crontab.

For authentication, create a .my.cnf file in the user's home directory:

[client]
user=username
password=password

Then create a file called crontab. I strongly recommend that you don't edit contab directly, but always through this one file; otherwise it's too easy to accidentally overwrite the contents of crontab.

MAILTO="email@foo.com"

# 1. minute (0-59)
# |   2. hour (0-23)
# |   |   3. day of month (1-31)
# |   |   |   4. month (1-12)
# |   |   |   |   5. day of week (0-7: 0 or 7 is Sun, or use names)
# |   |   |   |   |   6. commandline
# |   |   |   |   |   |
#min hr  dom mon dow command
  0   3   *   *   *  mysql DATABASE -e "DELETE FROM user_action WHERE action_date < subdate(now(), interval 30 day)" >/dev/null

Replace DATABASE with the name of your database.

This means that the cleanup will run at 3:00 AM every night (every day of month, every day of week, etc). Errors will be sent to your email. >/dev/null is necessary to hide MySQL's non-error output, which you're usually not interested in.

And run this command to update the system's copy of crontab:

crontab ./crontab

Do a script in your favorite language that executes the following query:

Pseudocode:

"DELETE FROM user_action WHERE created_at < #{(Date.now - 30.days)}" 

Then add this to cron to execute daily.

Write a SQL (preferable stored procedure) to do that and execute that SQL via crontab.