Caught in a Web

Few days ago I had to move all tables from one MySQL database to other database, but on the same MySQL server. Since I didn’t want to bother with mysqldump, after a short time I came with this simple solution:

use new_db;
create table foo like old_db.foo;
insert into foo select * from old_db.foo;

where new_db is new database in which we are moving tables from old_db. create table like query create table identicly with original table, which includes indexes as well. Other query insert all data from old table into it’s successor in new_db. And so one for each table in db, I had to do the same (fortunately, there weren’t many tables in db :)

Today I browser MySQL book (by Paul Dubois) and accidentaly came to faraway simpler solution :)

alter table old_db.foo rename new_db.foo;

I hope this will be helpfull for someone :)