Antw: MySQL Database

Hi Aaron

Maybe the following instructions will help you to solve your problem. For me it works. 
Please adjust all settings to your environment.

Because your table space is quite large the preludedb-admin command may run for several days.

I've posted the following instructions to the list a while ago but i didn't get a feedback.
Their must be a better way to do the cleanup i assume.

---> 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 occurred 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 useful 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.
 

Regards,
 
Heinz

>>> "Schubert, Aaron" <[email protected]> 19.11.2010 15:41 >>>
My Prelude deployment will consist of 14 sensors and one main correlator.  I only have 5 sensors up so far and it seems like the main correlator with Prewikka is bogging down.  When I look at the resources using "top" it seems MySQL is the one working really hard.

The database is 175 gig so far and I need a way to delete everything except for the last couple of days.  I issued both:

preludedb-admin delete heartbeat "type=mysql name=prelude user=prelude pass=xxxxxx" --criteria "heartbeat.create_time < 2010-11-15"
and:
preludedb-admin delete alert "type=mysql name=prelude user=prelude pass=xxxxxx" --criteria "alert.create_time < 2010-11-15"

Heartbeats complete and return me to a prompt just fine.  With alerts, at first, I was receiving the following error:



delete event failed: The total number of locks exceeds the lock table size.

Error at transaction 1000. Use --offset 1000 to resume operation.

18446744071566262268 'delete' events processed in 42.289796 seconds (0.000000 seconds/events - 436198464373120896.000000 delete/sec average).

18446744071566262268 events processed in 42.289796 seconds (0.000000 seconds/events - 436198464373120896.000000 events/sec average).



I then had a suggestion to make the following changes to the my.cnf file and did so:

key_buffer              = 20G  (70 % of ram)

innodb_buffer_pool_size = 20G  (70 % of ram)



I ran the preludedb-admin command again for the alerts and it never returned me to a prompt for 2 days, I finally killed it and the DB was unchanged in size.  I believe it was deleting alerts as I could see numbers decreasing on some alerts in prewikka.  I did not get the error again.



Any ideas on how to trim the db?  Am I doing something wrong with preludedb-admin?  Can I truncate the tables?  Which tables are safe to truncate without losing user logon info and my sensors?




_______________________________________________
Prelude-user site list
[email protected] 
http://lists.prelude-technologies.com/mailman/listinfo/prelude-user
_______________________________________________
Prelude-user site list
[email protected]
http://lists.prelude-technologies.com/mailman/listinfo/prelude-user