CODEWITHSUNDEEP
mysql
Vinod HC
Vinod HC

Reputation: 1627

Drop multiple databases using mysql command

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.

Upvotes: 10

Views: 37857

Answers (5)

hngr18
hngr18

Reputation: 857

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)!

Upvotes: 6

Binoy Antony
Binoy Antony

Reputation: 21

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.

Upvotes: 2

gouthV_
gouthV_

Reputation: 395

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.

Upvotes: 13

Jesse Burcsik
Jesse Burcsik

Reputation: 367

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

Upvotes: 6

nan
nan

Reputation: 20296

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

Upvotes: 2

Related Questions