Transfer records from one MySQL DB to another.

Question

How can I transfer specific rows from one MySQL DB on one server to another in a completely different server. Also the schemas don't necessarily have to be the same. For example on server "A" I could have a Users table with 2 columns and on Server "B" have a Users table with 4 columns.

Thanks.

Answer 1

make 4-column table on the server A, INSERT SELECT data into that table, then make an SQL dump of the table, and then execute that dump on the server B,

Answer 2

You should clarify 'specific rows' and specify if only one table or many.

mysqldump [options] [db_name [tbl_name...]] can do a lot
-c will create inserts with column names
-w will apply where condition, for example -w"userid=1"

so

mysqldump -c -w"column=value" db table > table.sql

might get you there.

Inserts with column names could work with slightly different schema (depending on ref integrity)

Answer 3

If your tables are not too large I would suggest first creating a tmp table on the side that you are getting the data from. The tmp table should match the columns on the side that you are exporting the data to. Insert all of the export table into the tmp table.

To create the tmp tables you'd so something like this:

create table tmpuser as select col1, col2, ... from user;

Then to transfer the data:

mysqldump --no-create-db --no-create-info db tmptablelist > dump.sql

import on the other end using:

mysql db < dump.sql