I recently switch to a new hosting provider for my application. My employees used the old site until the new site went live, however, the database backup from the old site was taken two days before the new site went live. So in the midst of transferring, records were being entered into the old site database while the new site had no existence of them (hence my two day time lag). How do I merge the two databases to reflect the changes?
我最近切换到我的应用程序的新托管服务提供商。我的员工使用旧站点,直到新站点上线,然而,旧站点的数据库备份是在新站点上线前两天进行的。因此,在传输过程中,记录被输入旧站点数据库,而新站点不存在(因此我的两天时间滞后)。如何合并两个数据库以反映更改?
A couple of things to note are the primary keys might be duplicated for some tables and there are only timestamps on a few tables as well. I would do a 'diff' or something of the sort, but the tables are dumped in different formats.
需要注意的一些事项是,某些表可能会复制主键,并且几个表上也只有时间戳。我会做一个'diff'或类似的东西,但表格会以不同的格式转储。
Any thoughts?
2 个解决方案
#1
0
This is something where you'll need to actually understand your database schema. You'll need to create a program that can look at both versions of the database, identify which records are shared, which are not, and which have conflicting primary keys (vs ones which were updated with the same keys). It then needs to copy over changes, possibly replacing the value of primary keys (including the values in other rows that refer to the row being renumbered!) This isn't easy, and it's not an exact science - you'll be writing heuristics, and expect to do some manual repairs as well.
这是您需要实际了解数据库模式的地方。您需要创建一个程序,该程序可以查看数据库的两个版本,识别哪些记录是共享的,哪些记录不是,哪些记录具有冲突的主键(与使用相同键更新的记录相比)。然后它需要复制更改,可能替换主键的值(包括引用重新编号的行的其他行中的值!)这并不容易,并且它不是一门精确的科学 - 您将编写启发式,并期望进行一些手动维修。
Next time shut that database down when you grab the final backup :)
当您获取最终备份时,下次关闭该数据库:)
#2
0
You don't need to create any additional programs. All what you need, to setup replications from the old DB to the new one. All your data from the old DB will automatically transfer to the new DB. At this period you should use you old DB as the main data source. And as soon as all data will be copied to the new location, you'll need just brake replica connection and change the DB address in your code (or DNS pointer) to the new one.
您无需创建任何其他程序。所需的一切,用于设置从旧数据库到新数据库的复制。旧数据库中的所有数据都将自动传输到新数据库。在此期间,您应该使用旧DB作为主要数据源。一旦所有数据都被复制到新位置,您只需要制动副本连接并将代码(或DNS指针)中的DB地址更改为新的。
1. oldDB ===> replication ==> newDB
R/W operations
2. oldDB ==/= brake ==/= newDB
R/W operations
MySQL Doc: 15.1.1. How to Set Up Replication
MySQL Doc:15.1.1。如何设置复制
#1
0
This is something where you'll need to actually understand your database schema. You'll need to create a program that can look at both versions of the database, identify which records are shared, which are not, and which have conflicting primary keys (vs ones which were updated with the same keys). It then needs to copy over changes, possibly replacing the value of primary keys (including the values in other rows that refer to the row being renumbered!) This isn't easy, and it's not an exact science - you'll be writing heuristics, and expect to do some manual repairs as well.
这是您需要实际了解数据库模式的地方。您需要创建一个程序,该程序可以查看数据库的两个版本,识别哪些记录是共享的,哪些记录不是,哪些记录具有冲突的主键(与使用相同键更新的记录相比)。然后它需要复制更改,可能替换主键的值(包括引用重新编号的行的其他行中的值!)这并不容易,并且它不是一门精确的科学 - 您将编写启发式,并期望进行一些手动维修。
Next time shut that database down when you grab the final backup :)
当您获取最终备份时,下次关闭该数据库:)
#2
0
You don't need to create any additional programs. All what you need, to setup replications from the old DB to the new one. All your data from the old DB will automatically transfer to the new DB. At this period you should use you old DB as the main data source. And as soon as all data will be copied to the new location, you'll need just brake replica connection and change the DB address in your code (or DNS pointer) to the new one.
您无需创建任何其他程序。所需的一切,用于设置从旧数据库到新数据库的复制。旧数据库中的所有数据都将自动传输到新数据库。在此期间,您应该使用旧DB作为主要数据源。一旦所有数据都被复制到新位置,您只需要制动副本连接并将代码(或DNS指针)中的DB地址更改为新的。
1. oldDB ===> replication ==> newDB
R/W operations
2. oldDB ==/= brake ==/= newDB
R/W operations
MySQL Doc: 15.1.1. How to Set Up Replication
MySQL Doc:15.1.1。如何设置复制