MySQL Drop Database

Last updated: October 19, 2026Oct 19, 2026 4 min read

DROP DATABASE permanently deletes a database together with every table, row, view, stored procedure, trigger and event in it. It is one of the most powerful and dangerous statements in SQL, so this tutorial covers both how to use it and how to use it safely.

1. What Gets Deleted

• All tables in the database and all their data and indexes.
• Views, stored procedures, functions, triggers and events defined in it.
• The database directory and files on disk.
• Not deleted: user accounts, and privileges granted on the database (they remain until revoked, and would apply again if a database with the same name is created).

2. Syntax

DROP {DATABASE | SCHEMA} [IF EXISTS] database_name;

DROP SCHEMA is a synonym. The statement reports how many tables and views it removed.

3. Dropping a Database

Look at what is inside shop_test first:

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'shop_test';

Result: 3 rows

Now drop it:

DROP DATABASE shop_test;

Query OK, 3 rows affected

"3 rows affected" is the number of table objects removed: the two tables and the view. The database is gone:

SHOW DATABASES LIKE 'shop%';

Result: Empty set (0 rows)

4. Using IF EXISTS

Dropping a database that doesn't exist is an error:

DROP DATABASE shop_test;

ERROR 1008 (HY000): Can't drop database 'shop_test'; database doesn't exist

IF EXISTS turns the error into a note, which is useful in setup and clean-up scripts:

DROP DATABASE IF EXISTS shop_test;
SHOW WARNINGS;

Query OK, 0 rows affected, 1 warning

Result: 1 row

5. There Is No Undo

DROP DATABASE is a DDL statement, and MySQL commits DDL immediately. Wrapping it in a transaction doesn't help:

START TRANSACTION;
DROP DATABASE old_reports;
ROLLBACK;
SHOW DATABASES LIKE 'old_reports';

Result: Empty set (0 rows)

6. Safety Checklist Before Dropping

1. Confirm the server: run SELECT @@hostname, @@port;. Many disasters happen on production while someone thought they were on a test server.
2. Confirm the name: type it carefully; avoid copy-pasting from chat messages.
3. Check what is inside: count tables and rows in information_schema.tables.
4. Back it up: mysqldump -u root -p --routines --events --triggers dbname > dbname.sql.
5. Check who uses it: look for application connections in SHOW PROCESSLIST.
6. Restrict the privilege: give the DROP privilege only to administrators.

# back up, then drop, then (if needed) restore
mysqldump -u root -p --routines --events --triggers --databases old_reports > old_reports.sql
mysql -u root -p -e "DROP DATABASE old_reports;"
mysql -u root -p < old_reports.sql      # restore if you change your mind

7. Privileges Needed

You need the DROP privilege on the database. A read-only user can't drop anything:

CREATE DATABASE analytics;
CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'An@lyst2026';
GRANT SELECT ON analytics.* TO 'analyst'@'localhost';
SHOW GRANTS FOR 'analyst'@'localhost';

Result: 2 rows

Logged in as analyst, the drop is refused:

DROP DATABASE analytics;

ERROR 1044 (42000): Access denied for user 'analyst'@'localhost' to database 'analytics'

8. Leftover Privileges

Privileges granted on a database survive the drop. If someone later creates a database with the same name, the old users get access again automatically. Clean them up:

DROP DATABASE analytics;
SHOW GRANTS FOR 'analyst'@'localhost';   -- the grant is still there
REVOKE SELECT ON analytics.* FROM 'analyst'@'localhost';
SHOW GRANTS FOR 'analyst'@'localhost';

Grants after DROP DATABASE

Grants after REVOKE

9. Common Errors

10. Summary

• DROP DATABASE name; removes the database and everything in it.
• IF EXISTS avoids an error when the database is missing.
• It cannot be rolled back; take a backup first and double-check the server and name.
• User accounts and database-level grants remain; revoke them if no longer needed.
• Only trusted administrators should have the DROP privilege.