Copy a mysql table content from one server to another server

Question

How to copy a table from server A database db1 to server B database db2 ?

I am able to copy a table from one database to another database within the server, but not able to do for across servers.

CREATE TABLE recipes_new LIKE production.recipes; 
INSERT recipes_new SELECT * FROM production.recipes;

The whole thing I am doing it to reduce the server load so that I can copy table info into another server and run my queries there...

Glenn

possible duplicate of copy MySql DB from one server to another – Rest

What connection do you have between server A and B? – Statvolt

@Attila Fulop : Both the servers are in same network, possibly intranet. – Glenn

Use aleroot's solution below: – Statvolt

Use aleroot's solution below: 1.) create the dump on server A (mysqldump...) 2.) copy the dump eg via sftp to server B 3.) read the dump on server B ---- I'd do that with a cron job on both servers. However depending on your needs, this might be a totally crappy solution. If you really want to do load balancing, then consider using MySQL cluster dev.mysql.com/downloads/cluster – Statvolt

Answer 1

You can dump the table with mysqldump and import the dumped file in the other Server :

mysqldump - root -p db1 tabletoexp > table.sql

and on the other side :

mysql -u root -p db2 < table.sql

Crissman

You can also do this without creating the intermediate file: "mysqldump - root -p db1 tabletoexp | mysql -u root -p db2" If they are on different servers use "-h hostname" on both sides. – Schoonmaker

Answer 2

You can export whole database if size is small else you can export only structure of database. When you select Db in phpmysql, There is one link called export.

Click on it, save it into one file . and import the same file in other server.

Piccoloist

I want it though program not with phpmyadmin tool. – Glenn

AFAIK it's not possible to do what you want with a query. You would have to use mysqldump / mysqlimport as aleroot stated in the other answer! Or write a wrapper program for those functions that does all the exporting / importing automatically. – Schaffner

The "small size restriction" is for phpMyAdmin and other webtools that have an execution and memory limit. If you use the command line as aleroot state, you can export a ton of data. I handled large databases that exported to a 2 GB text dump myself with no problems (aside from the waiting, it took over an hour back then to reimport the database). – Schaffner