Many out there will have different ideas about this, some using procs, some using a function, others using a shell script. Well I didn’t want to spend much time on it so decided a group_concat(concat would be enough.
There is no genius, rather laziness :) but what if you have a hundred databases and you want to drop them all?

mysql Thu Mar  3 13:50:06 2011 > pager sed 's/,/ /g'
PAGER set to 'sed 's/,/ /g''
mysql Thu Mar  3 13:50:32 2011 > select group_concat(concat('drop database ',SCHEMA_NAME,';')) from information_schema.schemata where SCHEMA_NAME !='mysql' and SCHEMA_NAME !='information_schema';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| group_concat(concat('drop database ' SCHEMA_NAME ';'))                                                                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| drop database abc; drop database def; drop database ghi; drop database jkl; drop database mno; drop database pqr; drop database stu; drop database vwx; drop database yz; |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql Thu Mar  3 13:50:34 2011 > drop database abc; drop database def; drop database ghi; drop database jkl; drop database mno; drop database pqr; drop database stu; drop database vwx; drop database yz;
Query OK, 42 rows affected (2.37 sec)

Query OK, 101 rows affected (4.25 sec)

Query OK, 3 rows affected (0.09 sec)

Query OK, 2 rows affected (0.09 sec)

Query OK, 26 rows affected (0.72 sec)

Query OK, 2 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.12 sec)

Query OK, 160 rows affected (7.98 sec)

mysql Thu Mar  3 13:50:55 2011 > nopager
PAGER set to stdout
mysql Thu Mar  3 13:51:02 2011 >
VN:F [1.9.22_1171]
Rating: 8.4/10 (5 votes cast)
VN:F [1.9.22_1171]
Rating: +5 (from 5 votes)

Removing all databases from a MySQL instance, 8.4 out of 10 based on 5 ratings

  1. TodoInTX says:

    One tip: You can avoid the extra awk foo in the pager by using the SEPARATOR argument of GROUP_CONCAT().

    mysql> select group_concat(concat(‘drop database ‘,SCHEMA_NAME,’;’) SEPARATOR ‘ ‘ ) from information_schema.schemata where SCHEMA_NAME !=’mysql’ and SCHEMA_NAME !=’information_schema’;

  2. Indeed, thank you Matt.

*