Quickly Purge a Magento MySQL Database

I’m sure everyone has got to that point where they need to empty a Magento MySQL database and you’ve logged into PHPMyAdmin, selected all the tables, then clicked delete. Only to find out only a small proportion can be deleted because of foreign key constraints. But if you persist, running the “select all” and “drop” process about 6 times in a row normally will end up with a nice clean DB.

There can be a number of reasons why you would choose to drop all tables in a database to clear it out, rather than dropping the whole database and simply re-creating it, so we wrote a quick bash script to automate the process for you.

Running the script is really easy, it will automatically populate the username, password, database name and host from your local.xml file. It even has 2 prompts to make absolutely sure that you want to run it!

cd /path/to/my/magento/store
wget -O mage-dbpurge.sh sys.sonassi.com/mage-dbpurge.sh
chmod +x mage-dbpurge.sh
./mage-dbpurge.sh

So in practice, it looks a little like this. Execution on a 3GB database takes just under 40 seconds.

# cd /home/sonassi/public_html
# wget -O mage-dbpurge.sh sys.sonassi.com/mage-dbpurge.sh
# chmod +x mage-dbpurge.sh
# ./mage-dbpurge.sh
Are you 100% sure you want to purge $DBNAME? [y/N]: y
Are you 110% sure you want to purge $DBNAME? [y/N]: y
########################################	
	
 MYSQL DB PURGE COMPLETE
 
########################################

You can download the file here.