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
Anonymous (not verified)
Sat, 07/11/2009 - 00:32
Permalink
Sounds useful!
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! :)
Anonymous (not verified)
Thu, 05/27/2010 - 07:17
Permalink
Thank you. Just what I
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.
Anonymous (not verified)
Wed, 06/02/2010 - 03:16
Permalink
ailove
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.
Anonymous (not verified)
Wed, 06/02/2010 - 03:18
Permalink
ailove
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.
Anonymous (not verified)
Mon, 03/21/2011 - 04:41
Permalink
Building Materials
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.
Anonymous (not verified)
Thu, 03/31/2011 - 19:22
Permalink
That's a useful looking
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
Anonymous (not verified)
Tue, 05/24/2011 - 13:14
Permalink
Brilliant post and useful
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