Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Usually I open Terminal.app and connect to a remote MySQL database.

Then I use this command to drop a table:

mysql> drop table [table name];

But what I need is the command line to drop all tables in the database.

If I use:

mysql> drop database [database name];

I'll destroy the database completely and I won't be able to create tables again. Am I right?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

You can drop the database then immediately recreate it:

mysql> drop database [database name];
mysql> create database [database name];

Or you could use a script to drop each table in the database.

share|improve this answer
    
ok, just to be sure: I connect to remote database in Terminal with this line: /Applications/MAMP/Library/bin/mysql -h 80.54.554.10 -u adm_user -p my_db. After dropping and creating the database as you said will I need to change this connection values or they remain the same? –  chefnelone Jul 8 '11 at 11:57
    
@chef - Dropping all tables and dropping the whole database does not have the same effect. If you drop only the tables any database wide permissions will remain, not so if you drop/recreate the database. –  Nifle Jul 8 '11 at 21:16
3  
@chefnelone : Be careful !!! Dropping a database drops stored procedures and user-defined views as well. –  RolandoMySQLDBA Jul 9 '11 at 1:03
    
Quite clear for me. I'll go with the script to delete each table. –  chefnelone Jul 11 '11 at 7:31

You can try the following command:

mysqldump --no-data --add-drop-table DB_NAME | grep ^DROP | mysql -v DB_NAME

Or:

mysql --silent --skip-column-names -e "SHOW TABLES" DB_NAME | xargs -L1 -I% echo 'DROP TABLE `%`;' | mysql -v DB_NAME

Where DB_NAME is your database name. Database credentials you can specify either in ~/.my.cnf or adding them to the command (e.g. -uroot -proot).

This method has some advantages over dropping and creating the database in case your database user doesn't have permission to drop it.

share|improve this answer
1  
Confirmed second example, awesome. Add db credentials as additional flags before --silent and -v / --verbose respectively like: --user=username --password=password --host=host.name –  here May 20 at 3:41

mysql -u USERHERE -pPASSWORDHERE --silent --skip-column-names -e "SHOW TABLES" DATABASENAMEHERE | xargs -L1 -I% echo 'SET FOREIGN_KEY_CHECKS = 0; DROP TABLE%;' | mysql -u USERHERE -pPASSWORDHERE -v DATABASENAMEHERE

share|improve this answer
2  
Welcome to Super User!  While this may answer the question, it would be a better answer if you could explain why it does so.  We’re looking for comprehensive, high-quality answers that provide some explanation and context.  Don’t just give a one-line answer; explain why your answer is right, ideally with citations.  Answers that don’t include explanations may be removed.  Also, check your formatting. –  G-Man May 29 at 1:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.