迁移后比较不同数据库的表 [英] Comparing tables of different databases after migration

问题描述

最近，我已使用 MySQL迁移工具包将我的项目从 SQLServer 2000 迁移到 MySQL 5.2 ，但在迁移结束时，虽然迁移成功，但我仍然不能确定数据是否成功迁移，因为在后端迁移这个迁移工具包做一些像它改变了数据类型为tiny int，这一个我注意到，但我不知道还有什么它确实是因为我有一些敏感数据，所以我想确定从我身边是否迁移是实际上成功或不是在同一时间，我不能这样手动，由于巨大的数据量有任何方式，使我可以比较表来自 sqlserver 2000数据库和 Mysql 数据库并生成报告。

解决方案

我怀疑有一个工具可以做到这一点，所以你很可能需要编写自己的，它将需要一些时间。

我要做的第一件事是编写一个连接到两个数据库的应用程序，将表定义捕获到内存中，并按表名顺序将它们输出到文件。

然后使用文件比较工具（如 WinMerge ）检查差异。如果有差异，如上面提到的，在MySQL中进行必要的更改，并在必要时映射数据。

之后，我将修改相同的应用程序循环数据库中的每个表，将所有数据恢复到某种类型的基于集的对象，该对象支持UNION操作。使用新表UNION查找旧表，并检查结果中的行数是否等于源数据库中的行数。如果是这样，所有的值是相同的，另外明智地标记它进一步检查。这将很可能照顾你的大多数表，你可以手动检查那些失败的测试。

所有其他对象将是主题的变体（他们是更简单的对象，只是文本），但我会花大部分时间来验证数据。 / p>

希望这有助于它，它会是一个耗时的过程，你以任何方式看看它。

Recently I have migrated my project from SQLServer 2000 to MySQL 5.2 using MySQL migration toolkit although at the end of migration it shows the message that migration successful but i am still not sure whether the data migrated successfully or not because while migrating in the back end this migration toolkit do something like it changed the bit Datatype to tiny int, this one i have noticed but i am not sure what else it does as i am having some sensitive data so i want to be sure from my side whether migration is actually successful or not in the same time i cannot do this manually due to huge amount of data are there any ways so that i can compare tables from both sqlserver 2000 database and Mysql database and generate a report. By comparing i mean comparing table as a whole including data, datatype, everything.

解决方案

I doubt there is a tool for this, so you will most likely need to write your own and it is going to take some time.

The first thing I would do is write an application that connects to both databases, grab the table definitions into memory, and output them to a file in order of table name.

Then use a file comparision tool like WinMerge to check for differences. If there are differences like the one you mentioned above, make the necessary changes in MySQL and map the data if necessary.

After that I would modify the same application to cycle through every table in the database, bring back all the data into some type of set-based object that supports a UNION operation. UNION the old table with the new table and check to see if the number of rows in the result equals the number of rows in the source database. If so, all the values are the same, other wise mark it for further inspection. This will most likely take care of most of your tables and you can do a manual inspection of the ones that fail this test.

All the other objects will be variation on a theme (they are simpler objects, just text), but I would spend the majority of my time on verifying the data.

Hope this helps, its going to be a time consuming process for you any way you look at it.

这篇关于迁移后比较不同数据库的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！