WL#5036: Backup: meta-data diff tool

Affects: WorkLog-3.4   —   Status: On-Hold   —   Priority: Medium

When a DBA gets to the point of considering to RESTORE a backed up database, he
may be in doubt about the contents of the backup image. Was this backup made
before or after I changed from MyISAM to InnoDB? Does the backup image contain
the modifications I made to the procedures?

One way to get this information is to use the backupclient tool. The tool will
among other things show you all meta-data information. However, if the database
consists of many objects (thousands of tables etc), it will be difficult and
very time consuming to manually identify all differences.

It would be good to have a diff-like tool that would output the difference
between the current database and the backed up database.

The database consists of two different parts: the meta-data (the database
schema, user privileges etc) and the data itself. This WL shall handle the
meta-data part of the database [1]. 

There are multiple suggestions for how the syntax for this tool should be:

* Command Line tool, e.g. integrated in backupclient
* SQL command
  - Option to restore, similar to "RESTORE 'my_backup.bak' COMPARE_SCHEMA"
  - New command, similar to "PREPARE RESTORE FROM 'my_backup.bak' [CHECK|DIFF]"

The syntax is yet to be decided.

The idea was originally emailed to mysql-backup list [29 Jun 2009 10:19] "Draft
idea - New option to RESTORE command". See email thread for discussion:

[1] While it could be equally interesting to show the diff of the data in the
image compared to the currently active database, this is a much more difficult
problem to solve. The data in the backup image is only occasionally stored in a
human readable format. Further more, the data in the image is not ordered in any
other way than the storage engine considered optimal (read performance) at
BACKUP time.