makes mysqldump every N seconds, shows colordiff of two latest dumps

watch-mysql-diff.php

<?php

/*
    makes mysqldump every N seconds, shows colordiff of two latest dumps.
    useful for debugging: you see all changes made in database tables in last N seconds.
    removes metadata from table so that only contents are diff'ed

    requires mysqldump, colordiff

    usage:
        define constants
        run php watch-mysql-diff.php
*/

define('DEBUG', false);

define('DB_NAME', 'db-name');
define('DB_PWD', '');

function buildCommand($filename) {
    return 'mysqldump -p"'.DB_PWD.'" -u root '.DB_NAME.' --xml | sed \'s/ Auto_increment="[0-9]*"//g\' | sed \'s/ Cardinality="[0-9]*"//g\' | sed \'s/ Avg_row_length="[0-9]*"//g\' | sed \'s/ Auto_increment="[0-9]*"//g\' | sed \'s/ Rows="[0-9]*"//g\' > '.$filename;
}

function generateFilename() {
    exec('mkdir -p /tmp/mysqldiffs');
    return '/tmp/mysqldiffs/'.date('Y-m-d_His').'.xml';
}

$previousRevisionFile = '';
$currentRevisionFile = '';
$j = 0;

while (true) {
    if ($previousRevisionFile && ($j>1)) {
        if (DEBUG) {
            echo '[removing old revision] rm '.$previousRevisionFile.PHP_EOL;
        }
        exec('rm '.$previousRevisionFile);
    }

    $file = generateFilename();

    if (!$previousRevisionFile) {
        $previousRevisionFile = $file;
    }
    if ($currentRevisionFile) {
        $previousRevisionFile = $currentRevisionFile;
    }
    $currentRevisionFile = $file;

    if (DEBUG) {
        echo '[new sqldump] '.buildCommand($file).PHP_EOL;
    }
    exec(buildCommand($file));

    $colordiffCommand = 'colordiff '.$previousRevisionFile.' '.$currentRevisionFile;

    echo $colordiffCommand.PHP_EOL;

    $output = null;
    exec($colordiffCommand, $output);
    echo implode(PHP_EOL, $output);

    $sleepWait = 20;

    echo PHP_EOL.'[sleep '.$sleepWait.'s]';

    for ($i = 0; $i < $sleepWait; $i++) {
        sleep(1);
        echo '.';
    }

    echo PHP_EOL;
    $j++;
}