Drop multiple databases with names matching a pattern

Question

I want to drop all the databases starting with a word.

abc
xyz
cms_db1
cms_db2
cms_xyz
pqr

In the example given above, I will like to drop all the Databases starting with the word "cms". I guess maatkit or shell script can do it. What is the best approach?

Answer 1

Here's a pure mySQL solution in two queries:

SELECT CONCAT('DROP DATABASE `', SCHEMA_NAME, '`;')
FROM `information_schema`.`SCHEMATA`
WHERE SCHEMA_NAME LIKE 'cms_%';

Then copy and paste the resulting recordset and run

Answer 2

I had to improve neurinos script because of special chars in my password, missing 'drop DATABASE ...' and not working comparision for DB_STARTS_WITH expression. The following script did work on Ubuntu Server:

#!/bin/bash

DB_STARTS_WITH="grp"
MUSER="root"
MPWD="YOUR_PASSWORD"
MYSQL="mysql"

DBS="$($MYSQL -u $MUSER -p"$MPWD" -Bse 'show databases')"
for db in $DBS; do

if [[ "$db" == $DB_STARTS_WITH* ]]; then
    echo "Deleting $db"
    $MYSQL -u $MUSER -p"$MPWD" -Bse "drop database $db"
fi

done

Answer 3

I would use something like:

echo "SHOW DATABASES LIKE 'cms_%'" \
  | mysql \
  | tail -n +2 \
  | xargs -n1 mysqladmin -f drop

If you don't have your default username and password configured inside ~/my.cnf, you may need to supply the username and password via the -u and -p switches to the mysql/mysqladmin commands above.

(Edit - added -n arg to tail.)

Answer 4

Linux way:

#!/bin/bash

DB_STARTS_WITH="cms"
MUSER="root"
MPWD="yourpass"
MYSQL="mysql"

DBS="$($MYSQL -u$MUSER -p$MPWD -Bse 'show databases')"
for db in $DBS; do

if [[ "$db" =~ "^${DB_STARTS_WITH}" ]]; then
    echo "Deleting $db"
    $MYSQL -u$MUSER -p$MPWD -Bse "drop database $db"
fi

done

Of course use the drop part at your own risk ;)

Answer 5

A Linux way:

for db_name in $(mysql -u USER -pPASS -e "show databases like 'cms_%'" -ss)
do
     mysql -u USER -pPASS -e "drop database ${db_name}";
done

Answer 6

If you wish to stay completely within MySQL/MariaDB (i.e. without using bash scripts and such) you can do the following:

DELIMITER //
CREATE PROCEDURE clean()
BEGIN
    SET @query := (SELECT CONCAT('DROP DATABASE ', SCHEMA_NAME, ';') FROM `information_schema`.`SCHEMATA` WHERE SCHEMA_NAME LIKE 'dbtVDB%' LIMIT 1);
    WHILE @query != '' DO
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET @query := (SELECT CONCAT('DROP DATABASE ', SCHEMA_NAME, ';') FROM `information_schema`.`SCHEMATA` WHERE SCHEMA_NAME LIKE 'cms%' LIMIT 1);
    END WHILE;
    DELETE FROM mysql.db WHERE mysql.db.Db LIKE 'cms%';
END;
//
DELIMITER ;
CALL clean();
DROP PROCEDURE clean;

Answer 7

I liked the answer suggesting a "for" loop from the shell. In my case, I had subdirectory names matching my database names so I made arrays, then used them in the command.

(I could have done this using the mysql data directory come to think of it, even if I hadn't had the setup I had. On my bitnami VM this is /opt/bitnami/mysql/data.)

1. created array from subset of files: tbtdirs=(tbt*2015*)

2. Tested a potentially spooky command first w/ "echo": for d in ${tbtdirs[@]}; do echo mysql -pPASS -e "drop database $d"; done

3. dropped all databases in the array: for d in ${tbtdirs[@]}; do mysql -pPASS -e "drop database $d"; done

Worked like a charm! Also modified the loop to remove subdirectories. I used Linux command line for quite some time before learning how useful the bash commands could be.

Answer 8

Using @léo-alves-de-araujo I have modified it to ask the user/password (More secure way) from command line (with linux)

#!/bin/bash 
echo -n "Enter Mysql User:"
read user
echo -n "Enter Mysql Password:"
read -s password
for db_name in $(mysql -u $user --password=$password -e "SHOW DATABASES LIKE 'cms_%'" -ss 2>/dev/null)
do
        mysql -u $user --password=$password -e "DROP DATABASE ${db_name}" 2>/dev/null;
done

Answer 9

Improved @neurino solution to avoid storing of MySQL credentials in the script and passing them through a command line (it might be visible in the list of processes then)

#!/bin/bash

DB_STARTS_WITH="cms"
MYSQL="mysql"

read -p "Enter MySQL user name: " MYSQL_USER
read -s -p "Enter password: " MYSQL_PASSWORD

CREDENTIALS_FILE="$(mktemp)"
chmod 600 $CREDENTIALS_FILE
cat > $CREDENTIALS_FILE <<- EOM
[client]
user=$MYSQL_USER
password=$MYSQL_PASSWORD
EOM

trap "{ rm -f $CREDENTIALS_FILE; }" EXIT

DATABASES="$(echo "show databases;" | $MYSQL --defaults-file=$CREDENTIALS_FILE)"

for DATABASE in $DATABASES; do
    if [[ $DATABASE =~ ^${DB_STARTS_WITH} ]]; then
        echo Removing $DATABASE...
        echo "drop database $DATABASE" | $MYSQL --defaults-file=$CREDENTIALS_FILE
    fi
done

Answer 10

Improvising on the excellent answer by @cloakedninjas, for easier retrieval of all the queries to execute in a single string.

Firstly, you can set the maximum value for group_concat_max_len to the maximum possible value, for this particular session:

SET SESSION group_concat_max_len = @@max_allowed_packet;

Now, you can prepare a query string (to execute later) using SQL. Using information_schema, we can get name of all the databases matching the pattern. Now, use Concat() to prepare a single DROP DATABASE .. query, and then utilize Group_Concat() to merge them all into a single string, for easier retrieval.

SELECT GROUP_CONCAT(CONCAT('DROP DATABASE `', SCHEMA_NAME, '`;') 
                    SEPARATOR ' ') AS query_to_execute
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME LIKE 'cms_%'

Now copy the string in query_to_execute and run it separately.

Answer 11

Option for SQL Server (T-SQL):

DECLARE @command NVARCHAR(MAX) = '';

SELECT @command = @command + 'DROP DATABASE ' + QUOTENAME(name) + CHAR(13)
FROM sys.databases
WHERE name LIKE 'cms\_%' {escape '\'};

PRINT @command

-- Double check generated command and then uncomment
-- EXEC sp_executesql @command;