How can I copy a single row/record from one MySQL instance to another?

Question

I have two MySQL instances running with the same schema. One is in the cloud; one is on my local box. The local-box version needs a couple of test rows in its main table.

Effectively I'd like to do something like a mysqldump or mysqlhotcopy of a single record on the production table, and then "restore" that record into the same table on the local instance. I don't want to copy the whole table. If there are rows on the local table, I want them left alone.

I'm fine with the PK of the copied row changing. I don't care about foreign keys, if any.

The table is big and complex enough that it's nontrivial for me to print the record with a select statement and then format it for an insert.

What are my options?

Answer 1

If you're already familiar with mysqldump, I'll remind you that mysqldump has a --where option, so you can get a single row if you write the condition for that:

mysqldump databasename tablename --where "id=12345" --no-create-info --skip-add-locks --host=db1  | \
  mysql --host=db2

You could also use SELECT...INTO OUTFILE to dump the result of any query you want into a flat file, even a query of a single row. Then LOAD DATA INFILE to import that file on the other instance.

You could also write a simple script in your choice of language. Connect to both instances. SELECT a single row from the source instance, save it in a variable in the script. Then form an INSERT command to execute against the destination instance. This may not be the most efficient way to move a large amount of data, but for a single row it would be fine.