Mysql 'DROP ALL TABLES' one-liner:

Prompt user about database name:

bash -c 'if [ "$#" -eq 0 ];then read -p "Enter database name: " dbname; else dbname="$1" ; fi; mysqldump --no-data --add-drop-table $dbname | grep ^DROP | echo "SET FOREIGN_KEY_CHECKS=0; $(cat -); SET FOREIGN_KEY_CHECKS=1; " | mysql -v $dbname ;'

Add database name as a last argument (instead of DBNAME):

Description:

bash -c 'BASH_CODE' - ARG1 ARG2

Tells bash to execute BASH_CODE and pass strings '-', 'ARG1', 'ARG2' as parameters bound to positional parameters $0, $1, $2 etc..

Our BASH_CODE looks like

if [ "$#" -eq 0 ]; then read -p "Enter database name: " dbname; else dbname="$1" ; fi; mysqldump --no-data --add-drop-table $dbname |\ grep ^DROP |\ echo "SET FOREIGN_KEY_CHECKS=0; $(cat -); SET FOREIGN_KEY_CHECKS=1; " |\ mysql -v $dbname ;

Lets break our BASH_CODE and see what it is doing:

if [ "$#" -eq 0 ]; if number of arguments is zero

then read -p "Enter database name: " dbname; then prompt user about database name and put it into $dbname variable

else dbname="$1" ; fi; otherwise take it from command line argument positioned at 1 (after '-' sign)

Following lines make the real job

mysqldump --no-data --add-drop-table $dbname |ask mysqldump to generate SQL code to drop and recreate tables, pass this code to

grep ^DROP |grep which will filter out all non "DROP TABLE" lines and pass results to

echo "SET FOREIGN_KEY_CHECKS=0; $(cat -); SET FOREIGN_KEY_CHECKS=1; " |echo, which will append and prepend disable/enable foreign keys check and at last pass it to

mysql -v $dbname mysql, which will execute the resulting pack of queries

Notes:

Mysql is supposed to be configured not to ask current user password. Otherwise u have to invoke mysqldump and mysql differently (choose one depending on if u want to pass alternative username):

mysqldump -p --no-data --add-drop-table $dbname mysqldump -p -u USERNAME --no-data --add-drop-table $dbname

mysql -p -v $dbname mysql -p -u USERNAME -v $dbname