Skip to content
Share this..

Drop all tables from a MySQL Database without deletion

2009 February 13
tags:
by Eddie

This solution allows you to purge all tables from a database without actually deleting the database.
This may not seem practical to those that have full rights and wonder.. “Why not just create a new DB?”.
For others that are using a hosting plan or work for a large company with restrictive bureaucracy will appreciate this simple tip.  The reason is that your account may only have specific rights on that Database and absolutely no rights on the hosting server.  Even if you had permission to delete the old DB, you would have to jump through hoops to get a new one created.  So in short, “Because we can’t”

It has also been pointed out as a way to maintain the schema.

Write a script to loop through all current tables and drop them? – NO!

You could get a listing of all tables, iterate through an loop and drop the tables, but let’s see if we can’t think of a one liner…

Rely on mysqldump and mysql to do all the work? – YES!

We need to drop tables, that’s obvious.  But how to get all the tables with drop command in hand.  What about our ever so faithful and functional friend mysqldump? Of course! Not only can mysqldump handle giving us the names of all the tables, but it can also hand us the drop command.

You’ll need to execute this command where mysqldump and mysql binaries live, typically this is /usr/bin.

/usr/bin/mysqldump -uuser_name -psecretpassword --add-drop-table database_name | grep ^DROP | /usr/bin/mysql  -uuser_name -psecretpassword database_name

SO we pump out all the table and data from the database,use grep to trash everything but the lines that begin with the drop commands and pass that right back into mysql. Works, but should we be passing and parsing all the data just to trash it? Probably not.

Keeping the workload small – no data!

Particularly for those of you that have rather large Databases we don’t want to waste time passing around data, so let’s leave it out entirely.

/usr/bin/mysqldump -uuser_name -psecretpassword --no-data --add-drop-table database_name | grep ^DROP | /usr/bin/mysql  -uuser_name -psecretpassword database_name
7 Responses leave one →
  1. March 6, 2009

    I think we can be a little more efficient than that. We can just ask mysql to give us the table names and use sed to add the “DROP TABLE IF EXISTS” at the front:

    mysql -uuser -ppass -e “show tables” –skip-column-names database | sed -e “s/.*/DROP TABLE IF EXISTS \`&\`;/” | mysql -uuser -ppass database

    (all in one line)
    We can also use xargs instead of sed.
    (You may want to have a look at my post for more info)

  2. Eddie permalink*
    March 6, 2009

    @Prasinos
    Thanks for the feedback though I’m not sure I understand why you say that is more efficient..

    Still one line, and I think that sed would be more resource intensive than grep.

    mysqldump -uuser -ppass --no-data --add-drop-table database | grep ^DROP | mysql  -uuser -ppass database

    Vs.

    mysql -uuser -ppass -e “show tables” –skip-column-names database | sed -e “s/.*/DROP TABLE IF EXISTS \`&\`;/” | mysql -uuser -ppass database

    I will leave the choice up to our readers, more options is not a bad thing.

  3. March 7, 2009

    Eddie, the “more efficient” bit was tongue-in-cheek 🙂
    Still, I think that using only mysql is conceptually better: we want the table names so we just execute “show tables”. We don’t need the “create table” statements of mysqldump so why get them and then grep them out?

    In any case, the difference is not really important.
    I was just toying with mysql/mysqldump to find out more about their potential and your post gave me a nice motivation.

  4. pavel permalink
    October 12, 2009

    worked like a charm,
    elegant solution,
    thanks man!

  5. btw permalink
    November 8, 2011

    BTW your ‘share’ links on the left cover up the content when the browser is small. That’s pretty annoying.

  6. Eddie permalink*
    January 14, 2012

    Made a change to the share tabs, hope the new pop out approach is less intrusive!

Trackbacks and Pingbacks

  1. Create a Duplicate Wordpress QA Site

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS