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”
2) Check the Number of Tables in a Database
mysql -u root -p<rootpass> -e “SHOW TABLES from mysql”
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”
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.