MySQL Drop Database

Overview

Dropping (deleting) a database is a permanent action that removes the database and all its contents including tables, views, indexes, and data. This action cannot be undone, so it should be performed with extreme caution.

Important Warnings

• Data Loss: All data will be permanently deleted
• Irreversible: Cannot be undone without backup
• Dependent Objects: Views, stored procedures, triggers will be deleted
• Access Impact: All user access will be removed

DROP DATABASE Statement

Basic Syntax

-- Drop a database
DROP DATABASE database_name;

-- Drop if exists (recommended)
DROP DATABASE IF EXISTS database_name;

Examples

-- Drop single database
DROP DATABASE old_database;

-- Safe drop with IF EXISTS
DROP DATABASE IF EXISTS unused_database;

-- Drop multiple databases
DROP DATABASE database1, database2, database3;

Methods to Drop Database

Using SQL Command

-- Connect to MySQL
mysql -u root -p

-- Drop database
DROP DATABASE IF EXISTS database_to_delete;

-- Verify deletion
SHOW DATABASES;

Using mysqladmin

# Drop database from command line
mysqladmin -u root -p drop database_name

# Confirm with 'y' when prompted

Using MySQL Workbench

1. Connect to MySQL server in MySQL Workbench
2. Right-click on the database in Navigator panel
3. Select "Drop Schema"
4. Confirm the action
5. Review objects to be dropped
6. Click "Drop Now"

Using Programming Languages

Python / Python

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password'
)

cursor = conn.cursor()

# Drop database
cursor.execute("DROP DATABASE IF EXISTS database_name")

conn.close()

PHP / PHP

<?php
$conn = new mysqli('localhost', 'username', 'password');

// Drop database
$conn->query("DROP DATABASE IF EXISTS database_name");

if ($conn->error) {
    echo "Error: " . $conn->error;
} else {
    echo "Database dropped successfully";
}

$conn->close();
?>

Pre-Drop Checklist

1. Backup Data

# Create backup before dropping
mysqldump -u root -p database_name > backup_file.sql

# For large databases
mysqldump -u root -p database_name | gzip > backup_file.sql.gz

2. Check Dependencies

-- Check for foreign key references
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'table_in_database';

-- Check for views
SELECT TABLE_NAME 
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'database_name';

-- Check for stored procedures
SELECT ROUTINE_NAME 
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'database_name';

-- Check for triggers
SELECT TRIGGER_NAME 
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'database_name';

3. Check Active Connections

-- Check current connections to database
SHOW PROCESSLIST;

-- Kill active connections
KILL connection_id;

4. Notify Users

Before dropping production databases:

• Notify application teams
• Schedule maintenance window
• Document the change

5. Verify Database

-- Confirm database exists
SHOW DATABASES LIKE 'database_name';

-- Get database size
SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'database_name';

-- List all tables
SHOW TABLES FROM database_name;

Safe Drop Procedures

Step-by-Step Process

-- Step 1: Backup database
\! mysqldump -u root -p database_name > backup_$(date +%Y%m%d).sql

-- Step 2: Rename instead of drop (safer)
RENAME DATABASE database_name TO database_name_backup;

-- Step 3: Wait period (observe if anything breaks)
-- This allows recovery if needed

-- Step 4: After confirmation, drop
DROP DATABASE IF EXISTS database_name_backup;

Using Transaction

-- Rename within transaction (for recoverable operation)
RENAME DATABASE old_database TO old_database_backup;
-- If issue arises, rename back
-- RENAME DATABASE old_database_backup TO old_database;
-- Once confirmed safe, drop
DROP DATABASE IF EXISTS old_database_backup;

Automatic Drop with Confirmation

Bash Script with Safety Checks

#!/bin/bash
# drop_database.sh

DATABASE_NAME=$1

if [ -z "$DATABASE_NAME" ]; then
    echo "Usage: $0 <database_name>"
    exit 1
fi

echo "=== Database Drop Safety Check ==="
echo "Database: $DATABASE_NAME"
echo ""

# Check if database exists
if ! mysql -u root -p -e "USE $DATABASE_NAME" 2>/dev/null; then
    echo "Error: Database '$DATABASE_NAME' does not exist"
    exit 1
