How to Delete a MySQL Database

Introduction

It’s been a long time since I wrote a blog post. Just want to share something casual. Companies always have numerous databases that need constant syncing and temporary usage. Some projects get shut down online but their databases remain undeleted. These databases become “Schrodinger’s databases” - they may never be used again if not deleted, but as soon as you delete them, someone will inevitably complain in the group chat the next day.

Operation

To handle this situation, I created a script:

#!/bin/bash
# Define database username and password
DB_USER=""
DB_PASS=""
DB_HOST=""
DB_PORT=""
DB_NAME=""
NEW_DB=arc_${DB_NAME}
# Create target database (if not exists)
mysql -u${DB_USER} -p${DB_PASS} -h ${DB_HOST} -P ${DB_PORT} -e "CREATE DATABASE IF NOT EXISTS \`$NEW_DB\`"

# Retrieve all table names from the source database
list_table=$(mysql -u${DB_USER} -p${DB_PASS} -h ${DB_HOST} -P ${DB_PORT} --batch --skip-column-names -e \
    "SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DB_NAME'")

# Loop through table names and rename tables
for table in $list_table; do
    echo "Processing table: $table"
    mysql -u${DB_USER} -p${DB_PASS} -h ${DB_HOST} -P ${DB_PORT} -e "RENAME TABLE \`$DB_NAME\`.\`$table\` TO \`$NEW_DB\`.\`$table\`"
    if [ $? -eq 0 ]; then
        echo "Successfully renamed table: $table"
    else
        echo "Failed to rename table: $table" >&2
    fi
done

First, create an archive database and move all tables into it. Then wait for three months. If no one complains in the group chat during this period, you can safely delete the archive database. If someone does complain, simply move the tables back from the archive database to the original one.

Feel free to follow my blog at www.bboy.app

Have Fun

Bboysoul's Blog

How to Delete a MySQL Database

Introduction

Operation