How to update the MySQL database or how can we change the name of our existing MySQL database. In this tutorial, We will cover all the possible ways to change the name of the existing MYSQL database.
Sometimes, there is a situation arises when we want to change the name of the database. So for this, an earlier version of MYSQL 5.1.7 provided the option to alter the name of the database but in the later version of MYSQL 5.1.23, this option was removed due to some security concerns.
If you want to alter the name of the database we have some alternate solution that we will discuss here:
- First, we will use a MySQL Workbench.
- Next, we will use a command prompt.
How to change the name of the database using MYSQL Workbench?
- Open MYSQL Workbench.
- Write a query in the query tab to create a new database in which you want to move all the tables and execute them.
create database new_codebun;
- After this, Write the following query to move all the tables from the old database to the new database.
Rename table codebun.c_user to new_codebun.c_user;
Now all your tables from the old database will be moved to the new one also you can drop the old one. So, this could be one possible way to rename the database. Next, we will see with command prompt.
How to change the name of the database using Command-Line?
Here, we will rename the database using mysqldump.
- Go to Start. Go to the command prompt. Right-click on it and run as administrator.
- change the directory to the folder where your MYSQL is installed and go to the bin folder. like in my case it is (C:\Program Files\MySQL\MySQL Server 8.0\bin).
/***************Write this command************************\ C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -u [username] -p -R [DatabaseName]> [DatabaseName].sql Enter password:
- Write the above command as it, just replace the [username] with the username which you provide during login without square bracket and Database Name with the database you’re altering and hit enter without semicolon. Then, it will ask you to input the password, enter it, and hit enter.
- Here, -u is for username, -p is for the password, and -R is for all the stored procedures and functions.
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqladmin -u [username] -p create [newDBname] Enter password: ****
- Execute this command and replace [username] with your credential and [newDBname] with the new Database name.
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u [username] -p [newDBName]< [oldDBName].sql Enter password: ****
- Execute the above command and now it will load all your data from the old table to the new.
- Now, just check with the following commands also you can connect to MYSQL and verify.
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u [username] -p Enter password: ****
Example:
Thus, this is how we can rename a database using MYSQL Workbench or Command Prompt.
