How to DROP all MySQL tables from the command line

If you want to drop all the tables in a database, but didn’t have access to a UI like phpMyAdmin, then the easiest thing to do would have been to drop the entire database then re-create it. But If you don’t have permissions to drop an entire database then use the command line as shown.

mysqldump -u username -p –no-data dbname | grep ^DROP > drop.sql

mysql -u username -p dbname < drop.sql

rm drop.txt

What these commands do is basically generated a mysqldump file (which included DROP commands by default), then filter the output to only show the lines that have the DROP statements. The arrow (>) redirects the output to a file. The second line then imports those SQL statements into the database.

Another option I have is to use information_schema database and generate an sql script like below:

 Select concat(‘DROP TABLE database_name.’, table_name,’;’) from information_schema.TABLES where table_schema=’database_name’;

Which gives:

DROP TABLE database_name.table_name1;

DROP TABLE database_name.table-nameN;

You can pipe it to be executed directly. I prefer this syntax than the DROP TABLE IF EXISTS table_name1;

Note : Replace database name with your DB Name. Also, sometime if you execute generated script, then it may throw exceptions if tables have dependencies with each other. In such case you have to first delete master table and then dependent table.


About Manish Jha

Hi All I am Manish Kumar Jha aka Alex Hunt. I am currently working in VMware Software India Pvt Ltd as Operations System Engineer (vCloud Air Operations). I have around 5 Years of IT experience and have exposure on VMware vSphere, vCloud Director, RHEL and modern data center technologies like Cisco UCS and Cisco Nexus 1000v and NSX. If you find any post informational to you please press like and share it across social media and leave your comments if you want to discuss further on any post. Disclaimer: All the information on this website is published in good faith and for general information purpose only. I don’t make any warranties about the completeness, reliability and accuracy of this information. Any action you take upon the information you find on this blog is strictly at your own risk. The Views and opinions published on this blog are my own and not the opinions of my employer or any of the vendors of the product discussed.
This entry was posted in Linux/CentOS. Bookmark the permalink.

One Response to How to DROP all MySQL tables from the command line

  1. Pingback: Are you sure? |

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s