Programming and breaking things

A simple script to drop all tables in a MySQL database

Enrico Testori's photo
Enrico Testori
·

2 min read

This is another translation of a post written originally on my blog, i hope it will be useful!

I had to drop all the tables in a MySQL database because the structure and the content were updated. I couldn't use ALTER and UPDATE statements because there were millions of records and a lot of tables i was unaware of.

An easy solution could be importing a dump which previously executes a full database DROP and create it from scratch. The problem? My user didn't have permissions to execute DROP DATABASE queries.

The only possible solution is to eliminate every table one by one. Easy, isn't it? NO! Do you remember about referential integrity constraints? (Spoiler: i didn't, until i tried to execute the first delete query)

In my case the foreign keys were defined without the DELETE ON CASCADE policy, which it was a no sense choice. But, coming back to the original problem, when you try to delete a table that is referenced by one or more tables, the query fails due to integrity constraints.

To work around the problem it is possible to launch the DROP queries multiple times, until no more errors rise. But... there must be a more elegant solution!

Me before dropping a production database

This is me before accidentally dropping a production database

I searched a bit on the documentation and i discovered it is possible to disable integrity contraints with the command SET foreign_key_checks = 0;.

Fortunately, a pious soul has also put together a beautiful ready-to-use snippet to be launched with Bash. You can find it on GitHub:

#!/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

This script worked out of the box for me, however you can tweak it in order to:

  • passing an host argument if the database is not local
  • avoid passing the password to the command since it will be stored into the history (and you should really avoid it)

If this script was useful to you don't forget to star it and leave a comment to its creator!

DatabasesMySQL