MySQL Database Diff Script

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.


$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;
}
}

Comments

That's a useful looking script! Thought I'd point out that there's no syntax highlighting or indentation in the post, though.

I landed on your site when Googling the openssl "Unable to write random state" error. Nice site! :)

Thank you. Just what I needed. But there are two errors when using the script for my tables:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in tablediff.php on line 59
You will have to put the tablename in backticks. And you will have to differ between views and regular tables.

One hot day, the boy and the tree was . “Come and play with me!” the said. “I am sad and old. I want to go to relax myself. Can you give me a ?”“Use my trunk to build your boat. You can sail far away and be happy.” So the boy cut the tree to make a boat. He went sailing and never showed up for a long time. The tree was happy, but it was not true.

One day, the boy back to the and he looked sad. “Come and with me,” the tree asked the . “I am no longer a kid, I don’t play around trees anymore.” The boy , “I want toys. I need money to buy .”“Sorry, but I don’t have ...but you can pick all my apples and sell them. So, you will have money.
” The boy was so excited. He all the apples on the tree and left happily. The boy never came back after he picked the apples. The tree was sad.

Thanks for taking the time to discuss this http://www.buildingmaterials.co.uk/Home-Renovation" rel="follow">Home renovations Costs , I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with extra information? It is extremely helpful for me.

That's a useful looking script! Thought I'd point out that there's no syntax highlighting or indentation in the post, though.

I landed on your site when Googling the openssl "Unable to write random state" error. Nice site! :)

Penis" rel="nofollow">http://www.bonermagic.com/">Penis Enlargers

Brilliant post and useful information…I think this is what I read somewhere…but I don’t know with your experience… increases my knowledge Forerunner" rel="nofollow">http://www.runtheline.com/1671/garmin-forerunner-610-review">Forerunner 610