Drop all mysql tables without permission to delete and create new database

In mysql, the simplest way to flush all tables is to delete the db and create a new one. However, one might not have permission to do that. So we need a script to list all tables and delete it.

#!/bin/bash

USER=xxx
PASS=xxx
HOST=localhost
DB=xxx

mysqldump -u$USER -p$PASS -h$HOST --no-data --add-drop-table $DB | grep ^DROP | mysql -v -u$USER -p$PASS -h$HOST $DB

Author: bpeh

Bernard Peh is a great passioner of web technologies and one of the co-founder of Sitecritic.net Website Design and Reviews. He works with experienced web designers and developers everyday, developing and designing commercial websites. He specialises mainly in SEO and PHP programming.