Archival and deletion of old data in MySQL
Date: Tue, 20 Oct 2015 11:36:42 -0700 (PDT)
Message-ID: <8894ea08-ef12-44c4-8545-46a754efc263_at_googlegroups.com>
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);
Could someone please help me construct the archive script? Received on Tue Oct 20 2015 - 20:36:42 CEST