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:

	#!/usr/bin/env bash
	if [ $# -eq 2 ]; then
	user=$1
	dbname=$2
	basecmd="mysql -u ${user} -D ${dbname}"
	elif [ $# -eq 3 ]; then
	user=$1
	PASS=$2
	dbname=$3
	basecmd="mysql -u ${user} -p$PASS -D ${dbname}"
	else
	echo "usage: mysqltrunc user [pass] database" >&2
	exit 1
	fi
	tables=$(${basecmd} -e "SHOW TABLES;" | grep -v "+--" | grep -v "Tables_in_${dbname}")
	if [ $? -ne 0 ]; then
	echo "Unable to retrieve the table names." >&2
	exit 1
	fi
	cmd=""
	for table in ${tables}; do
	cmd="${cmd} TRUNCATE ${table};"
	done
	$(${basecmd} -e "${cmd}")

view raw gistfile1.sh hosted with ❤ by GitHub

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.