janewilliams20 (![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png)
janewilliams20) wrote2010-11-30 10:55 am
janewilliams20) wrote2010-11-30 10:55 am
A bit more MySQL head-desking
We have a copy of the main, live DB on another server, for test purposes. It's rather out of date, and could use a refresh. I've got some test tables and data in there that I'd rather not lose until I've checked them, so what I want to do is rename the old copy rather than dropping or over-writing it, then import a new copy from last night's live backup.
In SQL Server, I would rename that database by right-clicking on it and choosing "rename". In MySQL, I right-click, and see no such option. "Alter schema" does not allow me to alter the name. Admin screen...? no, nothing. I hit Google, and the manual. Apparently there used to be a command "rename database", but it was removed due to being too dangerous. Further Googling shows that other people have hit this problem before, and either export and reimport (taking ages), or do various interesting hacks that probably work, sometimes, and require you to shut down MySQL while you do it.
Look, all I want is to rename a DB so I don't lose it! It should be about two clicks and type a few characters!
Meanwhile, my notes from last time on how to restore a single DB from a backup:
"Going through admin doesn't work" - well, of course not! Wouldn't expect the admin interface to let you do routine DB maintenance, would you?
In SQL Server, I would rename that database by right-clicking on it and choosing "rename". In MySQL, I right-click, and see no such option. "Alter schema" does not allow me to alter the name. Admin screen...? no, nothing. I hit Google, and the manual. Apparently there used to be a command "rename database", but it was removed due to being too dangerous. Further Googling shows that other people have hit this problem before, and either export and reimport (taking ages), or do various interesting hacks that probably work, sometimes, and require you to shut down MySQL while you do it.
Look, all I want is to rename a DB so I don't lose it! It should be about two clicks and type a few characters!
Meanwhile, my notes from last time on how to restore a single DB from a backup:
Drop the DB you want to replace (if you do).
Create a new schema with the new name.
Going through admin doesn't work.
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u root --password=(password) (new name) < C:\AA_Backup\server_47\mysql\(dbname).bak
Create a new schema with the new name.
Going through admin doesn't work.
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u root --password=(password) (new name) < C:\AA_Backup\server_47\mysql\(dbname).bak
"Going through admin doesn't work" - well, of course not! Wouldn't expect the admin interface to let you do routine DB maintenance, would you?