How to drop multiple databases in MySQL or MariaDB

Scenario:

You are working as MySQL or MariaDB DBA, you have created many databases for testing purpose. After testing you are asked to drop all of them, How would you do that?

Solution:

The below script can be used to loop through list of database those you wan to delete. Change the below query to choose the databases you want to drop.

Select Table_schema from information_Schema.tables where Table_schema like "%your pattern%"

# Provide the user name that has permission on mysql to drop the database
MYSQL_USER="yourUserName"
#Provide the Password 
MYSQL_PASS="YourPasswordforAboveUser"
MYSQL="mysql"
# Get the database list
for db in $(mysql -B -s -u $MYSQL_USER --password=$MYSQL_PASS  -e 'Select Table_schema from information_Schema.tables where Table_schema like "%your Pattern to filter the DBs%";')
do
  # backup each database in a separate file
echo $db
 $MYSQL -u$MYSQL_USER -p$MYSQL_PASS -Bse "drop database $db"
done