CODEWITHSUNDEEP
mysqlshell
T. Brian Jones
T. Brian Jones

Reputation: 13502

How can I drop all MySQL Databases with a certain prefix?

I need to drop hundreds of mysql databases that all share a common prefix, but have random id's as the rest of their name ( eg. database_0123456789, database_9876543210 ). All these databases are on the same server. There are other databases on that same server that I don't want to drop.

This is what I'd like to do:

DROP DATABASE `database_*`;

How can I drop these efficiently? Is there a MySQL query I can run? Maybe a shell script?

Upvotes: 19

Views: 31209

Answers (4)

spencer7593
spencer7593

Reputation: 108380

The syntax of the DROP DATABASE statement supports only a single database name. You will need to execute a separate DROP DATABASE statement for each database.

You can run a query to return a list of database names, or maybe more helpful, to generate the actual statements you need to run. If you want to drop all databases that start with the literal string database_ (including the underscore character), then:

SELECT CONCAT('DROP DATABASE `',schema_name,'` ;') AS `-- stmt`
  FROM information_schema.schemata
 WHERE schema_name LIKE 'database\_%' ESCAPE '\\'
 ORDER BY schema_name

Copy the results from that query, and you've got yourself a SQL script.

(Save the results as plain text file (e.g. dropdbs.sql), review with your favorite text editor to remove any goofy header and footer lines, make sure the script looks right, save it, and then from the mysql command line tool, mysql> source dropdbs.sql.)

Obviously, you could get more sophisticated than that, but for a one-time shot, this is probably the most efficient.)

Upvotes: 42

Jürgen Steinblock
Jürgen Steinblock

Reputation: 31723

This question lacks an answer without creating a file first.

Our build server automatically creates a database for every topic branch while running unit tests. After information_schema queries get really slow which causes our tests to fail.

I created a batch file which runs every day. I did not want to deal with temporary files. So here is my solution.

@ECHO OFF
REM drops all databases excluding defaults
SET user=user
SET pass=pass

mysql ^
-u %user% ^
-p%pass% ^
-NBe "SELECT CONCAT('drop database `', schema_name, '`;') from information_schema.schemata where schema_name NOT IN ('mysql', 'test', 'performance_schema', 'information_schema')" | mysql -u %user% -p%pass%

Upvotes: 2

Pardeep Kumar
Pardeep Kumar

Reputation: 1819

Modifying spencer7593 answer

Here is the command to find desired results and save it in file where prefix is database prefix

 SELECT CONCAT('DROP DATABASE ',schema_name,' ;') AS stmt
 FROM information_schema.schemata
 WHERE schema_name LIKE 'prefix\_%' ESCAPE '\\'
 ORDER BY schema_name into outfile '/var/www/pardeep/file.txt';

if you get permission denied then change folder permission to 777 or change folder group to mysql using this 

chown -R mysql /var/www/pardeep/

then run this query

source /var/www/pardeep/file.txt;

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

Don't need of an external script file. A stored procedure using prepare statements might do the trick:

CREATE PROCEDURE kill_em_all()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE dbname VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT schema_name
      FROM information_schema.schemata
     WHERE schema_name LIKE 'database\_%' ESCAPE '\\'
     ORDER BY schema_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO dbname;

        IF done THEN
          LEAVE read_loop;
        END IF;

        SET @query = CONCAT('DROP DATABASE ',dbname);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
    END LOOP;
END;

Once you have that procedure, you just have to:

CALL kill_em_all();

When done:

DROP PROCEDURE kill_em_all

Upvotes: 5

Related Questions