How to rename database in MySQL

In MySQL, you cannot directly rename a database using a single SQL statement like you can with tables. However, you can achieve the same effect by following these steps:

1. Create a New Database: First, create a new database with the desired name using the CREATE DATABASE statement. You can specify the desired character set and collation if needed.

   Replace new_database_name with the desired name for your database.

   <span class="pln">
   CREATE DATABASE new_database_name
       CHARACTER SET utf8mb4
       COLLATE utf8mb4_unicode_ci</span><span class="pun">;</span><span class="pln">
   </span>

2. Transfer Data: Next, transfer the data from the old database to the new one. You can do this by exporting the data from the old database and importing it into the new one. You can use the mysqldump command-line tool to export the data and the mysql command-line client to import it.

   <span class="pln">
   mysqldump </span><span class="pun">-</span><span class="pln">u username </span><span class="pun">-</span><span class="pln">p old_database_name </span><span class="pun">&gt;</span><span class="pln"> backup</span><span class="pun">.</span><span class="pln">sql
   </span>

   <span class="pln">
   mysql </span><span class="pun">-</span><span class="pln">u username </span><span class="pun">-</span><span class="pln">p new_database_name </span><span class="pun">&lt;</span><span class="pln"> backup</span><span class="pun">.</span><span class="pln">sql
   </span>

   Replace username with your MySQL username, old_database_name with the name of your old database, and backup.sql with the desired name for your backup file.

3. Drop Old Database: Once you've transferred the data, you can drop (delete) the old database using the DROP DATABASE statement.

   <span class="pln">
   DROP DATABASE old_database_name</span><span class="pun">;</span><span class="pln">
   </span>

   Replace old_database_name with the name of your old database.

4. By following these steps, you effectively rename your database from the old name to the new one. However, make sure to take appropriate backups and ensure that there are no active connections or operations on the database during the renaming process to avoid data loss or corruption.