Copy table on to different server?

[expired user #6652]'s profile image [expired user #6652] posted 9 years ago in General Permalink
For some reason it seems this isn't possible in Mysql. Yet, I do it in HeidiSQL - Maintenance - SQL export output to (select server) Database - select the table.
This is awesome, and I have no complaints as it does exactly what I ask it to do. What I would like to know, is the instruction set that this uses? I would like copy a table from server x to y as part of a procedure? Or automate it using the maintenance function in HeidiSQL. Thank you for any insight.
[expired user #6652]'s profile image [expired user #6652] posted 9 years ago Permalink
I would like to edit my question above - not sure how though so I will respond to myself.
I found the Mysqldump option, and have used that for dumping to files (.sql) not sure though of the syntax to dump to another database. If I find examples I will post, in the meantime if anyone has any..
I am thinking something like:
mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

My question arrives at the mydb_tables.sql part - can this be a table in another database, on another server? Or must it be a two part action - dump, and then load.
Again Thank you.
jfalch's profile image jfalch posted 9 years ago Permalink
mysqldump can only write to file, it is not possible to specify "other db" or "other server". you can, however, load the generated .sql file into another db or unto another server using the mysql command line client in a second step:

mysql -h server -u.. -p.. -D dbname <any.sql

note that you will have to fiddle around with the mysqldump ddl options for a bit to create the right effect on the receiver side. if this is not sufficient, i use sed to modify the .sql file in a third step (also available on windows).
[expired user #6652]'s profile image [expired user #6652] posted 9 years ago Permalink
Jfalch,
Thank you - that is what I suspected for MySQLdump - takes me back to the maintenance in Heidisql - is this actually a two step process - dump and import, or is it somehow copying the data directly to the table in the other database, in the other server?
jfalch's profile image jfalch posted 9 years ago Permalink
you have to ask ansgar (the author) for details. according to the mysql api, it is possible to copy the data directly from one database/table to another, using a read/copy/write loop.
ansgar's profile image ansgar posted 9 years ago Permalink
Heidi's SQL export to a second server is a two way process. The output is handled by a function which fires the commands on the server session you selected in that drop down. MySQL cannot do that directly.

That way, in theory, it should be even possible to dump from MySQL to a MSSQL or PostgreSQL server, or vice versa. This is just broken by the incompatible SQL dialects provided by these servers.
[expired user #6652]'s profile image [expired user #6652] posted 9 years ago Permalink
Thank you - bummer - but it isn't a slight on you in anyway.

I will still use the maintenance option as it seems to be the best solution for now - fastest at least.

Please login to leave a reply, or register at first.