Drop multiple databases with names matching a pattern

I had to improve neurinos script because of special chars in my password, missing 'drop DATABASE ...' and not working comparision for DB_STARTS_WITH expression. The following script did work on Ubuntu Server:

#!/bin/bash

DB_STARTS_WITH="grp"
MUSER="root"
MPWD="YOUR_PASSWORD"
MYSQL="mysql"

DBS="$($MYSQL -u $MUSER -p"$MPWD" -Bse 'show databases')"
for db in $DBS; do

if [[ "$db" == $DB_STARTS_WITH* ]]; then
    echo "Deleting $db"
    $MYSQL -u $MUSER -p"$MPWD" -Bse "drop database $db"
fi

done

Here's a pure mySQL solution in two queries:

SELECT CONCAT('DROP DATABASE `', SCHEMA_NAME, '`;')
FROM `information_schema`.`SCHEMATA`
WHERE SCHEMA_NAME LIKE 'cms_%';

Then copy and paste the resulting recordset and run

I would use something like:

echo "SHOW DATABASES LIKE 'cms_%'" \
  | mysql \
  | tail -n +2 \
  | xargs -n1 mysqladmin -f drop

If you don't have your default username and password configured inside ~/my.cnf, you may need to supply the username and password via the -u and -p switches to the mysql/mysqladmin commands above.

(Edit - added -n arg to tail.)

Linux way:

#!/bin/bash

DB_STARTS_WITH="cms"
MUSER="root"
MPWD="yourpass"
MYSQL="mysql"

DBS="$($MYSQL -u$MUSER -p$MPWD -Bse 'show databases')"
for db in $DBS; do

if [[ "$db" =~ "^${DB_STARTS_WITH}" ]]; then
    echo "Deleting $db"
    $MYSQL -u$MUSER -p$MPWD -Bse "drop database $db"
fi

done

Of course use the drop part at your own risk ;)