rsyslog

The rocket-fast system for log processing

How to automatically delete database contents?

Rsyslog supports writing to the database. Like with log files, the rsyslogd writes the data, but does not delete (or export) it. If you need the data only for a period of time, simply deleting excess data might be a workable solution.

Below are some examples how this can be done (thanks to Michael Meckelein for posting this in the forum):

You can start a script via cron job, e. g.

mysql -u database-userid -pdatabase-password -e "truncate table SystemEvents" database-name

to delete all data or like

mysql -u database-userid -pdatabase-password -e "DELETE FROM SystemEvents WHERE ReceivedAt < date_add(current_date, interval -1 day)" database-name

to delete data older than one day.

5 thoughts on “How to automatically delete database contents?

  1. Thanks for the tip. Just a minor correction; your syntax is slightly off. Should be single quotes around the DELETE statement. Double quotes causes an error, or at least it did with mysql 5.1.

    The following worked for me.
    mysql -u database-userid -pdatabase-password -e ‘DELETE FROM SystemEvents WHERE ReceivedAt < date_add(current_date, interval -1 day)' database-name

Comments are closed.