MySQL: Drop all tables from Database using Script / Linux

Hello,

Few days back, I got a task to do, Task was Keep the database as it is, So that we do not have to add database users, privileges and everything again and again, Just Drop all the databases tables inside the database. If there are only few tables like 5 or 10 than it is easy to do it manually, but what if you have the 100s or 1000s of tables inside database ?

I was in same situation, there were more than 500 tables and  I need to drop it quickly as it was production server, So for the same I managed to do it easily by using few command line parameters and loop, After that I have created script for the same which helps me to drop those tables very easily, You don’t have to worry about each and every table, It will keep database as it is but will drop all the tables from the selected database.

I am sharing that script and steps how to use that script, Would request you to test on non production environment first, Wherever you are executing this script its totally on your risk.

MySQL Script

SUGGESTION: Take a Backup of your database / tables / MySQL Before executing Script, So if anything goes wrong you can also recover.

Steps to execute script :-

1. Download Script

vikram@linux-ahmedabad:~$ wget vikram/Scripts/dropall_mysql_tables.sh

2. Give executable permissions

vikram@linux-ahmedabad:~$ chmod +x dropall_mysql_tables.sh

3. Now Let’s execute Script

vikram@linux-ahmedabad:~$ ./dropall_mysql_tables.sh

4. Provide name of the MySQL database from which you want to drop all the tables.

Enter Database name:                         <—- Provide Database name here
Enter MySQL root Password:           <—- Provide root password of MySQL Here

5. Script will take a Full database backup into /tmp, but I would request don’t depend on this, before executing script take backup.

6. That’s it wait for few minutes, It will drop all the tables from MySQL Database.