ibdata1 growed almost to the size of the partition space

As a new member of the list, first of all "Hello to everybody!"
 
Some weeks ago we've installed prelude-ids on debian lenny 5 for testing purposes.
After some tests i was pleasantly surprised about the possibilities of prelude (Great work!).
 
Last week we had the following issue on our prelude-ids system:
/var/lib/mysql/ibdata1 growed almost to the size of the partition space in our environment.
Size  Used Avail Use% 
9.2G  8.4G  342M  97%
 
We were unable to delete the alerts inside the agent configuration of prewikka. An internal server error occured while trying to delete alerts or heartbeats inside prewikka.
We've used the following procudere to delete the alerts and to reduce the size of ibdata1 down to 1GB. 
 
I would like to share this information. Maybe it's usefull for somebody.
 
How did we shrink the size of ibdata1? 
 
The following procedure works for us:
 
1.Stopping of all prelude related sensors on all involved hosts and apache2/prewikka on the prelude manager host.
/etc/init.d/snort stop 
/etc/init.d/snort prelude-manager stop
/etc/init.d/snort prelude-lml stop
/etc/init.d/snort prelude-correlator stop
/etc/init.d/apache2 stop
 
2.Quick and dirty remote filesystem mount via sshfs
sshfs root@serverxyz:/tmp/remote/ /tmp/remote/
(e.g. sshfs remoteuser@remotehost:/path/to/remote_dir local_mountpoint)
 
3.preludedb-admin command to delete alle alerts before a specific date
preludedb-admin delete alert "type=mysql name=prelude user=xxxxx pass=xxxxx" --criteria "alert.create_time < yyyy-mm-dd"
 
4.During the preludedb-admin delete process we've got the following error: "The total number of locks exceeds the lock table size".
We've changed the innodb_buffer_pool_size to solve this inside /etc/mysql/my.cnf from the default value and did a restart of mysqld
innodb_buffer_pool_size = 8388608 #default value
-> should be changed to
innodb_buffer_pool_size = 83886080
 
5.Run the preludedb-admin command again
(depending on the size of the db, in our case ibdata1 was about 8GB in size, the following command runs several hours):
preludedb-admin delete alert "type=mysql name=prelude user=xxxxx pass=xxxxx" --criteria "alert.create_time <= yyyy-mm-dd"
 
6.Dump of all databases / removal of /var/lib/mysql/ibdata1 and ib_logfiles
mysqldump --verbose --user=xxxxx --password=xxxxx --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > /tmp/remote/all_serverxyz_dbs.sql
/etc/init.d/mysqld stop
rm -i /var/lib/mysql/ib*
 
7.Re-initialize of the database
mysql_install_db
/etc/init.d/mysqld start
 
8.DB dump import
mysql --user=xxxxx -p
SET FOREIGN_KEY_CHECKS=0;
SOURCE /tmp/remote/all_serverxyz_dbs.sql;
SET FOREIGN_KEY_CHECKS=1;
/etc/init.d/mysqld restart
 
9. Remote file system unmount
fusermount -u /tmp/remote/
 
10. Restart of all snort & prelude sensors and apache2.
 
Furthermore it is planned to use the following script via cron to remove all alerts which are older than 1 month via cron:
#!/bin/sh
DATE=$(date -d "last month" +"%Y-%m-%d")
echo "All prelude alerts before $DATE will be removed!"
preludedb-admin delete alert "type=mysql name=prelude user=xxxxx pass=xxxxx" --criteria "alert.create_time < $DATE"
 
It would be great if someone could verify if the above approach is correct or if we miss something important. 
Any help to optimize the prelude and mysql environment would be appreciated
_______________________________________________
Prelude-user site list
[email protected]
http://lists.prelude-ids.org/mailman/listinfo/prelude-user