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.
about the author: Sohail Riaz
related posts
» Linux, Programming / Scripting » HowTo Drop All Tables in MySQL...
2 Responses to HowTo Drop All Tables in MySQL Database Using BASH
Leave a Reply
« How To Install MyTOP – a top clone for MySQL How To Display Loadavg on Terminal/Putty Title »
Hi, i just want to mention that if you mysql server is not on the localhost machine, then you can access this way:
mysql -h -u root -p -e “SHOW DATABASES”
it helps for me, thanks for your post 😀
Its really Helpfull………..
mysql -u root -p -e “SHOW DATABASES”
Thankss a lot