Re: Archival and deletion of old data in MySQL

On 10/20/2015 2:36 PM, dpich_at_realtruck.com wrote:
> I am running OSSEC. It's a very handy tool for sysadmins to watch activity on the servers in their network.
>
> My problem is this. When the MySQL database gets large, the front end UI gets bogged down because of the large amount of data. If I flush the data, the UI responds better.
>
> So I need to accomplish the following:
> 1. Move anything that is 31 days or older from the database 'ossec' to the database 'archive'.
> 2. Anything older that 365 days in 'archive', delete it forever.
>
> There are a ton of examples online on how to do it table by table. I need the entire database.
>
> For part 2, I have some preliminary bash scripting built:
>
> #!/bin/bash
> DELETE2TIME=`/bin/date -d "365 days ago" "+%Y-%m-%d %H:%M:%S"`
>
> getvaluefromossec()
> {
> /bin/grep "<$1>" /var/ossec/etc/ossec.conf | /bin/sed "s|^.*<$1>\(.*\)</$1>|\1|g"
> }
>
> MYSQLHOST=`getvaluefromossec "hostname"`
> MYSQLDB=`getvaluefromossec "database"`
> MYSQLUSER=`getvaluefromossec "username"`
> MYSQLPASS=`getvaluefromossec "password"`
>
> echo "
> SET _at_delete2time=\"$DELETE2TIME\";
> DELETE FROM alert WHERE timestamp < UNIX_TIMESTAMP(_at_delete2time);
> DELETE FROM data WHERE timestamp < _at_delete2time;
> " | mysql -h$MYSQLHOST -u$MYSQLUSER -p$MYSQLPASS -D$MYSQLDB
>
>
> Could someone please help me construct the archive script?
>

You can only access data in the database via the tables (or views, which are dependent on tables). You can't access everything in the database itself, which is why you only found out how to do it with the tables.

But rather than go through all that work, just use the MySQL functions, i.e.

DELETE FROM alert WHERE timestamp < CURRENT_DATE() - INTERVAL 365 DAY;

This takes the current date and subtracts 365 days (you might want to use 1 YEAR instead to account for leap years).

Since you will have multiple SQL statements which are dependent on each other, you should put everything in a transaction. See START TRANSACTION, COMMIT and ROLLBACK for more information. You will need to use the INNODB engine for transactions to function properly.

Hope this helps a bit.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================