CODEWITHSUNDEEP
sqlmysql
Jaime Garcia
Jaime Garcia

Reputation: 7096

Transfer records from one MySQL DB to another

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.

Upvotes: 0

Views: 1572

Answers (3)

Unreason
Unreason

Reputation: 12704

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)

Upvotes: 1

Dark Castle
Dark Castle

Reputation: 1299

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

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157896

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,

Upvotes: 0

Related Questions