fi

# Get database size
SIZE=$(mysql -u root -p -N -e "
SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2)
FROM information_schema.tables
WHERE table_schema = '$DATABASE_NAME';")

echo "Database size: ${SIZE} MB"
echo ""

# Check for active connections
CONNECTIONS=$(mysql -u root -p -N -e "
SELECT COUNT(*) FROM information_schema.processlist
WHERE db = '$DATABASE_NAME';")

echo "Active connections: $CONNECTIONS"
echo ""

# Get table count
TABLES=$(mysql -u root -p -N -e "
SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema = '$DATABASE_NAME';")

echo "Tables to be dropped: $TABLES"
echo ""

# Confirmation
read -p "Are you sure you want to drop '$DATABASE_NAME'? (type 'YES' to confirm): " CONFIRM

if [ "$CONFIRM" != "YES" ]; then
    echo "Drop cancelled"
    exit 1
fi

# Create backup
BACKUP_FILE="backup_${DATABASE_NAME}_$(date +%Y%m%d_%H%M%S).sql"
echo "Creating backup: $BACKUP_FILE"
mysqldump -u root -p $DATABASE_NAME > $BACKUP_FILE

if [ $? -ne 0 ]; then
    echo "Backup failed. Aborting drop."
    exit 1
fi

# Drop database
echo "Dropping database..."
mysql -u root -p -e "DROP DATABASE IF EXISTS $DATABASE_NAME"

if [ $? -eq 0 ]; then
    echo "Database '$DATABASE_NAME' dropped successfully"
    echo "Backup saved to: $BACKUP_FILE"
else
    echo "Failed to drop database"
    exit 1
fi

Usage

./drop_database.sh my_database

Common Errors and Solutions

Error: Database Doesn't Exist

-- Error: Unknown database 'database_name'
-- Solution: Use IF EXISTS
DROP DATABASE IF EXISTS database_name;

-- Verify database name
SHOW DATABASES LIKE '%database%';

Error: Access Denied

-- Error: Access denied for user to drop database
-- Solution: Need DROP privilege
GRANT DROP ON *.* TO 'username'@'host';
FLUSH PRIVILEGES;

Error: Database in Use

-- Error: Database is being used by other processes
-- Solution: Terminate connections first
SELECT GROUP_CONCAT(id) FROM information_schema.processlist 
WHERE db = 'database_name' AND id != CONNECTION_ID();

-- Kill connections
KILL connection_id1, connection_id2;

Error: Foreign Key Constraints

-- Error: Cannot drop database because of foreign keys
-- Solution: Drop tables in correct order or use CASCADE
-- (MySQL doesn't support CASCADE for DROP DATABASE)

-- Check foreign keys
SELECT 
    TABLE_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'database_name';

-- Drop dependent objects first

Restoring Dropped Database

From Backup

# Restore from SQL dump
mysql -u root -p < backup_file.sql

# Restore from compressed backup
gunzip < backup_file.sql.gz | mysql -u root -p

Point-in-Time Recovery

# If binary logging is enabled
mysqlbinlog --stop-datetime="2024-01-15 10:00:00" /var/log/mysql/bin.* | mysql -u root -p

Best Practices

Naming for Safety

-- Use naming convention to avoid accidental drops
CREATE DATABASE old_app_2024_01;     -- Clear this is old
CREATE DATABASE archive_deprecated;   -- Clear this is deprecated

-- Avoid generic names
CREATE DATABASE app;           -- Risky
CREATE DATABASE my_application;  -- Better

Protection Measures

-- Rename instead of immediate drop
RENAME DATABASE production TO production_backup;

-- Wait period before actual drop
-- If no issues after period, then drop
DROP DATABASE IF EXISTS production_backup;

Documentation

-- Document dropped databases
-- Keep log of:
-- - Database name
-- - Drop date
-- - Reason for drop
-- - Backup location
-- - Approval

Summary

Dropping databases requires careful consideration:

• Safety First: Always backup before dropping
• IF EXISTS: Prevent errors with conditional drop
• Check Dependencies: Verify no active connections
• User Notification: Inform stakeholders
• Documentation: Keep records of dropped databases

---

Previous: Create Database

Next: Select Database