RE: compare tables from two systems
There is a product that will do the job. It is MYdbPAL (www.it-map.com) which is a free license to MySQL users. You can extract all or partial datasets from 2 databases, compare them and produce a 'differences' dataset. This can, be viewed, edited, and if needed be applied to the target database to update it and put it back in synch. Tim Hayes -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: 05 October 2005 23:25 To: Claire Lee; mysql@lists.mysql.com Subject: Re: compare tables from two systems - Original Message - From: Claire Lee [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, October 05, 2005 5:58 PM Subject: compare tables from two systems We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. I'm not sure if there are any existing table comparison tools out there; in an ideal world, someone would have written something that works on any relational database that would compare any two tables to see if they are the same. I've never actually heard of such a tool but I've never looked for one either. If there is no such tool out there, you could create one of your own without too much difficulty. One way that should be pretty easy would be to do a mysqldump of each table then do standard file compares of each of the two resulting files. I have a freeware program for Windows called ExamDiff which seems to do that job okay, although I've never tried comparing output files from mysqldump with it. There may be one very difficult problem with this approach: it assumes that the mysqldump will write the INSERT statements for the individual rows in a specific order, ideally primary key sequence, for each table. Unfortunately, I don't see any options for mysqldump that ensure that this will happen and I don't see any statement in the manual that say it will happen automatically. Therefore, it is entirely possible that the mysqldumps of each table will write the INSERTs in some sequence other than primary key order. For example, mysqldump might use the sequence in which the rows were stored, retrieving them from oldest to newest, rather than primary key sequence. In any case where primary key sequence is not used - or where no primary key exists on the table - the two mysqldump files could have completely different sequences even though they have the identical rows. That would almost certainly preclude this approach working. Why not just try doing mysqldumps of each of your two tables and then do a file compare of the two files and see what happens? It shouldn't take long and you'll soon see if this approach will work. By the way, why are you keeping two independent - yet supposedly identical copies - of your tables? Isn't that redundant *and* expensive? Wouldn't it make more sense to backup a single copy of the database regularly so that you can restore when you have problems? Or, if you need the same database in two different locations, why not use replication to ensure that the two copies are automatically synchronised? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.9/116 - Release Date: 30/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compare tables from two systems
We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. Claire __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compare tables from two systems
On 10/5/05, Claire Lee [EMAIL PROTECTED] wrote: We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. mysqldump -l -B db1 db1.sql mysqldump -l -B db2 db2.sql diff db1.sql db2.sql diff.sql -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/
Re: compare tables from two systems
- Original Message - From: Claire Lee [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, October 05, 2005 5:58 PM Subject: compare tables from two systems We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. I'm not sure if there are any existing table comparison tools out there; in an ideal world, someone would have written something that works on any relational database that would compare any two tables to see if they are the same. I've never actually heard of such a tool but I've never looked for one either. If there is no such tool out there, you could create one of your own without too much difficulty. One way that should be pretty easy would be to do a mysqldump of each table then do standard file compares of each of the two resulting files. I have a freeware program for Windows called ExamDiff which seems to do that job okay, although I've never tried comparing output files from mysqldump with it. There may be one very difficult problem with this approach: it assumes that the mysqldump will write the INSERT statements for the individual rows in a specific order, ideally primary key sequence, for each table. Unfortunately, I don't see any options for mysqldump that ensure that this will happen and I don't see any statement in the manual that say it will happen automatically. Therefore, it is entirely possible that the mysqldumps of each table will write the INSERTs in some sequence other than primary key order. For example, mysqldump might use the sequence in which the rows were stored, retrieving them from oldest to newest, rather than primary key sequence. In any case where primary key sequence is not used - or where no primary key exists on the table - the two mysqldump files could have completely different sequences even though they have the identical rows. That would almost certainly preclude this approach working. Why not just try doing mysqldumps of each of your two tables and then do a file compare of the two files and see what happens? It shouldn't take long and you'll soon see if this approach will work. By the way, why are you keeping two independent - yet supposedly identical copies - of your tables? Isn't that redundant *and* expensive? Wouldn't it make more sense to backup a single copy of the database regularly so that you can restore when you have problems? Or, if you need the same database in two different locations, why not use replication to ensure that the two copies are automatically synchronised? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.9/116 - Release Date: 30/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
