Drop MySQL databases matching some wildcard?

Understand the command: Are you sure?

Before you turn on the ignition for this heavy artillery, make sure you're 100% certain about the target databases. Any destructive operation like this should be preceded by a data backup. Always test the commands in a sandbox first.

Automated deletion: The developer's Roomba

For more complex scenarios, we can utilize scripting to automate the dropping process. Here's a nifty PHP script that does just that:

<?php
$mysqli = new mysqli("yourHost", "yourUser", "yourPassword", "");

$result = $mysqli->query("SHOW DATABASES LIKE 'wildcard_%'");
while ($row = $result->fetch_array()) {
    $database = $row[0];
    // "With great power comes great responsibility" - Uncle Ben would double-check
    if (preg_match('/^wildcard_[\w]+$/', $database)) {
        $mysqli->query("DROP DATABASE `". $mysqli->real_escape_string($database) ."`");
    }
}
?>


This will retrieve a list of databases, match those against a complex pattern, and drop them after verification. Always ensure a backup precedes the execution, and run the script in a safe test environment beforehand.

Damage control: Safety nets

Once you're ready to deploy scripts for production, it's advisable to include the following safety mechanisms:

• Dialogue prompts for confirmation before dropping.
• Logging the drop commands for trail purposes.
• Using low-traffic periods for script execution to minimize the impact.

Other safe alternatives: When you're not a one-liner fan

If you're not comfortable using gutsy one-liners, you can opt to use graphical tools like MySQL Workbench, which provide intuitive interfaces.

With MySQL Workbench

With MySQL Workbench, use this:

SHOW DATABASES LIKE 'prefix%';


Once you have the list, you can manually run DROP statements, thus maintaining stringent control over the deletion process. Use with caution!

With Python or Jython

A more versatile approach would be to use languages like Python or Jython. With these, you benefit from granular control over the databases, and you can implement additional logic, and safety checks.

Mind the special characters

Database names with special characters require special attention; they need to be escaped correctly. For instance, an underscore (_) is also a one-character wildcard in SQL.

mysql -u user -p -e "SHOW DATABASES LIKE 'wildcard\_%';" | grep 'wildcard' | xargs -I "{}" mysql -u user -p -e "DROP DATABASE \`{}\`;"
