DelphiFAQ Home Search:

mysql: How can I rename a database

 

comments15 comments. Current rating: 4 stars (8 votes). Leave comments and/ or rate it.

Question:

I want to rename my database from 'TEST' to something more meaningful. Do I have to make a backup, modify the SQL script and then restore it?

Answer:

While that would work, there is an easier way. Shutdown the mysql server, rename the directory that stores the database from 'TEST' to your desired name. This 'TEST' directory is below the data directory. If you don't know where that is, try:

$ mysqladmin variables | grep datadir

After renaming, restart the MySQL server and check the grants - they might still contain references to the old database name.

You can also use a tool like phpMyAdmin (web interface to mysql) which has an option to rename a database.


Comments:

2006-02-24, 18:13:55
anonymous from Hungary  
rating
2006-03-13, 00:51:59
anonymous from United States  
rating
Worked perfectly!

If you're renaming the DB by command line, you'll want to grant user permissions to the new DB and delete permissions granted for the old DB. At a minimum, you'll need to run the following SQL:

remove from mysql.db where db='oldDBname';
2006-04-04, 07:02:07
[hidden] from Italy  
try this..
$ mysqladmin create new_db
$ mysqldump --opt old_db | mysql new_db

mysql>; revoke all from old_db_user@host;
2008-01-21, 07:49:01
anonymous  
I think that renaming the directory will cause trouble for those of you using stored procedures.
2008-05-30, 01:01:25
anonymous from Australia  
rating
not being able to just rename a schema blows ass
2008-08-12, 02:43:25   (updated: 2008-08-12, 02:52:59)
armmani2001@yahoo.com from United States  
I tried to shutdown mysql and rename the database in data folder. apperently it worked but when I tried to use the tables it gave me the error the tables does not exist !


Keywords:
2009-02-15, 07:27:24
[hidden] from United Kingdom  
rating
Provided that you can shutdown anything which is updating the database (so that there is no chance of anything changing), this is a good solution:


mysql -p
<enter password>
create database new_database;
quit


mysqldump old_database -p | mysql -D new_database -p
<enter password>

It'll dump out the old database to STDOUT, pipe this to a second MySQL process and insert the dumped data (and schema) to the new DB.

You'll have to manually DROP the old DB and change the permissions on the new DB, but at least you have the data and schema.

I find this very useful for creating 'live' backups of databases.
2009-03-20, 11:58:05
anonymous from Spain  
you're a crack!!!
perfect!
2009-06-26, 15:01:47
anonymous  
rating
Provided that you can shutdown anything which is updating the database (so that there is no chance of anything changing), this is a good solution:


unfortunately, this is not a good solution for a sizable database. on a dual core 2.4Ghz workstation, loading my database from a dump takes about 52 hours.
2010-01-30, 09:10:26
tink3rbel1@yahoo.com from United States  
THANK YOU!! I renamed my DB perfectly thanks to YOU!! But Wanted to add.... if you go to your DB files on HD, I found them with no prob... but I did 1st try to rename it as store = store-1st = did not like the nameing config... I then renamed it to mystore, and it worked perfectly!!!! Thanks again you saved me a TON of time!!!!

-tink3rbel1
2010-02-20, 12:54:38
anonymous from Ireland  
rating
i did what you said and it worked the only thing is that it put '#mysql50#' before what i renamed my database was, i fixed it so the #mysql50# wasnt there but i had to use phpMyAdmin
2010-06-03, 09:20:28
Anonymous from United States  
rating
Provided that you can shutdown anything which is updating the database (so that there is no chance of anything changing), this is a good solution:


Exactly what I needed - very helpful, very 'professional' way of doing it.
2012-11-21, 00:57:43
anonymous from India  
heads off dude.
dumping and importing in single statement
2013-04-09, 23:52:18
anonymous from India  
rating
2017-08-23, 01:57:52
anonymous from Indonesia  
kami sekeluarga tak lupa mengucapkan puji syukur kepada ALLAH S,W,T
dan terima kasih banyak kepada NYAI atas nomor togel.nya yang NYAI
berikan 4 angka 9050 alhamdulillah ternyata itu benar2 tembus NYAI.
dan alhamdulillah sekarang saya bisa melunasi semua utan2 saya yang
ada sama tetangga.dan juga BANK BRI dan bukan hanya itu NYAII. insya
allah saya akan coba untuk membuka usaha sendiri demi mencukupi
kebutuhan keluarga saya sehari-hari itu semua berkat bantuan NYAII..
sekali lagi makasih banyak ya NYAI… bagi saudara yang suka main togel
yang ingin merubah nasib seperti saya silahkan hubungi NYAI RONGGENG,,di no (((082-189-481-547)))
insya allah anda bisa seperti saya…menang togel 275
juta, wassalam.

dijamin 100% jebol saya sudah buktikan…sendiri….

Apakah anda termasuk dalam kategori di bawah ini !!!!

1?Dikejar-kejar hutang

2?Selaluh kalah dalam bermain togel

3?Barang berharga anda udah habis terjual Buat judi togel

4?Anda udah kemana-mana tapi tidak menghasilkan solusi yg tepat

5?Udah banyak Dukun togel yang kamu tempati minta angka jitunya
tapi tidak ada satupun yang berhasil..

Solusi yang tepat jangan anda putus asah….NYAI RONGGENG akan membantu
anda semua dengan Angka ritwal/GHOIB:
butuh angka togel 2D 3D 4D SGP / HKG / MALAYSIA / TOTO MAGNUM / dijamin
100% jebol
Apabila ada waktu
silahkan Hub: NYAI RONGGENG DI NO: (((082-189-481-547)))

angka GHOIB: singapur 2D/3D/4D/

angka GHOIB: hongkong 2D/3D/4D/

angka GHOIB; malaysia

angka GHOIB; toto magnum 4D/5D/6D/

angka GHOIB; laos 4D/6D/8D/

Angka GHOIB; thailan 2D/3D/

Angka GHOIB; macau 2D/3D/4D/

Angka GHOIB; sidney 2D/3D/4D/

DEMI ALLAH INI ADALAH KISAH NYATA SAYA
INI ASLI, BUKAN REKAYASAH !!?....
This image was also posted here:
The UK NATIONAL LOTTERY scam
The UK NATIONAL LOTTERY scam
How to run a *.sql script (mysql)
Dating scammer Rose James
DelphiFAQ Site wide search function
Various dating scammers
How can I rename table names and column names in mysql
Compare 2 dates in mysql
How can I obtain the next auto_increment number (before inserting a record)?
Lock an entire mysql database with all its tables



Keywords:

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: