Many developers search for ways to rename MySQL databases, often stumbling upon the deprecated RENAME DATABASE command. Oracle removed this command from MySQL 5.1.23 due to its dangerous behavior - it could corrupt data if interrupted during execution or cause permission issues.

The safest method to rename a database involves:

1. Dumping the original database
2. Creating a new database with the desired name
3. Importing the data into the new database

Here's the complete process using command-line tools:

# 1. Create a dump of the original database
mysqldump -u username -p old_database > old_database_dump.sql

# 2. Create the new database
mysql -u username -p -e "CREATE DATABASE new_database"

# 3. Import the data
mysql -u username -p new_database < old_database_dump.sql

After migration, you'll need to update user privileges:

# Show current grants for old database
mysql -u username -p -e "SHOW GRANTS FOR 'user'@'host'"

# Recreate grants for new database
mysql -u username -p -e "GRANT ALL PRIVILEGES ON new_database.* TO 'user'@'host'"

For large databases or frequent renaming needs, consider this bash script:

#!/bin/bash
OLD_DB="old_database"
NEW_DB="new_database"
USER="username"
PASS="password"

# Dump old DB
mysqldump -u $USER -p$PASS $OLD_DB > temp_dump.sql

# Create new DB
mysql -u $USER -p$PASS -e "CREATE DATABASE $NEW_DB"

# Import data
mysql -u $USER -p$PASS $NEW_DB < temp_dump.sql

# Clean up
rm temp_dump.sql

For some cases, you might rename tables instead of the entire database:

mysql -u username -p -e "CREATE DATABASE new_database"
mysql -u username -p -e "RENAME TABLE old_database.table1 TO new_database.table1"

MySQL's RENAME DATABASE command was introduced in version 5.1.7 but quickly removed in 5.1.23 due to data integrity risks. Oracle's documentation explicitly warns against its use:

-- This syntax is DANGEROUS and no longer supported
RENAME DATABASE old_name TO new_name;

The safest method involves creating a new database and migrating all objects:

# Step 1: Create dump file
mysqldump -u root -p --routines --events old_db > old_db_dump.sql

# Step 2: Create new database
mysql -u root -p -e "CREATE DATABASE new_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Step 3: Import data
mysql -u root -p new_db < old_db_dump.sql

For large databases, consider this PHP script to handle the transition:

<?php
$db_host = 'localhost';
$db_user = 'admin';
$db_pass = 'secret';
$old_db = 'dev_project_x';
$new_db = 'production_app_v2';

try {
    $pdo = new PDO("mysql:host=$db_host", $db_user, $db_pass);
    
    // Create new database
    $pdo->exec("CREATE DATABASE $new_db");
    
    // Get all tables from old DB
    $tables = $pdo->query("SHOW TABLES FROM $old_db")->fetchAll(PDO::FETCH_COLUMN);
    
    foreach ($tables as $table) {
        $pdo->exec("RENAME TABLE $old_db.$table TO $new_db.$table");
    }
    
    // Handle views, procedures, etc.
    $pdo->exec("DROP DATABASE $old_db");
    
    echo "Database renamed successfully!";
} catch (PDOException $e) {
    die("Error: " . $e->getMessage());
}
?>

For zero-downtime migrations with active connections:

-- Create temporary users for both databases
CREATE USER 'temp_migrator'@'%' IDENTIFIED BY 'temp_pass';
GRANT ALL PRIVILEGES ON old_db.* TO 'temp_migrator'@'%';
GRANT ALL PRIVILEGES ON new_db.* TO 'temp_migrator'@'%';

-- Set up replication (simplified example)
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='localhost',
    SOURCE_USER='repl_user',
    SOURCE_PASSWORD='repl_pass',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=107;

For comparison, PostgreSQL allows direct renaming:

ALTER DATABASE old_name RENAME TO new_name;

This highlights MySQL's different architectural approach to database metadata operations.

• Always perform during low-traffic periods
• Create complete backups before proceeding
• Update all connection strings in application code
• Modify cron jobs and scheduled tasks
• Update database user privileges