MySQL Diff Tool
After searching for a while, I haven’t been able to find a tool which will show the diffs between two MySQL Databases. There are plenty to handle migrations, but migrations are tough when you’re writing an app which is install by an end-user. So I wrote a tool/class which will take the XML of a proper database (that file can be distributed in your package), and then will compare the XML schema against the schema in the current database.
Generate a MySQL Dump file:
|
1 |
mysqldump --xml --no-data testuser -utestuser -ptest1 > structure.xml |
Then call the command line script (diffgen):
|
1 2 3 4 5 6 7 8 |
diffgen -utestuser -ptest1 -dtestuser -hlocalhost -fstructure.xml -tshow
-u Database User
-p Database Password
-d Database Name
-h Database Host
-f Dump File Path
-t "show" or "run" - show will output the SQL, "run" will run the SQL |
There’s also a class file (which it is all from), which you can use to integrate into your own custom scripts (as-is the case with phpVMS, which is distributed with the structure.xml that is generated by my Phing build process, and it “shapes” the database on the remote server properly in an update script).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
include 'MySQLDiff.class.php';
$params = ;
try {
$diff = new MySQLDiff(array(
'dbuser' => 'testuser',
'dbpass' => 'test1',
'dbname' => 'testuser',
'dbhost' => 'localhost',
'dumpxml' => 'structure.xml',
));
} catch(Exception $e) {
echo $e->getMessage(); exit;
}
# This returns an array of what's missing in the database
try {
$diff_lines = $diff->getDiffs();
var_dump($diff_lines);
catch(Exception $e) {
echo $e->getMessage(); exit;
}
# This returns SQL queries which can be run to fix the database
try {
$diff_lines = $diff->getSQLDiffs();
var_dump($diff_lines);
} catch(Exception $e) {
echo $e->getMessage(); exit;
}
# This generates the SQL and actually runs all of them
try {
$diff_lines = $diff->runSQLDiff();
var_dump($diff_lines);
} catch(Exception $e) {
echo $e->getMessage(); exit;
} |
The script can be downloaded from https://github.com/nshahzad/MySQLDiff
Pingback: on Planet CakePHP « Afzet Inc.