Use this command:
mysql -uroot -p<password> -e "show databases" | grep -vw Database | grep -v mysql| grep -v information_schema| grep -v sys | gawk '{print " drop database "$1";select sleep(0.1); "}'| mysql -uroot -p<password>
What it does is
mysql -uroot -p<password> -e "show databases" -- This will return the list of dbs. In my case its result is:
Then this command will remove the first line from the result,:
grep -vw Database
The output is now:
mysql -uroot -p<password> -e "show databases" | grep -vw Database | grep -v mysql| grep -v information_schema| grep -v sys | gawk '{print " drop database "$1";select sleep(0.1); "}'| mysql -uroot -p<password>
What it does is
mysql -uroot -p<password> -e "show databases" -- This will return the list of dbs. In my case its result is:
+--------------------------------------------------+| Database |+--------------------------------------------------+| information_schema || DarkConfigDatabase || DarkMailDatabase || DarkServerActiveDatabaseMarkerDatabase || DarkServerAuditingDatabase || DarkServerCardManagerDatabase || DarkServerCertificateAuthorityDatabase || DarkServerComplianceServerSharedSecurityDatabase || DarkServerDataRecoveryDatabase || DarkServerDatabaseMetadata || DarkServerExternalUserAccountManagementDatabase || DarkServerExternalUserDatabase || DarkServerFederatedDatabase || DarkServerRoutingDatabase || mysql || sys |+--------------------------------------------------+
Then this command will remove the first line from the result,:
grep -vw Database
The output is now:
+--------------------------------------------------+
| information_schema |
| DarkConfigDatabase |
| DarkMailDatabase |
| DarkServerActiveDatabaseMarkerDatabase |
| DarkServerAuditingDatabase |
| DarkServerCardManagerDatabase |
| DarkServerCertificateAuthorityDatabase |
| DarkServerComplianceServerSharedSecurityDatabase |
| DarkServerDataRecoveryDatabase |
| DarkServerDatabaseMetadata |
| DarkServerExternalUserAccountManagementDatabase |
| DarkServerExternalUserDatabase |
| DarkServerFederatedDatabase |
| DarkServerRoutingDatabase |
| mysql |
| sys |
+--------------------------------------------------+
Please note the "vw" option, v removes the selection from the result and w is used for matching words. We need to use w switch because the required database names also contain the word "Database" them, if we hadn't used the w switch then the result would be something like this:
+--------------------------------------------------+
| information_schema |
| mysql |
| sys |
+--------------------------------------------------+
This is not what we want, hence the w switch.
Now, this command will remove the information_schema, mysql and sys tables from the result:
grep -v mysql| grep -v information_schema| grep -v sys
So now the result will be:
+--------------------------------------------------+
| DarkConfigDatabase |
| DarkMailDatabase |
| DarkServerActiveDatabaseMarkerDatabase |
| DarkServerAuditingDatabase |
| DarkServerCardManagerDatabase |
| DarkServerCertificateAuthorityDatabase |
| DarkServerComplianceServerSharedSecurityDatabase |
| DarkServerDataRecoveryDatabase |
| DarkServerDatabaseMetadata |
| DarkServerExternalUserAccountManagementDatabase |
| DarkServerExternalUserDatabase |
| DarkServerFederatedDatabase |
| DarkServerRoutingDatabase |
+--------------------------------------------------+
These are the Databases which we need, now all we need to do is to run the db related commands on these. We use gawk tool to run these commands.
gawk '{print " drop database "$1";select sleep(0.1); "}'
This statement will iterate on each of the rows selected by the result of last commands and will print the result. So, the eventual result for one of the iteration will be :
drop database DarkConfigDatabase; select sleep(0.1);
We then pass this string back to mysql by piping it to the mysql command.
And this is how we delete all the dbs in a single bash command.
Thanks :)
Comments
Post a Comment