MySQL Database Exporting/Importing

we can use Perl scripts to automate the MySQL database in connection with the mysqldump utility specially for large databases.
mysqldump -u UserName -pPassword DBName | gzip > DBName.sql.gz
Once the DatabaseName.sql.gz is completed the same file can be imported to the new created database but gz file needs to be decompressed first it can be done as shown below.
gzip -d DatabaseName.sql.gz


Database Restoring Steps.
Create new Database
Connect to the newly created database
Import the decompressed/extracted file DatabaseName.sql using below option
Create database NewDBName
Connect NewDBName
source D:\DatabaseName.sql


This will import the entire sql dump file to this new database.

MySQL Database Optimization

This is specially tested with Snort IDS Database

Select your database and then issue the command below, where 'mytablename' is the name of the table you want to query
show table status like 'mytablename'\G
You can omit the "like 'mytablename'" part and then it will show this information for all tables.
However if you have a lot of tables and there's only one or two you want to examine then it's better to specify the particular table.
You can end you query with either ; or \G. I prefer \G for this particular query because it shows each column from the resultset on a new line,
whereas ; will show the columns across the screen. This is OK for a resultset with only a few columns with only a
small amount of information in each one, but it's not so good for this query.

The result from the above will look something like so:

*************************** 1. row ***************************

Name: mytablename

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 2444

Avg_row_length: 7536

Data_length: 564614700

Max_data_length: 281474976710655

Index_length: 7218176

Data_free: 546194608

Auto_increment: 1187455

Create_time: 2008-03-19 10:33:13

Update_time: 2008-09-02 22:18:15

Check_time: 2008-08-27 23:07:48

Collation: latin1_swedish_ci

Checksum: NULL

Create_options: pack_keys=0

Comment:

***************************************************************

The values that are important for working out if the table is non optimal is the "Data_free" value.
If this is high, as in the above example where 564614700 bytes are free (538MB),
then the table has a lot of space not being used and should be optimized.
To optimize the table, issue the following command, where "mytablename" is the name of the MySQL table to optimise:

optimize table mytablename;

After doing this (it may take a few seconds dpending on the size of the table, free space etc) and running "show table status" again, the result should look much better:
*************************** 1. row ***************************
Name: tblmailqueue
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 6145
Avg_row_length: 7505
Data_length: 46119636
Max_data_length: 281474976710655
Index_length: 296960
Data_free: 0
Auto_increment: 1191156
Create_time: 2008-03-19 10:33:13
Update_time: 2008-09-02 22:24:58
Check_time: 2008-09-02 22:21:32
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: pack_keys=0
Comment:
1 row in set (0.00 sec)
***************************************************************
In the above example we can see the "Data_free" value is now zero so the table is nicely optimised.

You can do something like this:
SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";") FROM tables WHERE DATA_FREE > 0 INTO OUTFILE '/tmp/optimize.sql'; SOURCE '/tmp/optimize.sql';

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