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/
