HowTo Drop All Tables in MySQL Database Using BASH

In this howto i will describe how to execute MySQL commands on BASH shell and do more on results. The beauty of BASH is that you can developed a script which help you to do different, repeated and can execute different MySQL commands on result of previous MySQL command result. My primary example is to drop all tables in MySQL database using BASH shell.

1) Check Number of Databases.

mysql -u root -p<rootpass> -e “SHOW DATABASES”

+————————-+
Database                       
+————————-+
information_schema
Randydb                       
test                                  
mysql                             
+————————-+

2) Check the Number of Tables in a Database

mysql -u root -p<rootpass> -e “SHOW TABLES from mysql”
+—————————+
Tables_in_mysql          
+—————————+
columns_priv                
db                                        
func                                    
help_category               
help_keyword               
help_relation                 
help_topic                       
host                                    
proc                                   
procs_priv                      
tables_priv                     
time_zone                       
user                                    
+—————————+

3) Drop All Tables in a Database

There is no single command for dropping all tables on mysql prompt. You can drop table one by one. To drop all tables we can script it using bash

mysql -u root -p <rootpass> -e “SHOW TABLES FROM Randydb” | grep -v “Tables_in_Randydb” | while read a; do
mysql -u root -p<rootpass> -e “DROP TABLE Randydb.$a”
done

The above script will drop all tables in Randydb. In this way you can create several small scripts to maintain / manage / monitor your MySQL. I am creating new mysql-bash scripts as per need and will post as soon as there is any new one.