I would like to drop all tables in a database of MySQL but I don't want to drop the database itself. The reason are that the created tables seemed to be incorrect and I would like to recreate or import it again. However, I am so lazy to create the database and grant the user/pass again. Fortunately, I found a workaround trick.

The trick is as simple as follow.

mysqldump -u$USER -p$PASS --add-drop-table --no-data $DATABASE | \
    grep ^DROP | \
    mysql -u$USER -p$PASS $DATABASE

The idea is to dump the database using mysqldump which also generates ADD and DROP command. And then filter out all DROP.

Tags: mysql