MySQL Drop All Tables Shell Script Utility

by Vivek Gite on November 13, 2008

#!/bin/bash
# A shell script to delete / drop all tables from MySQL database.
# Usage: ./script user password dbnane
# Usage: ./script user password dbnane server-ip
# Usage: ./script user password dbnane mysql.nixcraft.in
# -------------------------------------------------------------------------
# Copyright (c) 2008 nixCraft project <http://www.cyberciti.biz/fb/>
# This script is licensed under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# ----------------------------------------------------------------------
# See URL for more info:
# http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/
# ---------------------------------------------------
MUSER="$1"
MPASS="$2"
MDB="$3"
MHOST="localhost"
[ "$4" != "" ] && MHOST="$4"
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
# help
if [ ! $# -ge 3 ]
then
	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} [host-name]"
	echo "Drops all tables from a MySQL"
	exit 1
fi
# make sure we can connect to server
$MYSQL -u $MUSER -p$MPASS -h $MHOST -e "use $MDB"  &>/dev/null
if [ $? -ne 0 ]
then
	echo "Error - Cannot connect to mysql server using given username, password or database does not exits!"
	exit 2
fi
TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
# make sure tables exits
if [ "$TABLES" == "" ]
then
	echo "Error - No table found in $MDB database!"
	exit 3
fi
# let us do it
for t in $TABLES
do
	echo "Deleting $t table from $MDB database..."
	$MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e "drop table $t"
done

#!/bin/bash

# A shell script to delete / drop all tables from MySQL database.

# Usage: ./script user password dbnane

# Usage: ./script user password dbnane server-ip

# Usage: ./script user password dbnane mysql.nixcraft.in

# -------------------------------------------------------------------------

# This script is licensed under GNU GPL version 2.0 or above

# -------------------------------------------------------------------------

# This script is part of nixCraft shell script collection (NSSC)

# Visit http://bash.cyberciti.biz/ for more information.

# ----------------------------------------------------------------------

# See URL for more info:

# http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/

# ---------------------------------------------------

MUSER="$1"

MPASS="$2"

MDB="$3"

MHOST="localhost"

[ "$4" != "" ] && MHOST="$4"

# Detect paths

MYSQL=$(which mysql)

AWK=$(which awk)

GREP=$(which grep)

# help

if [ ! $# -ge 3 ]

then

echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} [host-name]"

echo "Drops all tables from a MySQL"

exit 1

# make sure we can connect to server

$MYSQL -u $MUSER -p$MPASS -h $MHOST -e "use $MDB" &>/dev/null

if [ $? -ne 0 ]

then

echo "Error - Cannot connect to mysql server using given username, password or database does not exits!"

exit 2

TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

# make sure tables exits

if [ "$TABLES" == "" ]

then

echo "Error - No table found in $MDB database!"

exit 3

# let us do it

for t in $TABLES

echo "Deleting $t table from $MDB database..."

$MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e "drop table $t"

done

Tagged as: awk command, database usage, delete database mysql, drop all tables mysql, empty database, empty mysql database, for loop, if command, mysql delete all tables, mysql delete database, mysql drop all tables, mysql empty, mysql empty database

{ 7 comments… add one }

fitorec August 31, 2011, 1:45 pm
good script, add internal mysql options:
```
TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -BNe 'show tables' )
```
- – B: Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file.
- -N: Do not write column names in results.
Reply Link
Matthew Setter November 10, 2010, 10:16 am
thanks for the script, works nicely.
Matt
Reply Link
jexxer November 30, 2009, 3:14 pm
I have one problem with this script.
It does not take foreign keys into account. It will try to delete tables that have dependencies to other tables and this will throw a error.
I your script would build a dependencies tree and delete top-down it would be perfect ;)
Reply Link
moxxom July 6, 2009, 6:00 am
Good answer , straight but polite and smart
Reply Link
- antonio May 28, 2011, 12:40 am
  hhahaha very good
  Reply Link
moxnox January 21, 2009, 2:59 pm
Because one might not have the right to create a database.
Reply Link
Dallas Marlow November 19, 2008, 1:11 pm
why wouldn’t you just drop the database if you were going to drop all of the tables? in mysql even if you drop the db it keeps user and host info, so you could just recreate it.
drop database $db; create database $db;
Reply Link

Next Script: Iptables block ip address – Security Shell Script

Previous Script: Shell Script To Bridge Multiple Network Interfaces on Xen host to Guests VPS