Friday, March 21, 2008

Truncate all Tables in a MySQL Database

UPDATE: I've written a new post with an updated script that encourages safer password handling. You should go there instead.

Here is a handy script to truncate all the tables in a MySQL database. I use this sometimes to wipe out an entire database before loading in some production data to replicate a customer problem:



This script isn't perfect -- it should really prompt the user for his password so that these passwords don't get into bash history logs. In a later post I'm going to talk about using this command in conjunction with some command line MySQL command to load a remote database locally for testing.

2 comments:

  1. I just found this command :
    mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

    here : http://www.thingy-ma-jig.co.uk/blog/10-10-2006/mysql-drop-all-tables

    ReplyDelete
  2. does not work if database contains temp tables.

    ReplyDelete