Craig

I am an IT security professional with close to 20 years experience in the IT industry.

Jun 172013
 

Many open source security tools require a database back end.  Unfortunately, database administration is not something most security professionals pursue during their careers.  However, it’s important to know a few basic commands.  This post will list some of those commands, and as I come across more mysql commands, I will try to update the information below.

Drop Tables

I was initially trying to purge all data from a database, but I inadvertently used this command which didn’t do exactly what I wanted.  I think the tables were still available, just no longer associated with the database (please comment and correct me if I’m wrong).  However, there may be a scenario where I’ll need this in the future so I’m including this in my mysql command blog.

Login to the database as root:

[root@CentOS ~]$ mysql -u root -p

Drop the database:

mysql> DROP DATABASE databasename;

Re-create the database:

mysql> CREATE DATABASE databasename;

Exit the database:

mysql> quit

This does not re-create all the tables.  For that, you need the table structures.
 

Load Table Structures

After dropping the database, I need to find a way to get the table structures back.  That’s when I came across this snippet:
 
Login to the database as root:

[root@CentOS ~]$ mysql -u root -p

Identify the database to be used and source the structure:

mysql> USE databasename;
mysql> source ./doc/sql/mysql.sql;
mysql> exit

 

Additional Reading

http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/ 
https://bruteforce.gr/logging-kippo-events-using-mysql-db.html/

 

 Posted by at 22:13