Skip to main content

MySQL – Moving tables from one db to another

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;
insert into foo select * from;

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 rename;

I hope this will be helpfull for someone 🙂