Adminbuntu

Everything for the Ubuntu Server Administrator

User Tools

Site Tools


Sidebar

Server Administration


Server Applications


At the Command Line


Elsewhere


Copyright 2013 Applied Conscious Technologies, LLC

Terms of Agreement

Contact


submit to reddit

drop_all_tables_in_a_database

MySQL

Drop All Tables in a MySQL Database

IMPORTANT: This is a dangerous command. Back up the database first!

Drop all tables in a database, without dropping the database itself. In other words, this leaves an empty database with no tables in it.


This one liner will drop all tables in a MySQL database.

  • Replace USERNAMEHERE with the MySQL user to use, Likely “root”.
  • Replace PASSWORDHERE with the correct password
  • Replace DATABASENAMEHERE with the name of the database

First Back up the Database

Before executing the one line command to drop all tables, make a backup.

mysqldump --user=USERNAMEHERE --password=PASSWORDHERE DATABASENAMEHERE > DATABASENAMEHERE.dump.sql

Drop all Tables in the Database

You made that backup, right?

mysql --user=USERNAMEHERE --password=USERNAMEHERE -BNe "SHOW TABLES" DATABASENAMEHERE | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=USERNAMEHERE --password=USERNAMEHERE DATABASENAMEHERE

This one liner is a great example of generating and then executing a MySQL script on one line using Gnu commands.

  • The first mysql command outputs a table of all tables in the specified database.
  • tr '\n' ',' changes the list into a single line with table names separated by commas
  • sed -e 's/,$//' removes the trailing comma
  • The awk command prepends and postpends additional lines of MySQL statements
  • The second mysql command executes the generated MySQL commands

drop_all_tables_in_a_database.txt · Last modified: 2015/05/31 21:20 (external edit)