Drop multiple databases using mysql command

开发者_JAVA技巧

I have many databases with different names. I want to drop multiple databases, Is there any command since all names of db are different.

Eg: mysql db, Test db, live db.

---

As of I know, there is no specific command/query to delete multiple databases without having a specific pattern in their names. Even I was asked to do the favor several times. So I researched and found no specific solution. Then I tried the below hack. It worked without giving much trouble. May be it could help for you too.

Take all the databases using the below command.

SHOW DATABASES ;

Paste all of them in an excel/some other text file (I prefer NPP). Keep the only names which you want to delete from the list. Dont forget to remove your working db's from the list.

Add DROP DATABASE in front of those names.

That's it simple. Copy & Paste all of those in your workbench. You can execute all of them in one shot.

---

If you create a shell script this should remove all the databases. You will need to edit it to suit your needs.

DBUSER='user'
DBPASS='password'
SQLFILE='/path/to/file/databases.sql'

echo '* Dropping ALL databases'

DBS="$(mysql -u$DBUSER -p$DBPASS -Bse 'show databases' | grep -v Database | grep -v database | grep -v mysql | grep -v information_schema)"

for db in $DBS; do
    echo "Deleting $db"
    mysql -u$DBUSER -p$DBPASS -Bse "drop database $db; select sleep(0.1);"
done

---

First run this query to produce a list of drop commands:

select CONCAT('drop database `', schema_name,'`;') as database_name from information_schema.schemata where schema_name like '%DATABASES_TO_REMOVE%' order by schema_name;

Then copy the output rows of this query and paste into a query window

In my case I then needed to remove the single-quotes (') surrounding the resulting command queries which I did using a simple find + replace (often Ctrl + H, replace ' with < empty >)

And execute (highlighting all of the drop statements in my case)!

---

Unfortunetly, there is nothing like that, unless you create your own function.

---

simple bash script can be done this work

#!/bin/bash
cat /home/mshafee/file | while read line

do
        mysql -u username -p****** -h 0.0.0.0 -e "drop database $line;"

done

here provide username, password and IP address.