Good friends. Over time, it seems necessary to rename a database in MySQL. For that purpose, we used to apply a simple RENAME DATABASE command that existed in previous versions of MySQL. However, it was no longer available in the newer versions because it presented risks. The reason was that the RENAME DATABASE query could result in the loss of essential content from the database, which was clearly not desirable.
Method to rename a database in MySQL
There is a way to accomplish this task quite easily. In fact, MySQL version 5.5 (and later) has InnoDB Storage Engine as the default engine, which can be useful.
Simply put, we can apply the RENAME TABLE command inside a MySQL prompt to alter the database name from a particular table without changing the table name. But to do it, first we need to create a new database with the following command mysqladmin shell:
$ mysqladmin -u username -p "password" create newDbname
Now that we have created an empty database, we need move tables one by one from old database to database just created with the following command:
RENAME TABLE oldDbname.table TO newDbname.table;
As you can see, the query allows us to move the tables only one at a time, which is not very practical for large databases. Also, the RENAME TABLE command does not work for views and triggers. Instead executing the aforementioned statement, we would have to remove them and create them again.
Dumping Method
Another way to rename a database in MySQL would use the mysqldump shell command. In this way, we can create a dumped copy of the database and import all the contents of the database data to the new database. After that, we can remove the base from above data if necessary.
$ mysqldump -u username -p "password" -R oldDbname> oldDbname.sql
Next, we need to use the command mentioned above to create a new database:
$ mysqladmin -u username -p "password" create newDbname
Finally, we need to import the dump file created in the first step to the new database:
mysql -u username -p "password" newDbname <oldDbname.sql
If you need help with this or another operation, do not hesitate to contact us !! Until next time! You can see more information about RENAME TABLE in MySQL: https://dev.mysql.com/doc/refman/5.7/en/rename-table.html
Do not hesitate to sign up for our monthly newsletter with only one email per month. You will be attentive to all our entries.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn