如何将单个行/记录从一个MySQL实例复制到另一个实例? [英] How can I copy a single row/record from one MySQL instance to another?

问题描述

我有两个以相同模式运行的MySQL实例.一个在云中；另一个在云中.一个在我的本地盒子上.本地盒版本在其主表中需要几个测试行.

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.

有效地，我想对生产表上的单个记录执行类似mysqldump或mysqlhotcopy的操作，然后将该记录还原"到本地实例的同一表中.我不想复制整个桌子.如果本地表上有行，我希望将它们单独保留.

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.

我很好，复制行的PK发生了变化.我不在乎外键，如果有的话.

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

该表足够大且复杂，以至于我无法使用select语句打印记录，然后对其进行格式化以进行插入.

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.

我有什么选择?

推荐答案

如果您已经熟悉mysqldump，我会提醒您mysqldump具有

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

您还可以使用 SELECT ... INTO OUTFILE 可以将任何查询的结果转储到平面文件中，甚至是单行查询.然后加载数据文件将该文件导入到其他情况.

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.

您还可以使用您选择的语言编写一个简单的脚本.连接到两个实例.从源实例中选择一行，然后将其保存在脚本的变量中.然后形成一个INSERT命令以对目标实例执行.这可能不是移动大量数据的最有效方法，但是对于单行来说就可以了.

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.

这篇关于如何将单个行/记录从一个MySQL实例复制到另一个实例?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！