How do I copy table data between DBs without using dump files?

[Tama]

Hi there

I'm upgrading my server which runs PHPNuke, PHPBB and a couple of other PHP/MySQL modules. During the upgrade I have changed/upgraded some of the modules/ tablenames etc. so I can't do a straight SQL copy.

Also the DB is around 150meg in size so trying to upload Dump files would be really painful - If I try transfering via dump files I'll slaughter my bandwidth and kill phpMyAdmin with the size of the files. For added fun and games my server is in another country so I can only access it remotedly.

To try and get around dump files I uploaded my old SQL DB to the new server and have imported it as a seperate database (using Linux command line/gzip.)

I now have both databases accessible via phpMyAdmin and am trying to work out if there is a command to copy tables between the two databases. I want to pick and choose which tables to copy, and in some cases their names differ.


Is there a MySQL command to do this? I've been searching the web but haven't got anything useful so far. Any help/ suggestions/ advice would be much appreciated.

Thank you in advance
Tama

 

[hvass]

I could not find anything when I looked so wrote a simple perl script.

You are probably best using some form of script perhaps perl or php to

select * from sourcetable into outfile;
physically copy outfile to new server;
create table (using output of) show create table sourcetable;
load data infile;

This gives you the opportunitiy to programatically ammend table structures etc.. if you are dealing with large volumes of tables.

 

[Tama]

Hi hvass

Cheers for that, definitely something to think about. The SQL dump file is 300meg and I'm only on 256kb so I'm trying to avoid too much pushing and pulling from my PC. I can get shell access into my server, but it would take forever to do the editting via PICO

I'm surprised that I can't find a "simple" copy command that goes something like COPY TABLE olddatabase.table1 TO newdatabase.table1 - am I being too optimistic?

Cheers
Tama

 

[Tama]

I've attempted to edit the dump file via Pico but it overloads my server and starts spewing up "Unallocated bytes" messages.

What is the linux bash command to extract a table from a SQL database?

 

[KarveR]

INSERT INTO table SELECT * FROM othertable;

http://www.mysql.com/doc/en/INSERT_SELECT.html

hope this helps


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[hvass]

I realise I might have read your original posting wrong.

Are you saying you now have olddb and newdb on the same new server. If you have then MySQL lets you reference more than one database so you should just be able to write sql code something like

create table newdb.newtablename
select
*
from olddb.oldtablename;

Then you can use alter table to drop columns, change column names, types, sizes etc..

 

[Tama]

Thank you Hvass - that was exactly what I was after. I managed to do most of it by using individual dump tables on the server.

But I can now use your way to deal to the really trick conversions.

Thanks again.

Tama