comparing data from mysql backups

views:

144

answers:

comparing data from mysql backups

I've got a few backups of my database, and I'm trying to figure out if there is a way to compare the data in the backups to see how the data as changed over time. I do not have a timestamp on the fields I want to compare, but I do have a unique id on the row. The backups are .sql files created by using mysqldump.

Is there a process for doing this?

+1 A:

There are a number of diff tools for MySQL including a tool called MySQLDiff (http://www.mysqldiff.org/). There's also a command line interface for this that can be found here:

http://devzone.intellitree.com/projects/intellitree-cli-extension-to-mysqldiff/

See this previous StackOverFlow post for a visual diff tool similar to this (plus some other solutions):

http://stackoverflow.com/questions/218499/mysql-diff-tool

Jon 2009-05-20 02:14:10

it looks like for each of these tools I need to load the backups into parallel dbs. I was hoping their was a way to do this directly from the .SQL file. But I just looked at what I .sql file contains, and I can understand why it wouldn't work that way. I didn't realize mysqldump dumps the db into a bunch of mysql statements to recreate the db.

pedalpete 2009-05-20 02:47:52

+1 A:

You could theoretically import your backups as a second database, then write a PHP script that connects to your SQL server and iterates over entries by unique id, comparing as it goes.

You could also just use diff at the command line, but the resultant dataset may not be that workable.

Tim 2009-05-20 02:14:51

I was originally thinking of creating a php script, but was hoping I could do this without creating multiple instances of the same database (which it seems like I might need to do). I was hoping their was way to just look at the data in the .sql file

pedalpete 2009-05-20 02:24:53

If all that changed between the backups was the data in the set (not any of the table definitions or anything) then command-line diff will definitely work. It just may not produce pretty results. (But some bash magic should work that out :)

Tim 2009-05-20 02:58:35

I think I was misunderstanding before. I was looking at 'diff' as a mysql function, but now I see you mean just do a file compare diff. I think that should work.

pedalpete 2009-05-20 03:18:36

+1 A:

You could use a tool like WinMerge, or any other merge tool, to see an actual diff of the backups. The success of this is going to depend on the size of your files. I wouldn't recommend doing this for really large backups.

Kibbee 2009-05-20 02:15:55

If you do your backups using mysqldump, then your backup files will be standard text files. You can then use any decent text file compare utility (i.e. diff on a unix system) to see the differences.

One nice thing about this is the table schema is dumped along with the data.

Another benefit is that the backup is version and machine independant - you can restore onto any running copy of MySQL.

The downside is the data part of the dump is written as maximum length lines, so some text editors will choke on it.

Cheers,

-R

Huntrods 2009-05-20 03:43:37

ansaurus

tags:

views:

answers:

comparing data from mysql backups

related questions