Rename MySQL database

Question

I created a database with the name of hrms. Now I need to change database name to sunhrm. But, It is disabled in MySQL workbench. Can I do that on the Linux server itself?

Also on serverfault: http://serverfault.com/questions/195221/how-to-rename-a-mysql-database — Yves Martin, Aug 14 '13 at 06:05
Hopefully MySQL will implement a new, working `RENAME DATABASE` statement that doesn't have any dangers, as there is no easy way to do this task currently. **There is no obvious reason why it was dangerous in the [documentation](http://dev.mysql.com/doc/refman/5.1/en/rename-database.html) so they should be able to make a replacement.** At least people have put feature request bugs on their website. For example, http://bugs.mysql.com/bug.php?id=58593 and http://bugs.mysql.com/bug.php?id=1698. — Edward, Apr 15 '16 at 16:05

score 71 · Answer 1 · answered Dec 19 '12 at 03:05

71

In case you need to do that from the command line, just copy, adapt & paste this snippet:

mysql -e "CREATE DATABASE \`new_database\`;"
for table in `mysql -B -N -e "SHOW TABLES;" old_database`
do 
  mysql -e "RENAME TABLE \`old_database\`.\`$table\` to \`new_database\`.\`$table\`"
done
mysql -e "DROP DATABASE \`old_database\`;"

answered Dec 19 '12 at 03:05

jan

1,160
1
9
11

21

Note that this only handles the tables. Views and stored procedures have to be done separately. – Greg Jan 28 '13 at 19:21
6

Bit easier to use version of this script: https://gist.github.com/tadas-s/5411299 – Tadas Sasnauskas Apr 18 '13 at 08:59
1

Here is an alternate way to create rename table command in SQL within MySQL directy: http://blog.marceloaltmann.com/how-to-rename-a-database-in-mysql/ – Yves Martin Aug 14 '13 at 06:04
The gist at gist.github.com/tadas-s/5411299 worked fine for me. However, it would be nice if it could handle views and would take some command-line arguments. – bean5 Nov 24 '13 at 05:39
This also doesn't transfer permissions on new database.. other than that it worked for me (I don't have views and procedures) – davidhq May 28 '15 at 11:41
Am I missing something? Why not create the database and **simply use `mysqldump -R -uroot -p old_db|mysql -uroot -p new_db`?** – Pacerier Oct 12 '15 at 09:57
1

DROP the TRIGGERs first (if any) !!!! – TVNshack Jan 19 '17 at 13:17

New Alexandria · Accepted Answer · 2020-12-10T12:54:10.450

65

I don't think you can do this. Basic answers will work in many cases, and in others cause data corruptions. A strategy needs to be chosen based on heuristic analysis of your database. That is the reason this feature was implemented, and then removed. [doc]

You'll need to dump all object types in that database, create the newly named one and then import the dump. If this is a live system you'll need to take it down. If you cannot, then you will need to setup replication from this database to the new one.

If you want to see the commands that could do this, @satishD has the details, which conveys some of the challenges around which you'll need to build a strategy that matches your target database.

edited Dec 10 '20 at 12:54

answered Aug 30 '12 at 04:59

New Alexandria

6,951
4
57
77

13

Does it make sense? Why MySQL developer omitted such a important option? – Milad Rahimi Jan 19 '16 at 18:30
This could be a reason for marketing. Many MySQL hosting providers are offering you a free but randomly and banal named database name but if you wish to rename it to something simple and readable, then you pretty much have to pay for it. – knoxgon Apr 11 '17 at 13:30

score 37 · Answer 3 · edited Feb 03 '18 at 02:43

You can create a new database exactly as the previous database existed and then drop the old database when you're done. Use the mysqldump tool to create a .sql backup of the database via mysqldump orig_db > orig_db.sql or if you need to use a username and password then run mysqldump -u root -p orig_db > orig_db.sql. orig_db is the name of the database you want to "rename", root would be the user you're logging in as and orig_db.sql would be the file created containing the backup. Now create a new, empty database with the name you want for the database. For example, mysql -u root -p -e "create database new_db". Once that's done, then run mysql -u root -p new_db < orig_db.sql. new_db now exists as a perfect copy of orig_db. You can then drop the original database as you now have it existing in the new database with the database name you wanted.

The short, quick steps without all the above explanation are:

mysqldump -u root -p original_database > original_database.sql
mysql -u root -p -e "create database my_new_database"
mysql -u root -p my_new_database < original_database.sql
mysql -u root -p -e drop database originl_database

Hope this helps and this is a reliable means to accomplish it without using some ad-hoc method that will corrupt your data and create inconsistencies.

