IBDATA1-HUGE Size Of MySQL Database

IBDATA1 is of approx 100GB consuming entire disk space of my server, it is a bug of mysql database or it is a common issue i dont know but to get out of the situation i simply followed below steps and everything back to normal with nominal initial database size.The one more way to get out of this is to truncate the tables with huge data but that doesn't reduced space occupied by this IBDATA file and hence the only way was to delete this file and generate the same by restarting the mysql server again .

Step 1).Stop Snort Server and take the Complete Backup of Snort Database (if Possible)

Step 2).If not possible, take the individual database backup of the following Databases.

archive

aw_hsc

idsevents

mysql

snort ( Except this Database) 

while taking the snort database backup, take the complete data with table scripts 

Following Tables needs to be backed up for restoring the snort database.

DETAIL

ENCODING

ICMPHDR

REFERENCE

REFERENCE_SYSTEM

SCHEMA

SENSOR

SIG_CLASS

SIG_REFERENCE

SIGNATURE

Usage: - Command to take the Individual Table Backup

mysqldump -u root -p <Database Name> <Table Name> > <FileName.sql>

e.g.

mysqldump -u root -p SNORT DETAIL > detailtable.sql

Step 3).Now stop the Database MySql Server

Step 4).Now Locate the Path of the ibdata1 file in my case the size is 100GB
D:\Program Files\MySQL\MySQL Server 5.0\data\ibdata1, Rename or Move these files ib_logfile0,ib_logfile1 and ibdata1 to some other location not accessible by MySql Database later we can delete these files.

Step 5). Now Rename the data folder of "D:\Program Files\MySQL\MySQL Server 5.0\Data" of the MySql Server.

Step 6). Create a folder named data under the location D:\Program Files\MySQL\MySQL Server 5.0\

Step 7). Copy & Paste the Existing mysql schema to this location

Step 8). Now import all Databases schemas except mysql as it is the MySQL Server's Database.

Step 9). Now Start the MySql Database it will create the ib_logfile0,ib_logfile1 and ibdata1 to its existing location with some default file size say 25MB.

Step 10). If the imported databases are not working fine then create the following Databases manually

archive

aw_hsc

idsevents

snort

Step 11). And Now Import the Dump to all the above databases one by one.

Step 12). Now for SNORT Database follow the following path.

12.1) Import the Create_Mysql.sql schema

12.2) Import the existing table data for the following tables which are taken from the existing working SNORT Database.

12.3) Now Start the Snort Server and Check whether it writes to database on not.

Step 13). Check the alerts of the table's iphdr, tcphdr, icmphdr, udphdr, If alerts/events are logged into the database it shows that your snort server database is working fine.

SCRIPTS FOR TABLE TRUNCATE

DELETE FROM DETAIL;

DELETE FROM ENCODING;

DELETE FROM ICMPHDR;

DELETE FROM REFERENCE;

DELETE FROM REFERENCE_SYSTEM;

DELETE FROM SCHEMA;

DELETE FROM SENSOR;

DELETE FROM SIG_CLASS;

DELETE FROM SIG_REFERENCE;

DELETE FROM SIGNATURE;

DELETE FROM EVENT;

OR simply import the schema for snort from the taken backup file.

Reference SQL Scripts :- Create_Mysql.sql (download link)

https://docs.google.com/leaf?id=0B56Gql-OtCzFMjdkMzVhNjQtMTgxZS00ZDk5LWE0ZmMtNmE4ZWNjNDhlYzcz&hl=en

Snort @ IDS

Today web sites are the most vulnerable, and therefore the most hacked. Snort a free and open source Network Intrusion Detection/Prevention System is the best tool for managing and preventing intrusions to your applications, and Internet-enabled programs or web sites, Snort can not only protect your sites, but it can analyze what's really going on with your networks, read more