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

»

ailove

Submitted by Anonymous on Wed, 2010-06-02 07:18.

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

»

ailove

Submitted by Anonymous on Wed, 2010-06-02 07:16.

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

»

Thank you. Just what I

Submitted by Anonymous on Thu, 2010-05-27 11:17.

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.

»

Sounds useful!

Submitted by Anonymous on Sat, 2009-07-11 04:32.

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! :)

»