如何使用来自另一个数据库的数据更新MySQL数据库?

Disclaimer: this is a bit of a "best practices" question, but I'm not sure how else to phrase it to be more concrete or evoke a more objective answer.

免责声明:这是一个“最佳实践”的问题，但我不知道该如何表达它更具体或更客观的回答。

I have two databases for a small Rails project: a dev database and a prod database that live on the same server.

我有两个用于小型Rails项目的数据库:一个dev数据库和一个位于同一服务器上的prod数据库。

What happens is that every couple weeks, I make some data changes (mostly inserts) via the ActiveAdmin gem to the dev database via a dev Rails environment. I poke around the Rails app making sure the new data looks good. When I'm ready to deploy, I run a script that:

每隔几周，我就会通过一个dev Rails环境，通过ActiveAdmin gem对dev数据库进行一些数据更改(主要是插入)。我在Rails应用程序上搜索，确保新数据看起来不错。当我准备部署时，我运行一个脚本:

Dumps the dev db
转储dev db
Drops and recreates the prod db to delete all data
删除并重新创建prod db来删除所有数据。
Imports the dev db dump into the prod db
将dev db转储导入到prod db中。

My intuition tells me that this is not a great way of going about this, and it's also a bit slow, but I can't seem to find the standard way of doing data deployments from one database to another. What is the standard way, if there is one?

我的直觉告诉我，这不是一个很好的方法，而且它也有点慢，但是我似乎不能找到从一个数据库到另一个数据库进行数据部署的标准方法。如果有的话，标准的方法是什么?

Things I've considered:

我认为:

Setting up an additional database that is the replica of the dev database; in deployment, somehow switch the Rails app over to use the replica as "prod", update the old "prod" db to be the replica, etc. I can barely even keep this idea in my head, and it seems like such a mess.
设置一个额外的数据库，该数据库是dev数据库的副本;在部署过程中，将Rails应用程序转换为“prod”，更新旧的“prod”db，以成为副本，等等。我甚至不能把这个想法放在脑子里，看起来就像一团糟。
Doing data changes directly on prod, which completely invalidates the need for a dev database (feels very gross)
在prod上直接进行数据更改，这完全使开发数据库的需求完全失效(感觉非常粗糙)
Doing data changes in dev via SQL script with transactions, and applying them to prod when deploying (it'd be really, really annoying to write these scripts by hand)
通过使用事务的SQL脚本对dev进行数据更改，并在部署时将其应用于prod(用手工编写这些脚本确实非常烦人)

Some additional notes:

一些需要注意的地方:

The only data changes that are ever made are the ones I make
唯一的数据变化是我做的。
Schema changes are done via Rails migrations
模式更改是通过Rails迁移完成的。
The databases are relatively small (the biggest table is ~1000 rows)
数据库相对较小(最大的表是~1000行)

2 个解决方案

#1

If you have two databases on the same server, you can compare and insert into tables. First, for deleted rows:

如果在同一台服务器上有两个数据库，则可以比较和插入表。首先,删除行:

BEGIN TRAN;
DELETE FROM prod.tbl1
WHERE id IN (
 SELECT id FROM dev.tbl1 RIGHT JOIN prod.tbl1 ON dev.tbl1.id = prod.tbl1.id WHERE dev.tbl1.id IS NULL);
COMMIT;

Second, for new rows:

第二,新行:

BEGIN TRAN;
INSERT INTO prod.tbl1
SELECT *
FROM dev.tbl1
WHERE id IN (
 SELECT id FROM dev.tbl1 LEFT JOIN prod.tbl1 ON dev.tbl1.id = prod.tbl1.id WHERE prod.tbl1.id IS NULL);
COMMIT;

Now, a trigger on your dev database to manage updates:

现在，您的dev数据库上的一个触发器来管理更新:

CREATE DEFINER=`root`@`localhost` TRIGGER `dev`.`tbl1_update`
AFTER UPDATE ON `dev`.`tbl1`
FOR EACH ROW
BEGIN
    SET NEW.update = '1';
END

You need a "update" field on the dev table. When a update query run on the table, the field "update" changes to 1 automatiaclly. Then, use this query:

您需要在dev表上有一个“更新”字段。当更新查询在表上运行时，字段“更新”更改为1自动。然后,使用这个查询:

BEGIN TRAN;
UPDATE prod.tbl1
LEFT JOIN dev.tbl1
    ON prod.tbl1.id = dev.tbl1.id
SET prod.tbl1.fld1 = dev.tbl1.fld1, prod.tbl1.fld2 = dev.tbl1.fld2
WHERE prod.tbl1.id IN (SELECT id FROM dev.tbl1 WHERE update = '1');
UPDATE dev.tbl1 SET update = '0';
COMMIT;

You can run a query like this on all tables. You can put it on a .sql file and run with a cron job (mysql -h -u -D < myscript.sql).

您可以在所有表上运行这样的查询。您可以将它放在一个.sql文件上，并运行一个cron作业(mysql -h -u -D < myscript.sql)。

This query compare tables and get the IDs on dev not present on production. Then, execute a select for the complete table (only these ids), and insert them on prod.

此查询比较表，并获得在开发上不存在的id。然后，为完整的表执行select(只有这些id)，并在prod上插入它们。

(Replace the id field with your unique identifier one for each table).

(每个表用唯一标识符替换id字段)。

#2

This seems like a pretty strange approach. Usually the data in development is regarded as disposable. You want just enough data so that you can do styling and troubleshooting - usually with psuedorandom data. Building the "finished" app data in development seems error prone and you would need to sync work if you are more than one developer.

这似乎是一种很奇怪的方法。通常，开发中的数据被认为是一次性的。您只需要足够的数据，这样您就可以进行样式化和故障排除——通常使用psuedorandom数据。在开发中构建“完成”的应用程序数据似乎容易出错，如果您不止一个开发人员，那么您需要同步工作。

Plus if the data set is significantly large Rails will be very slow in development due to the lack of caching.

此外，如果数据集明显很大，由于缺少缓存，开发过程将非常缓慢。

What you want is a staging environment which runs on the same settings as the intended production. The key here is that it should be as close to production as possible. This can run on remote server or a server in a intranet.

您想要的是一个登台环境，它运行在与预期生产相同的设置上。这里的关键是，它应该尽可能接近生产。这可以在远程服务器上运行，也可以在内部网中运行。

You can also use the staging environment to display new features or progress to clients/stakeholders and let them preview new features or be looped in on the progress in development.

您还可以使用登台环境向客户/涉众展示新特性或进展，并让他们预览新功能，或在开发过程中进行循环。

You can create it by copying config/environments/production.rb -> staging.rb and by setting the RAILS_ENV env var to staging on the intended staging server.

您可以通过复制配置/环境/生产来创建它。rb - >登台。将RAILS_ENV env var设置为在预期的分段服务器上进行分段。

You should also create an additional section in config/database.yml or use ENV['DATABASE_URL'].

您还应该在config/database中创建一个额外的部分。yml或使用ENV(“DATABASE_URL”)。

Depending on the project staging can be flushed daily with mirrored data from production or be fully synced.

根据项目阶段的不同，可以每天刷新来自生产的镜像数据或完全同步。

#1