#!/bin/bash # A shell script to delete / drop all tables from MySQL database. # Usage: ./script user password dbnane # Usage: ./script user password dbnane server-ip # Usage: ./script user password dbnane mysql.nixcraft.in # ------------------------------------------------------------------------- # Copyright (c) 2008 nixCraft project <http://www.cyberciti.biz/fb/> # This script is licensed under GNU GPL version 2.0 or above # ------------------------------------------------------------------------- # This script is part of nixCraft shell script collection (NSSC) # Visit http://bash.cyberciti.biz/ for more information. # ---------------------------------------------------------------------- # See URL for more info: # https://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/ # --------------------------------------------------- MUSER="$1" MPASS="$2" MDB="$3" MHOST="localhost" [ "$4" != "" ] && MHOST="$4" # Detect paths MYSQL=$(which mysql) AWK=$(which awk) GREP=$(which grep) # help if [ ! $# -ge 3 ] then echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} [host-name]" echo "Drops all tables from a MySQL" exit 1 fi # make sure we can connect to server $MYSQL -u $MUSER -p$MPASS -h $MHOST -e "use $MDB" &>/dev/null if [ $? -ne 0 ] then echo "Error - Cannot connect to mysql server using given username, password or database does not exits!" exit 2 fi TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' ) # make sure tables exits if [ "$TABLES" == "" ] then echo "Error - No table found in $MDB database!" exit 3 fi # let us do it for t in $TABLES do echo "Deleting $t table from $MDB database..." $MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e "drop table $t" done
🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source, and DevOps topics via:
- RSS feed or Weekly email newsletter
- 8 comments... add one ↓
Category | List of Unix and Linux commands |
---|---|
File Management | cat |
Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
Network Utilities | dig • host • ip • nmap |
OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
Package Manager | apk • apt |
Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
Searching | grep • whereis • which |
User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
Really great script. I grabbed a few pieces of it for one I’m working on. Saved me a ton of time. I made one tweak to the part where you check if the tables exist.
# make sure tables exits
if [ ! $MYSQLTABLES ]
then
echo "Error - No table found in $MDB database!"
exit 3
fi
good script, add internal mysql options:
thanks for the script, works nicely.
Matt
I have one problem with this script.
It does not take foreign keys into account. It will try to delete tables that have dependencies to other tables and this will throw a error.
I your script would build a dependencies tree and delete top-down it would be perfect ;)
Good answer , straight but polite and smart
hhahaha very good
Because one might not have the right to create a database.
why wouldn’t you just drop the database if you were going to drop all of the tables? in mysql even if you drop the db it keeps user and host info, so you could just recreate it.
drop database $db; create database $db;