concise, safe, worked best for me (on simple DBs without triggers) — foo, Sep 25 '14 at 12:22
Just create the database and pipe the results from one directly to the other: `mysqldump -u root -p old_db|mysql -u root -p new_db`. — Pacerier, Oct 12 '15 at 09:55
You may have to edit the `original_database.sql` file and comment out the `CREATE DATABASE …` and `USE …` statements at the top, to avoid creating/using the original database. — Jens, Feb 03 '18 at 02:42

score 36 · Answer 4 · edited Apr 05 '15 at 13:29

36

It's possible to copy database via mysqldump command without storing dump into file:

mysql -u root -p -e "create database my_new_database"
mysqldump -u root -p original_database | mysql -u root -p my_new_database
mysql -u root -p -e "drop database original_database"

edited Apr 05 '15 at 13:29

evandrix

6,041
4
27
38

answered Jul 24 '13 at 14:13

Hryhorii Hrebiniuk

526
4
4

Cristian Porta · Answer 5 · 2012-08-30T05:42:42.573

23

You can do it by RENAME statement for each table in your "current_db" after create the new schema "other_db"

RENAME TABLE current_db.tbl_name TO other_db.tbl_name

Source Rename Table Syntax

edited Aug 30 '12 at 05:42

answered Aug 30 '12 at 05:17

Cristian Porta

4,393
3
19
26

14

Hi mikeslattery, my statement is a RENAME TABLE not a RENAME DATABASE or SCHEMA – Cristian Porta Mar 08 '13 at 05:58
Pffft, oops. Sorry. :) – mikeslattery Mar 09 '13 at 21:02

score 4 · Answer 6 · answered Aug 30 '12 at 04:49

In short no. It is generally thought to be too dangerous to rename a database. MySQL had that feature for a bit, but it was removed. You would be better off using the workbench to export both the schema and data to SQL then changing the CREATE DATABASE name there before you run/import it.

score 4 · Answer 7 · edited Sep 11 '13 at 05:31

4

I used following method to rename the database

take backup of the file using mysqldump or any DB tool eg heidiSQL,mysql administrator etc
Open back up (eg backupfile.sql) file in some text editor.
Search and replace the database name and save file.
Restore the edited SQL file

edited Sep 11 '13 at 05:31

giampaolo

6,906
5
45
73

answered Sep 11 '13 at 05:11

Adarsha

41
1

Don't forget to create the new Database which you want to import the data. In other words, the "renamed" database. – Stévillis Apr 03 '21 at 17:51

score 2 · Answer 8 · answered Apr 02 '14 at 07:17

If your DB contains only MyISAM tables (do not use this method if you have InnoDB tables):

shut down the MySQL server
go to the mysql data directory and rename the database directory (Note: non-alpha characters need to be encoded in a special way)
restart the server
adjust privileges if needed (grant access to the new DB name)

You can script it all in one command so that downtime is just a second or two.

score 1 · Answer 9 · answered Jan 12 '13 at 17:50

1

For impatient mysql users (like me), the solution is:

/etc/init.d/mysql stop
mv /var/lib/mysql/old_database /var/lib/mysql/new_database 
/etc/init.d/mysql start

answered Jan 12 '13 at 17:50

user1972813

45
1
5

I tried this. I moved `my_project` to `my_project_bak`, then created a new blank database called `my_project`. I was unable to create a table in the new blank database when it had the same name as a table in `my_project_bak`. – Greg Jan 28 '13 at 16:42
1

While this was very easy and quick to do and seemed to work, it did cause some inconsistencies, probably due to innodb – tobixen Feb 04 '13 at 09:55
you have to replace all privileges in the system tables (db mysql), this one is for the database privileges `use mysql; update db set Db=newdbname where Db=olddbname` – Felipe Buccioni Mar 17 '14 at 19:28
4

Do **NOT** do this if you have InnoDB tables. Use this method only if all your tables are MyISAM tables. – rustyx Apr 02 '14 at 07:38

score 0 · Answer 10 · answered Dec 24 '12 at 04:21

0

First backup the old database called HRMS and edit the script file with replace the word HRMS to SUNHRM. After this step import the database file to the mysql

answered Dec 24 '12 at 04:21

Dhileepan

2,037
8
24
28

score 0 · Answer 11 · answered Mar 07 '14 at 10:24

Another way to rename the database or taking image of the database is by using Reverse engineering option in the database tab. It will create a ERR diagram for the database. Rename the schema there.

after that go to file menu and go to export and forward engineer the database.

Then you can import the database.

Rename MySQL database

11 Answers11

Linked