-- 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';

# 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*

-- 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;

-- 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;

-- 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;

-- 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

-- 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;

# 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

#!/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