Skip to main content

Deleting all the mysql databases from command line

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:

+--------------------------------------------------+
| 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

Popular posts from this blog

@MappedSuperclass vs. @Inheritance

MappedSuperClass must be used to inherit properties, associations, and methods. Entity inheritance must be used when you have an entity, and several sub-entities. You can tell if you need one or the other by answering this questions: is there some other entity in the model which could have an association with the base class? If yes, then the base class is in fact an entity, and you should use entity inheritance. If no, then the base class is in fact a class that contains attributes and methods that are common to several unrelated entities, and you should use a mapped superclass. For example: You can have several kinds of messages: SMS messages, email messages, or phone messages. And a person has a list of messages. You can also have a reminder linked to a message, regardless of the kind of message. In this case, Message is clearly an entity, and entity inheritance must be used. All your domain objects could have a creation date, modification date and ID, and you could thus ...
Post a Comment
Read more

Some good links

https://www.html5rocks.com/en/tutorials/internals/howbrowserswork/ http://taligarsiel.com/ClientSidePerformance.html -- Client side performance tips https://ariya.io/ https://vertx.io/docs/ -- New exciting Framework, Must read. https://javaee.github.io/ -- Very good resource to see various javaee projects and explore enterprise architecture and design concepts. https://projects.eclipse.org/projects/ee4j -- Lots of interesting open source projects by eclipse http://openjdk.java.net/projects/mlvm/ -- the main project for supporting more dynamic languages to jvm. http://esprima.org/ -- EcmaScript parser http://c2.com/ppr/ and http://hillside.net/ -- Good place to learn patterns http://cr.openjdk.java.net/~briangoetz/lambda/Defender%20Methods%20v4.pdf https://validator.w3.org/nu/ -- This will validate your website css and js https://www.cellstream.com/intranet/reference-reading/faq/216-what-is-2-128.html http://shattered.io/ -- An example of SHA1 collision attack.
1 comment
Read more

String.format or String concat?

I'd suggest that it is better practice to use `String.format()` . The main reason is that `String.format()` can be more easily localised with text loaded from resource files whereas concatenation can't be localised without producing a new executable with different code for each language If you plan on your app being localisable you should also get into the habit of specifying argument positions for your format tokens as well: "Hello %1$s the time is %2$t" This can then be localised and have the name and time tokens swapped without requiring a recompile of the executable to account for the different ordering. With argument positions you can also re-use the same argument without passing it into the function twice: String.format("Hello %1$s, your name is %1$s and the time is %2$t", name, time) Because printf-style format strings are interpreted at runtime, rather than validated by the compiler, they can contain errors that result in the wrong str...
Post a Comment
Read more