How do I change the database name using MySQL

Question

How can I change the database name of my database   I tried to use the rename database command  but on the documents about it it is said that it is dangerous to use  Then what should I need to do to rename my database name   For example  if I want to rename my database to this   database1 - gt  database2

User · Answer

I agree with above answers and tips but there is a way to change database name with phpmyadmin

Renaming the Database From cPanel, click on phpMyAdmin. (It should open in a new tab.) Click on the database you wish to rename in the left hand column. Click on the Operations tab. Where it says "Rename database to:" enter the new database name. Click the Go button. When it asks you to want to create the new database and drop the old database, click OK to proceed. (This is a good time to make sure you spelled the new name correctly.) Once the operation is complete, click OK when asked if you want to reload the database.

here's the video tutorial:

http://support.hostgator.com/articles/specialized-help/technical/phpmyadmin/how-to-rename-a-database-in-phpmyadmin

User · Answer

You can change the database name using MySQL interface   Go to http   www hostname com phpmyadmin  Go to database which you want to rename  Next  go to the operation tab  There you will find the input field to rename the database

User · Answer

Follow bellow steps   shell gt  mysqldump -hlocalhost -uroot -p  database1   gt  dump sql  mysql gt  CREATE DATABASE database2   shell gt  mysql -hlocalhost -uroot -p database2  lt  dump sql   If you want to drop database1 otherwise leave it   mysql gt  DROP DATABASE database1    Note   shell  denote command prompt and mysql  denote mysql prompt

User · Answer

InnoDB supports RENAME TABLE statement to move table from one database to another  To use it programmatically and rename database with large number of tables  I wrote a couple of procedures to get the job done  You can check it out here - SQL script  Gist  To use it simply call the renameDatabase procedure   CALL renameDatabase  old name    new name      Tested on MariaDB and should work ideally on all RDBMS using InnoDB transactional engine

User · Answer

Try   RENAME database1 TO database2

User · Answer

Another way to rename the database or taking an image of the database is by using the reverse engineering option in the database tab  It will create an ER diagram for the database  Rename the schema there   After that  go to the File menu and go to export and forward engineer the database   Then you can import the database

User · Answer

After much aggravation this is what I have found to work simply   First thing  I am using MYSQL Workbench and the import would not work as it should  as the import dump file would always revert to the original schema name   I spent several hours trying every thing to no avail all for a spelling error   I solved the issue by opening one of the  sql dump files in notebook and hand editing the typo s of the schema name  take care to rename all instances schema name has three in the beginning  save the file and then import  this worked perfectly for me and hope that it will help others looking for the simple answer to changing database names schema names  One more tip that I have found true  when programs do not do as they should go to the  source  literally find the source code    Hope this helps someone  Low rep so they wont let me comment on the prior post answer it keeps changing rank or position   so I added it here  reverse engineering will work fine as long as there is no data in the sever table  if data exists and you try to update the server after the name change it will either pull an error or just create a new database schema with no data  I know I tried ten times to no avail  The above works simply and avoids headaches  as one can review the SQL code for other errors if any or change table names or creation data  the  sql file is just a compiled SQL code so in theory one could copy and add it through PHP or the script console of the database management tool

User · Answer

As long as two databases are on the same file system  you can use RENAME TABLE to move a table from one database to another   -- ensure the char set and collate match the existing database  SHOW VARIABLES LIKE  character set database   SHOW VARIABLES LIKE  collation database     CREATE DATABASE  database2  DEFAULT CHARACTER SET    utf8  DEFAULT COLLATE    utf8 general ci   RENAME TABLE  database1   table1  TO  database2   table1   RENAME TABLE  database1   table2  TO  database2   table2   RENAME TABLE  database1   table3  TO  database2   table3     http   dev mysql com doc refman 5 7 en rename-table html

User · Answer

Go to data directory and try this   mv database1 database2    It works for me on a 900 MB database size

User · Answer

Unfortunately  MySQL does not explicitly support that  except for dumping and reloading database again   From http   dev mysql com doc refman 5 1 en rename-database html   13 1 32  RENAME DATABASE Syntax RENAME  DATABASE   SCHEMA  db name TO new db name  This statement was added in MySQL 5 1 7 but was found to be dangerous and was removed in MySQL 5 1 23      Use of this statement could result in loss of database contents  which is why it was removed  Do not use RENAME DATABASE in earlier versions in which it is present

User · Answer

I don t think it s possible    You can use mysqldump to dump the data and then create a schema with your new name and then dump the data into that new database

User · Answer

You can use below command  alter database Testing modify name LearningSQL   Old Database Name   Testing  New Database Name   LearningSQL

[mysql] How do I change the database name using MySQL?

Examples related to mysql