Our company has really old legacy system with such a bad database design (no foreign keys, columns with serialized PHP arrays, etc. :(). We decided to rewrite a system from a scratch with new database schema.
我们公司有一个非常古老的遗留系统，有这么糟糕的数据库设计（没有外键，带有序列化PHP数组的列等等）。我们决定用新的数据库模式从头开始重写一个系统。
We want to rewrite a system by parts. So we will split old monolithic application to many smaller ones.
我们想要按部分重写系统。因此，我们将旧的单片应用程序拆分为许多较小的应用程序。
Problem is: we want to have live data in two databases. Old and New schema. I'd like to ask you if anyone of you knows best practices how to do this.
问题是：我们希望在两个数据库中拥有实时数据。新旧架构。我想问你是否有人知道如何做到这一点的最佳实践。
What we think of:
我们的想法：
- asynchronous data synchronization with message queue
- 与消息队列的异步数据同步
- make a REST API in new system and make legacy application to use it instead of db calls
- 在新系统中创建REST API并使遗留应用程序使用它而不是db调用
- some kind of table replication
- 某种表复制
Thank you very much
非常感谢你
3 个解决方案
#1
0
I had to deal with a similar problem in the past. There was a system which didn't have support but there was people using it because, It had some features (security holes) which allowed them certain functionalities. However, they also needed new functionalities.
我过去不得不处理类似的问题。有一个系统没有支持，但有人使用它，因为它有一些功能（安全漏洞），使他们具有某些功能。但是，它们也需要新的功能。
I selected the tables which involved the new system and I created some triggers for cross update the tables, so when I created a register on the old system the trigger created a copy in the new system and reversal. If you design this system properly you would have both systems working at the same time in real time.
我选择了涉及新系统的表，并创建了一些交叉更新表的触发器，因此当我在旧系统上创建一个寄存器时，触发器在新系统中创建了一个副本并进行了反转。如果您正确设计此系统，您将使两个系统实时同时工作。
The drawback is that while the both system are running the system is going to become slower since you have to maintain the integrity of two databases in every operation.
缺点是，当两个系统都在运行时，系统将变慢，因为您必须在每个操作中保持两个数据库的完整性。
#2
0
I would start by adding a database layer to accept API calls from the business layer, then write to both the old schema and the new. This adds complexity up front, but it lets you guarantee that the data stays in sync.
我首先添加一个数据库层来接受来自业务层的API调用，然后写入旧模式和新模式。这预先增加了复杂性，但它可以保证数据保持同步。
This would require changing the legacy system to call an API instead of issuing SQL statements. If they did not have the foresight to do that originally, you may not be able to take my approach. But, you should do it going forward.
这将需要更改遗留系统以调用API而不是发出SQL语句。如果他们原本没有先见之明，那么你可能无法接受我的方法。但是，你应该继续前进。
Triggers may or may not work out. In older versions of MySQL, there can be only one trigger of a given type on a given table. This forces you to lump unrelated things into a single trigger.
触发器可能会或可能不会成功。在旧版本的MySQL中，给定表上只能有一个给定类型的触发器。这会迫使您将无关的事物整合到一个触发器中。
Replication can solve some changes -- Engine, datatypes, etc. But it cannot help with splitting one table into two. Be careful of the replication of Triggers and where the Trigger has effect (between Master and Slave). In general, a stored routine should be performed on the Master, letting the effect be replicated to the slave. But it may be worth considering how to have the trigger run in the Slave instead. Or different triggers in the two servers.
复制可以解决一些更改 - 引擎，数据类型等。但它无法帮助将一个表拆分为两个。注意触发器的复制以及触发器有效的位置（主站和从站之间）。通常，应该在主服务器上执行存储的例程，让效果复制到从服务器。但是可能值得考虑如何在Slave中运行触发器。或两个服务器中的不同触发器。
Another thought is to do the transformation in stages. By careful planning of schema changes versus application of triggers versus code changes versus database layer, you can do partial transformations one at a time, sometimes without having a big outage to update everything simultaneously (with your fingers crossed). A simple example: (1) change code to dynamically handle either new or old schema, (2) change the schema, (3) clean up the code (remove handling of old schema).
另一个想法是分阶段进行转型。通过仔细规划模式更改与触发器的应用与代码更改与数据库层的比较，您可以一次进行一次部分转换，有时无需大量中断即可同时更新所有内容（用手指交叉）。一个简单的例子：（1）更改代码以动态处理新的或旧的模式，（2）更改模式，（3）清理代码（删除旧模式的处理）。
#3
0
Doing a database migration may be a tedious task considering the complexity of data and structure of the tables which is of-course with out any constraints or a proper design. But given that your legacy application was doing its job - the amount of corrupt usable data will be minimal.
考虑到数据的复杂性和表的结构，进行数据库迁移可能是一项繁琐的工作，当然没有任何约束或适当的设计。但鉴于您的遗留应用程序正在完成其工作 - 损坏的可用数据量将是最小的。
For the said problem I would suggest a db migration task which would convert all the old legacy data into the new form. And develop the new application. The advantages being.
对于上述问题，我建议使用db迁移任务，将所有旧的遗留数据转换为新的表单。并开发新的应用程序。优点是。
1) There is not need to keep 2 different applications.
1）不需要保留2种不同的应用程序。
2) No need to change the code in the legacy application - which can become messy.
2）无需更改遗留应用程序中的代码 - 这可能会变得混乱。
3) DB migration will give us a chance to correct any corrupt data (if needed).
3）数据库迁移将使我们有机会纠正任何损坏的数据（如果需要）。
DB migration may not be practical under all scenarios but if you can do it in lesser effort than making the changes for database sync, new api's for legacy application - I would suggest to go for it.
在所有情况下，数据库迁移可能都不实际，但是如果你能够以比为数据库同步进行更改而花费更少的努力来实现它，那么遗留应用程序的新API也是如此 - 我建议你去做。
#1
0
I had to deal with a similar problem in the past. There was a system which didn't have support but there was people using it because, It had some features (security holes) which allowed them certain functionalities. However, they also needed new functionalities.
我过去不得不处理类似的问题。有一个系统没有支持，但有人使用它，因为它有一些功能（安全漏洞），使他们具有某些功能。但是，它们也需要新的功能。
I selected the tables which involved the new system and I created some triggers for cross update the tables, so when I created a register on the old system the trigger created a copy in the new system and reversal. If you design this system properly you would have both systems working at the same time in real time.
我选择了涉及新系统的表，并创建了一些交叉更新表的触发器，因此当我在旧系统上创建一个寄存器时，触发器在新系统中创建了一个副本并进行了反转。如果您正确设计此系统，您将使两个系统实时同时工作。
The drawback is that while the both system are running the system is going to become slower since you have to maintain the integrity of two databases in every operation.
缺点是，当两个系统都在运行时，系统将变慢，因为您必须在每个操作中保持两个数据库的完整性。
#2
0
I would start by adding a database layer to accept API calls from the business layer, then write to both the old schema and the new. This adds complexity up front, but it lets you guarantee that the data stays in sync.
我首先添加一个数据库层来接受来自业务层的API调用，然后写入旧模式和新模式。这预先增加了复杂性，但它可以保证数据保持同步。
This would require changing the legacy system to call an API instead of issuing SQL statements. If they did not have the foresight to do that originally, you may not be able to take my approach. But, you should do it going forward.
这将需要更改遗留系统以调用API而不是发出SQL语句。如果他们原本没有先见之明，那么你可能无法接受我的方法。但是，你应该继续前进。
Triggers may or may not work out. In older versions of MySQL, there can be only one trigger of a given type on a given table. This forces you to lump unrelated things into a single trigger.
触发器可能会或可能不会成功。在旧版本的MySQL中，给定表上只能有一个给定类型的触发器。这会迫使您将无关的事物整合到一个触发器中。
Replication can solve some changes -- Engine, datatypes, etc. But it cannot help with splitting one table into two. Be careful of the replication of Triggers and where the Trigger has effect (between Master and Slave). In general, a stored routine should be performed on the Master, letting the effect be replicated to the slave. But it may be worth considering how to have the trigger run in the Slave instead. Or different triggers in the two servers.
复制可以解决一些更改 - 引擎，数据类型等。但它无法帮助将一个表拆分为两个。注意触发器的复制以及触发器有效的位置（主站和从站之间）。通常，应该在主服务器上执行存储的例程，让效果复制到从服务器。但是可能值得考虑如何在Slave中运行触发器。或两个服务器中的不同触发器。
Another thought is to do the transformation in stages. By careful planning of schema changes versus application of triggers versus code changes versus database layer, you can do partial transformations one at a time, sometimes without having a big outage to update everything simultaneously (with your fingers crossed). A simple example: (1) change code to dynamically handle either new or old schema, (2) change the schema, (3) clean up the code (remove handling of old schema).
另一个想法是分阶段进行转型。通过仔细规划模式更改与触发器的应用与代码更改与数据库层的比较，您可以一次进行一次部分转换，有时无需大量中断即可同时更新所有内容（用手指交叉）。一个简单的例子：（1）更改代码以动态处理新的或旧的模式，（2）更改模式，（3）清理代码（删除旧模式的处理）。
#3
0
Doing a database migration may be a tedious task considering the complexity of data and structure of the tables which is of-course with out any constraints or a proper design. But given that your legacy application was doing its job - the amount of corrupt usable data will be minimal.
考虑到数据的复杂性和表的结构，进行数据库迁移可能是一项繁琐的工作，当然没有任何约束或适当的设计。但鉴于您的遗留应用程序正在完成其工作 - 损坏的可用数据量将是最小的。
For the said problem I would suggest a db migration task which would convert all the old legacy data into the new form. And develop the new application. The advantages being.
对于上述问题，我建议使用db迁移任务，将所有旧的遗留数据转换为新的表单。并开发新的应用程序。优点是。
1) There is not need to keep 2 different applications.
1）不需要保留2种不同的应用程序。
2) No need to change the code in the legacy application - which can become messy.
2）无需更改遗留应用程序中的代码 - 这可能会变得混乱。
3) DB migration will give us a chance to correct any corrupt data (if needed).
3）数据库迁移将使我们有机会纠正任何损坏的数据（如果需要）。
DB migration may not be practical under all scenarios but if you can do it in lesser effort than making the changes for database sync, new api's for legacy application - I would suggest to go for it.
在所有情况下，数据库迁移可能都不实际，但是如果你能够以比为数据库同步进行更改而花费更少的努力来实现它，那么遗留应用程序的新API也是如此 - 我建议你去做。