How to delete tables in MySQL or MariaDB

Dropping obsolete tables in MySQL or MariaDB keeps a schema clean, reduces backup and replication churn, and removes unused data that can complicate migrations or compliance.

Tables are schema objects stored inside a database, and the DROP TABLE statement removes the table definition and its rows while releasing related indexes and storage. Because it is DDL, the server takes a metadata lock so concurrent sessions cannot use the table while the change is applied.

The operation is effectively irreversible, and DDL is a one-way door without a restore path from backups or exports. Dropping the wrong table is most common when the wrong database is selected or when names look similar, so confirming the active database and exact table name before issuing DROP TABLE avoids expensive mistakes.

1. Create a logical backup of the target table if recovery might be required.

   $ mysqldump --user username --password --host db.example.net --port 3306 --result-file=table_name-backup.sql database_name table_name
   Enter password:

   MariaDB installations may provide mariadb-dump as an equivalent of mysqldump.

2. Connect to the server with the interactive client.

   $ mysql --user username --password --host db.example.net --port 3306
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   ##### snipped #####
   mysql>

3. Select the database that contains the table.

   mysql> USE database_name;
   DATABASE changed

   Dropping a table acts on the currently selected database unless the table name is fully qualified (database_name.table_name).

4. Confirm the currently selected database.

   mysql> SELECT DATABASE();
   +------------+
   | DATABASE() |
   +------------+
   | database_name |
   +------------+
   1 ROW IN SET (0.00 sec)

5. Confirm the target table name.

   mysql> SHOW TABLES LIKE 'table_name';
   +------------------------+
   | Tables_in_database_name |
   +------------------------+
   | TABLE_NAME             |
   +------------------------+
   1 ROW IN SET (0.00 sec)

   Use backticks (`) around identifiers that contain dashes or reserved words, e.g., DROP TABLE `order`;.

6. Drop the table.

   mysql> DROP TABLE TABLE_NAME;
   Query OK, 0 ROWS affected (0.05 sec)

   DROP TABLE permanently deletes the table and its data, and the practical rollback is a backup restore. Use DROP TABLE IF EXISTS table_name; when scripting, and treat foreign-key errors like ERROR 1217 or ERROR 1451 as a signal that dependent tables or constraints exist.

7. Verify the table no longer exists.

   mysql> SHOW TABLES LIKE 'table_name';
   Empty SET (0.00 sec)

8. Exit the client session.

   mysql> EXIT;
   Bye