I want to drop all the databases starting with a word.
abc
xyz
cms_db1
cms_db2
cms_xyz
pqr
In the example given above, I will like to drop all the Databases starting with the word "cms". I guess maatkit or shell script can do it. What is the best approach?
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 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With