Skip to content

Instantly share code, notes, and snippets.

@cweinberger
Created June 6, 2018 12:44
Show Gist options
  • Save cweinberger/c3f2882f42db8bef9e605f094392468f to your computer and use it in GitHub Desktop.
Save cweinberger/c3f2882f42db8bef9e605f094392468f to your computer and use it in GitHub Desktop.
drops all tables of a specific db
#!/bin/bash
#usage: mysql-drop-all-tables -d database -u dbuser -p dbpass
TEMP_FILE_PATH='./drop_all_tables.sql'
while getopts d:u:p: option
do
case "${option}"
in
d) DBNAME=${OPTARG};;
u) DBUSER=${OPTARG};;
p) DBPASS=${OPTARG};;
esac
done
echo "SET FOREIGN_KEY_CHECKS = 0;" > $TEMP_FILE_PATH
( mysqldump --add-drop-table --no-data -u$DBUSER -p$DBPASS $DBNAME | grep 'DROP TABLE' ) >> $TEMP_FILE_PATH
echo "SET FOREIGN_KEY_CHECKS = 1;" >> $TEMP_FILE_PATH
mysql -u$DBUSER -p$DBPASS $DBNAME < $TEMP_FILE_PATH
rm -f $TEMP_FILE_PATH
@jonataswalker
Copy link

Thank you dude, so useful!

@jchook
Copy link

jchook commented Nov 16, 2018

Thanks for the code! I modified slightly to:

  • Support MariaDB's sudo style root access (no password)
  • Avoid writing to a temp file
#!/bin/bash
echo \
  "SET FOREIGN_KEY_CHECKS = 0;" \
  $(mysqldump --add-drop-table --no-data "$@" | grep 'DROP TABLE') \
  "SET FOREIGN_KEY_CHECKS = 1;" \
| mysql "$@"

Usage remains similar:

mysql-drop-all-tables [-udbuser] [-pdbpass] database

@kepi
Copy link

kepi commented Oct 14, 2022

I suggest simply using:

mysqldump --add-drop-table --no-data $DBNAME | grep 'DROP TABLE' | mysql --init-command="SET FOREIGN_KEY_CHECKS = 0;" $DBNAME

ommiting -u and -p as it is better to manage through .my.cnf or socket access

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment