Home » Howtos » mysql

MySQL Database Diff Script

Submitted by adam on Tue, 2007-12-25 22:46.

I know there's a few MySQL database structure difference detection scripts out there (mysqldiff for perl and mysqldiff for php) but the two I found didn't meet my needs: simple to get running, portable. So, I created my own which simply looks at the tables and if anything is different or missing reports the table name so that I can look at it by hand. You can download the code or look below. If anyone wants to expand on it while still keeping it as a single file then I would appreciate the updates emailed so I can add it to here (adam @ this domain) or a link posted below.


<?php
$src_host = '';
$src_user = '';
$src_pass = '';
$src_db = '';

$dst_host = '';
$dst_user = '';
$dst_pass = '';
$dst_db = '';

$src = mysql_connect($src_host, $src_user, $src_pass);
if (!mysql_select_db($src_db, $src)) die("Could not find/USE source database: {$src_db}\n");

$src_tables = getTables($src);
getCreateStatements($src, $src_tables);

$dst = mysql_connect($dst_host, $dst_user, $dst_pass);
if (!mysql_select_db($dst_db, $dst)) die("Could not find/USE destination database: {$dst_db}\n");

$dst_tables = getTables($dst);
getCreateStatements($dst, $dst_tables);

foreach ($src_tables as $table) {
foreach ($dst_tables as $key=>$dst_table) {
$found = false;
if ($dst_table->name == $table->name) {
if ($dst_table->create != $table->create) {
echo "{$table->name} is different\n";
}
unset($dst_tables[$key]);
$found = true;
break;
}
}
if (!$found) {
echo "{$table->name} does not exist in destination\n";
}
}

function getCreateStatements($link, $tables) {
foreach ($tables as $table) {
$table->create = getCreateStatement($link, $table->name);
}
}

function getTables($link) {
$rsrc = mysql_query('SHOW TABLES', $link);
$tables = array();
while ($row = mysql_fetch_row($rsrc)) {
$table = new table($row[0]);
$tables[] = $table;
}
return $tables;
}

function getCreateStatement($link, $table) {
$rsrc = mysql_query("SHOW CREATE TABLE {$table}");
$row = mysql_fetch_row($rsrc);
$create = preg_replace('/ AUTO_INCREMENT\=\d+/','',$row[1]);
return $create;
}
class table {
function table($name) {
$this->name = $name;
}
}

»