MySQL Drop All Tables Shell Script Utility

by on November 13, 2008 · 7 comments

  1. #!/bin/bash
  2. # A shell script to delete / drop all tables from MySQL database.
  3. # Usage: ./script user password dbnane
  4. # Usage: ./script user password dbnane server-ip
  5. # Usage: ./script user password dbnane mysql.nixcraft.in
  6. # -------------------------------------------------------------------------
  7. # Copyright (c) 2008 nixCraft project <http://www.cyberciti.biz/fb/>
  8. # This script is licensed under GNU GPL version 2.0 or above
  9. # -------------------------------------------------------------------------
  10. # This script is part of nixCraft shell script collection (NSSC)
  11. # Visit http://bash.cyberciti.biz/ for more information.
  12. # ----------------------------------------------------------------------
  13. # See URL for more info:
  14. # http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/
  15. # ---------------------------------------------------
  16.  
  17. MUSER="$1"
  18. MPASS="$2"
  19. MDB="$3"
  20.  
  21. MHOST="localhost"
  22.  
  23. [ "$4" != "" ] && MHOST="$4"
  24.  
  25. # Detect paths
  26. MYSQL=$(which mysql)
  27. AWK=$(which awk)
  28. GREP=$(which grep)
  29.  
  30. # help
  31. if [ ! $# -ge 3 ]
  32. then
  33. echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} [host-name]"
  34. echo "Drops all tables from a MySQL"
  35. exit 1
  36. fi
  37.  
  38. # make sure we can connect to server
  39. $MYSQL -u $MUSER -p$MPASS -h $MHOST -e "use $MDB" &>/dev/null
  40. if [ $? -ne 0 ]
  41. then
  42. echo "Error - Cannot connect to mysql server using given username, password or database does not exits!"
  43. exit 2
  44. fi
  45.  
  46. TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
  47.  
  48. # make sure tables exits
  49. if [ "$TABLES" == "" ]
  50. then
  51. echo "Error - No table found in $MDB database!"
  52. exit 3
  53. fi
  54.  
  55. # let us do it
  56. for t in $TABLES
  57. do
  58. echo "Deleting $t table from $MDB database..."
  59. $MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e "drop table $t"
  60. done


4000+ howtos and counting! If you enjoyed this article, join 45000+ others and get free email updates!

Click here to subscribe via email.

  • Dallas Marlow

    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;

  • moxnox

    Because one might not have the right to create a database.

  • moxxom

    Good answer , straight but polite and smart

  • jexxer

    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 ;)

  • Matthew Setter

    thanks for the script, works nicely.

    Matt

  • antonio

    hhahaha very good

  • fitorec

    good script, add internal mysql options:

    TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -BNe 'show tables' )
    
    • - B: Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file.
    • -N: Do not write column names in results.

Previous Script:

Next Script: