How can we rename the database name in MySQL 5.0

Question

I am using MySQL 5.0.

I have created a database named accounts, but now I want to change the database name to FinanceAccounts.

How can I change the database name in MySQL 5.0?

See http://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name — g ., Jun 24 '11 at 12:00

score 21 · Answer 1 · edited May 23 '17 at 12:02

I think there is only one way (besides renaming directory in the MySQL datadir which will fail for InnoDB tables):

create new database (with new name)
make dump of old database
import dumped data into new database
delete old database

To create the new DB:

mysql> CREATE DATABASE new_database;

To create the dump of the old DB:

mysqldump -u "your_username" -p --lock-tables old_database > old_database_dump.sql

To import dumped data into the new DB:

mysql -u "your username" -p new_database  < old_database_dump.sql

To delete the old DB:

mysql> DROP DATABASE old_database;

Bear in mind that your permissions on the old DB will need to be deleted as well. See here for more info: Revoke all privileges for all users on a MySQL DB

MySQL 5.1.7 to MySQL 5.1.22 had a RENAME {DATABASE | SCHEMA} db_name TO new_db_name; command but this one has been removed in MySQL 5.1.23 for being too dangerous.

score 18 · Answer 2 · edited Sep 07 '11 at 09:10

18

The best way is probably to rename each of the tables inside the database to the new name. For example:

Update: There are two steps here

Create a new blank database as you want say "new accounts"

CREATE DATABASE newaccounts;
Migrate each table one-by-one

RENAME TABLE accounts.tablename TO newaccounts.tablename;

See http://dev.mysql.com/doc/refman/5.0/en/rename-table.html for more information.

edited Sep 07 '11 at 09:10

Jirapong

24,074
10
54
72

answered Mar 27 '09 at 11:55

brian-brazil

31,678
6
93
86

We're talking about renaming a DATABASE... Renaming a table is no problem at all. – Stefan Gehrig Mar 27 '09 at 11:57
2

thats what he says: RENAME TABLE database_name.table_name TO new_database_name.table_name – ax. Mar 27 '09 at 11:58
That's correct - didn't think of that possibility. This method perhaps generate some issues with open transactions or current locks on that table. But nevertheless it's a solution. Sorry for my first comment! – Stefan Gehrig Mar 27 '09 at 12:09
1

from the mysql docs: 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: `RENAME TABLE current_db.tbl_name TO other_db.tbl_name;` Any privileges granted specifically for the renamed table or view are not migrated to the new name. They must be changed manually. – Pablo Marin-Garcia Sep 29 '10 at 16:02
works pretty much instantly, no matter the size of the tables or how many indexes, etc. much faster and more reliable than using mysqldump. – sbeam Oct 24 '12 at 13:43

score 2 · Answer 3 · answered Sep 27 '12 at 15:41

2

MySQL kinda sucks for this. The only solid reliable solution is to use phpMyAdmin.

Login > click Scheme > click "Operations" > find "Rename database to:" > write NewName > click "Go."

As simple as that. All permissions are carried over.

answered Sep 27 '12 at 15:41

spencerthayer

77
1
7

score 2 · Answer 4 · edited May 31 '13 at 10:09

here , I rename mydb database to ecommerce, you follow this steps, but usin phpmyadmin is to easy

CREATE DATABASE `ecommerce` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

RENAME TABLE `mydb`.`Articles` TO `ecommerce`.`Articles` ;

RENAME TABLE `mydb`.`Categories` TO `ecommerce`.`Categories` ;

RENAME TABLE `mydb`.`Utilisateurs` TO `ecommerce`.`Utilisateurs` ;

ALTER TABLE `Articles` ADD CONSTRAINT fk_Articles_Categories FOREIGN KEY ( Categorie_id ) REFERENCES Categories( id ) ON DELETE NO ACTION ON UPDATE NO ACTION ;

DROP DATABASE `mydb` ;

score 1 · Answer 5 · answered Feb 26 '19 at 11:28

1

To Rename MySQL Database name follow the following steps:

1) Click the database name 

2) Click at Operations from the top menu

3) Type new database name Under Rename database to:

answered Feb 26 '19 at 11:28

Nazmul Haque

720
8
13

How can we rename the database name in MySQL 5.0

5 Answers5