Advanced Search

mysql-empty-tables

/ Published in: Bash
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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. PHP=$(which php)
  30.  
  31. # help
  32. if [ ! $# -ge 3 ]
  33. then
  34. 	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} [host-name]"
  35. 	echo "Drops all tables from a MySQL"
  36. 	exit 1
  37. fi
  38.  
  39. # make sure we can connect to server
  40. $MYSQL -u $MUSER -p$MPASS -h $MHOST -e "use $MDB"  &>/dev/null
  41. if [ $? -ne 0 ]
  42. then
  43. 	echo "Error - Cannot connect to mysql server using given username, password or database does not exits!"
  44. 	exit 2
  45. fi
  46.  
  47. TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
  48.  
  49. # make sure tables exits
  50. if [ "$TABLES" == "" ]
  51. then
  52. 	echo "Error - No table found in $MDB database!"
  53. 	exit 3
  54. fi
  55.  
  56. # let us do it
  57. for t in $TABLES
  58. do
  59. 	echo "Deleting $t table from $MDB database..."
  60. 	$MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e "SET autocommit=0; SET unique_checks=0; set foreign_key_checks = 0;truncate table $t; SET autocommit=1; SET unique_checks=1; set foreign_key_checks = 1;"
  61. done

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.