Truncate all Tables in a MySQL Database Revisited

Since this old post still manages to get a fair number of hits I thought I should fix it up to encourage people to not reveal their MySQL passwords in history logs and process lists by entering it on the command line. The new version uses getopts for parsing command line options. If you pass in "-p", it will prompt you for your database password for each mysql command. If you really want the old bad behavior then "-P password" will do the trick. Enjoy:

	#!/usr/bin/env bash
	usage() {
	echo "\
	Usage: $0 -u username -d database [-h host] [-o port)] \\
	[[-p] | [-P password]]
	host defaults to localhost
	port defaults to 3306
	-p will cause mysql to prompt for the password, good
	-P password will show the password in your ps list, evil" >&2
	exit 1
	}
	username=
	passwdprompt=no
	host=localhost
	port=3306
	database=
	password=
	while getopts ":u:pPd:h:" name; do
	case $name in
	u) username=$OPTARG;;
	p) passwdprompt="yes";;
	P) password=$OPTARG;;
	d) database=$OPTARG;;
	h) host=$OPTARG;;
	?) usage;;
	esac
	done
	if [ -z "${username}" ] || [ -z "${database}" ]; then
	usage
	fi
	basecmd="mysql -u ${username} -D ${database} -h ${host} -P ${port}"
	if [ "${passwdprompt}" = "yes" ]; then
	basecmd="${basecmd} -p"
	elif [ -n "${password}" ]; then
	basecmd="${basecmd} -p${password}"
	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 mysqltrunc.sh hosted with ❤ by GitHub

It is also possible to put your password in your my.cnf file to have it picked up automatically. You can read more about MySQL password for version 5.4 security here.