0

I'm in the process of migrating from one MySQL server (5.0.95) to out new MySQL server (5.1.73). I have a script that is transferring the schema's over and doing a table checksum using CHECKSUM TABLE schema.table; on both the source and destination servers. This process on my testing schema can validate all but 90 of my 930 tables. For those 90 how can I find out what the differences are?

I've tried saving them both, source and destination, out with mysqldump and using Beyond Compare on the resulting sql files. This doesn't go well since Beyond Compare will split the insert lines where it chooses and in turn finds differences where it has done that. Is there a tool or other MySQL check that I can do to find out why tables aren't the same?

Due to company policy I have to use RHEL 6 thus is why the old server is going away since it is on RHEL 5 and won't support RHEL 6.

  • As it turns out. In MySQL v5.0.95 doubles can have a value of -0. In MySQL that is not allowed. That is where my checksum differences are coming from. – Keith Bux Mar 10 '17 at 19:39
0
mysqldump --skip-extended-insert

to break up the inserts.

Or

 SELECT ... INTO OUTFILE ... ORDER BY ...

to make sure the order is identical, thereby helping the diff program. (This assumes you have a PRIMARY or UNIQUE key.)

  • The extended insert option reduces the number of inserts in the resulting sql file. That's the problem with Beyond Compare, which is a text diff tool. I will try the outfile why though when I get into work on Monday. – Keith Bux Mar 4 '17 at 12:53
  • I was asleep at the switch -- I added skip. – Rick James Mar 4 '17 at 17:47
  • I'll try the skip version of it on Monday. – Keith Bux Mar 4 '17 at 20:06
  • I had to add the --skip-extended-insert after the -opt. After that it works great. Thanks. – Keith Bux Mar 6 '17 at 21:17
1

MySQL Utility mysqldbcompare (https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldbcompare.html) may be of help. It does provide details on any mismatches it finds when comparing objects (not just tables) on two MySQL Servers.

  • My old server is to old for the utility to work. Just tried that. – Keith Bux Mar 3 '17 at 17:33
0

CHECKSUM TABLE not warrant same checksum between MySQL versions, it always present as notice to this function in docs

use text diff tools over dumps - it also not a best choice,

The first and quick tests (very quick for understanding direction of feature search):

  • compare structure
  • compare number of rows

than, possible:

  • if space not a problem - import tables in different schema and compare by SQL selects
  • I often use DBSolo as tools with may be one of the best compare and search function (Data compare) but there are many similar tools available

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.