How can I copy one row / record from one MySQL instance to another?
I have two MySQL instances working with the same schema. One is in the cloud; one is in my local box. The local window version in the main table requires several test lines.
In fact, I would like to do something like mysqldump or mysqlhotcopy of a single record in a work table and then "restore" that record to the same table in a local instance. I don't want to copy the whole table. If there are rows in the local table, I want them to be left alone.
I'm fine with the PK of the copied line. I don't need foreign keys, if any.
The table is large and complex, which is not trivial for me to print a record using a select statement and then format it for insertion.
What are my options?
source to share
If you are already familiar with mysqldump --where
, let me remind you that mysqldump has a parameter , so you can get one line if you write a condition for it:
mysqldump databasename tablename --where "id=12345" --no-create-info --skip-add-locks --host=db1 | \
mysql --host=db2
You can also use SELECT ... INTO OUTFILE to output the result of any query to a flat file, even a single row query. Then LOAD DATA INFILE import this file to another instance.
You can also write a simple script in your language choice. Connect to both instances. SELECT one line from the original instance, store it in a variable in your script. Then issue an INSERT statement to execute against the target instance. This may not be the most efficient way to move a large amount of data, but for a single row it will be fine.
source to share