Bienvenido! - Willkommen! - Welcome!

Bitácora Técnica de Tux&Cía., Santa Cruz de la Sierra, BO
Bitácora Central: Tux&Cía.
Bitácora de Información Avanzada: Tux&Cía.-Información
May the source be with you!

Tuesday, December 7, 2010

To empty MySQL database


Q. How do I empty MySQL database? What SQL command needs to be executed in order to delete all (100s) of tables in MySQL database called atomstore?

A. You need to use (with the client mysql) the
sql command to drops all tables in the database/empty database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database
MySQL drop all tables syntax:DROP DATABASE {mysql-database-name}

Method #1: Empty database with root user
In order to use this procedure you must have the drop and create database privilege (otherwise you will drop database but not able to create it again). Login as MySQL root or admin user to drop atomstore database:
$ mysql -u root -p
Now drop database:
mysql> DROP DATABASE atomstore;
Now create database again:
mysql> CREATE DATABASE atomstore;
Exit and close the session:
mysql> quit

Method #2: Drop all tables using shell script w/o root access
I've small handy shell script that removes all tables without dropping and creating MySQL database again.
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
if [ $# -ne 3 ]
 echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
 echo "Drops all tables from a MySQL"
 exit 1
TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
for t in $TABLES
 echo "Deleting $t table from $MDB database..."
 $MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
Simply use above script as follows to empty a database called quiz with username tom and password jerry:
$ ./ tom jerry quiz

Download MySQL empty database drop script

You can download complete script with error checking below:

No comments: