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.
1
-
I don't think there is a command to drop multiple databases in one statement. You could put a list of the databases in a text file and then run some sort of clever regex search-and-replace over it to produce a suitable statement.tdammers– tdammers2011-01-11 11:31:48 +00:00Commented Jan 11, 2011 at 11:31
Add a comment
|
5 Answers
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.
3 Comments
tdaget
MS Excel function :
=CONCATENER("drop database "; SUPPRESPACE(A1); ";")
R Nanthak
will this drop parallely?
Martijn Heemels
@RNanthak No, in series.
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
Comments
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)!
Comments
Unfortunetly, there is nothing like that, unless you create your own function.
Comments
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.
