Menghapus database adalah operation yang sangat berbahaya. Setelah menyaksikan accidental deletions yang hampir fatal, saya akan berbagi procedures yang wajib diikuti sebelum DROP DATABASE.

Pre-Deletion Checklist

1. Verifikasi Database yang Akan Dihapus

-- List semua databases
SHOW DATABASES;
-- Check database size
SELECT 
table_schema AS database_name,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_mb DESC;
-- Verify table count
SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db_to_delete';

2. Backup Sebelum Penghapusan

# Full backup
mysqldump --single-transaction --routines --triggers \
    db_to_delete > /backup/db_to_delete_backup_$(date +%Y%m%d_%H%M%S).sql
Backup dengan compression
mysqldump --single-transaction db_to_delete | gzip > \
/backup/db_to_delete
backup$(date +%Y%m%d_%H%M%S).sql.gz
Verify backup file
ls -lh /backup/db_to_delete
backup
.sql

3. Verifikasi Backup Integrity

-- Test restore ke temporary database
CREATE DATABASE test_restore;
Restore dari backup
mysql test_restore < /backup/db_to_delete_backup_YYYYMMDD_HHMMSS.sql
-- Verifikasi
USE test_restore;
SHOW TABLES;
SELECT COUNT(*) FROM table_name;
-- Cleanup test
DROP DATABASE test_restore;

Safe Deletion Process

1. Read-Only Mode

-- Set database ke read-only jika possible
-- (Tidak ada native per-database read-only, tapi bisa simulate)
-- Revoke write privileges
REVOKE INSERT, UPDATE, DELETE, CREATE, DROP ON db_to_delete.* FROM 'app_user'@'%';
FLUSH PRIVILEGES;

2. Double Verification

-- Step 1: Confirm correct database
SELECT DATABASE();  -- Should be db_to_delete
-- Step 2: List tables untuk final check
SHOW TABLES;
-- Step 3: Count rows dalam tables utama
SELECT 'users' AS table_name, COUNT(
) AS row_count FROM users
UNION ALL
SELECT 'orders', COUNT() FROM orders
UNION ALL
SELECT 'products', COUNT(*) FROM products;

3. Perform Deletion

-- FINAL CHECKLIST:
-- [ ] Backup verified
-- [ ] Correct database selected
-- [ ] Application connections terminated
-- [ ] Team approval obtained
-- Execute DROP
DROP DATABASE IF EXISTS db_to_delete;
-- Verifikasi deletion
SHOW DATABASES LIKE 'db_to_delete';
-- Should return empty set

Recovery Process (Jika Dibutuhkan)

1. Restore dari Backup

-- Create database baru
CREATE DATABASE db_restored;
-- Restore dari backup
-- Command line:
-- mysql db_restored < /backup/db_to_delete_backup_YYYYMMDD_HHMMSS.sql
-- Verifikasi restore
USE db_restored;
SHOW TABLES;
SELECT COUNT(*) FROM users;

2. Point-in-Time Recovery (Jika Ada Binlog)

# Restore base backup
mysql db_restored < /backup/db_to_delete_backup_base.sql
Apply binlog sampai sebelum drop
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
--stop-datetime="2024-01-15 14:30:00" \
/var/log/mysql/mysql-bin.000001 | mysql db_restored

Automation dengan Safety Checks

1. Safe Drop Script

#!/bin/bash
# safe_drop_database.sh
DB_NAME="$1"
BACKUP
DIR="/backup"
DATE=$(date +%Y%m%d%H%M%S)
if [ -z "$DB_NAME" ]; then
echo "Usage: $0 <database_name>"
exit 1
fi
echo "=== SAFE DATABASE DROP PROCEDURE ==="
echo "Database: $DB_NAME"
echo "Time: $(date)"
Confirm with user
echo "WARNING: This will permanently delete database $DB_NAME"
read -p "Are you sure? Type 'yes' to continue: " confirm
if [ "$confirm" != "yes" ]; then
echo "Aborted."
exit 1
fi
Backup
echo "Creating backup..."
mysqldump --single-transaction "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}
backup${DATE}.sql"
if [ $? -ne 0 ]; then
echo "Backup failed! Aborting."
exit 1
fi
echo "Backup created: $BACKUP_DIR/${DB_NAME}
backup${DATE}.sql"
Verify backup
echo "Verifying backup..."
if [ ! -s "$BACKUP_DIR/${DB_NAME}
backup${DATE}.sql" ]; then
echo "Backup file is empty! Aborting."
exit 1
fi
Final confirmation
read -p "Backup verified. Final confirmation: type 'DELETE' to drop database: " final_confirm
if [ "$final_confirm" != "DELETE" ]; then
echo "Aborted."
exit 1
fi
Drop database
echo "Dropping database..."
mysql -e "DROP DATABASE IF EXISTS `$DB_NAME`;"
if [ $? -eq 0 ]; then
echo "Database $DB_NAME dropped successfully."
echo "Backup location: $BACKUP_DIR/${DB_NAME}
backup${DATE}.sql"
else
echo "Drop failed!"
exit 1
fi

Best Practices

1. NEVER DROP tanpa backup
2. Verify backup integrity sebelum drop
3. Double/triple confirm database name
4. Use scripts dengan confirmations
5. Test restore process regular
6. Document semua deletions

Kesimpulan

Database deletion adalah operation yang sangat berisiko:
– Always backup: Sebelum deletion
– Verify: Backup integrity dan correct database
– Multiple confirmations: Prevent accidents
– Test restore: Know you can recover
– Document: Track what was deleted

Dengan proper procedures, accidental deletions dapat dihindari atau di-recover.