How to clear all tables from a MySQL database.

Bash Shell Scripting

Sometimes you may be in a situation where you need to clear out a database with out actually deleting the database, Maybe the database is on a hosted service that doesn’t allow you root access, or they do not give you phpMyAdmin access (By the way don’t ever use that!).  I actually ran into this very scenario, so I need to figure out a fast way to nuke the database from the Linux command line, with out root access. So I wrote this little shell script to do the job for me.

 

#!/bin/bash

if [ -z "$1" ] || [ -z "$2" ]
then  
       echo "You are missing some parameters, you need to pass database, username and password."
       exit
fi

read -p "Are you really really sure you want to nuke $1? [y/N]" -n 1 -r
echo

if [[ $REPLY =~ ^[Yy]$ ]]
then
       echo "Launching missiles at target $1"
       mysqldump --add-drop-table --no-data  -u $2 -p $1 | grep "DROP TABLE" >droptables.sql && mysql -u $2 -p $1 <dropt
ables.sql
else  
       echo "Mission aborted! Have a nice day!"
       echo
       exit
fi
echo "Your database $1 has been nuked."
echo
echo "Cleaning up temporary files..."
rm droptables.sql
echo "Done!"
echo

 

Now, I know passing passwords along the command line is a bad idea, but the system was a single user system, so there is no chance of anyone seeing the password.  If this is an issue, you can remove the --password=$3 from the code (2 instances of it) and also remove the || [-z “$3”] from the if statement. This will make mysql prompt you for the database password twice.  Much more secure that way in a multi-user environment.

 

-Fratm