pre-freeze-script

#!/usr/bin/php -q
<?php

/**
 * This is a script that would help flush MySQL database before VMware snapshot is taken, and then release 
 * the lock when snapshot is done
 * Intended use: Veeam backup, or any other VMware snapshot that needs to be consistent, for that matter
 * Usage: Place this file in /usr/sbin, and run these:
chmod 755 /usr/sbin/pre-freeze-script
ln -s /usr/sbin/pre-freeze-script /usr/sbin/post-thaw-script
ln -s /usr/sbin/pre-freeze-script /usr/sbin/pre-freeze-mysql-lock
 *
 * Check the config below, and set mysql login and password for the instance you're working with.
 * Then don't forget to check "Quiesce guest file system" when doing a backup
 * You can check log file to make sure that it is being called properly.
 * Oh, and don't forget to add the log file to "logrotate" configuration, or it might get large...
 */

/**
 * Configuration
 */

// MySQL connection parameters
$_config['mysqlLogin'] = 'root';
$_config['mysqlPass'] = '';
// Host name will always be localhost, as there is no point in locking a remote instance,
// as this one needs to be flushed

// File that will contain information about lock process (e.g. current state. Shared memory, if you will)
$_config['runFile'] = '/var/run/vmware-freeze-script';

// Log file where we would write down all the status changes
$_config['logFile'] = '/var/log/vmware-freeze-script.log';

// How long should we wait for the right time to do a snapshot (e.g. for long queries to finish)
// If we go beyond this time, script will terminate with exit code 1 (signaling an error)
$_config['snapshotTimeout'] = 600; // seconds

// How long should we wait every time we tried acquiring a lock
// Also used to determine maximum runtime of running queries beyond which we don't want to attempt lock acquisition
$_config['lockWaitTimeout'] = 10; // seconds

// For how long at most should we hold the lock once it is acquired
$_config['maxLockTime'] = 15; // seconds

// What exit code should be provided on error (like unable to acquire lock, can't connect to database, etc)
// 0 would mean normal exit, signaling to vmware it's ok to proceed, non-0 will cause snapshot to fail
$_config['errorExitCode'] = 1;

// When checking for long running queries, which "Command" values should be ignored
$_config['processlistIgnoreCommands'] = array('Sleep', 'Delayed insert', 'Binlog Dump', 'Connect');

// When true, all log messages are also sent to STDOUT
$_config['outputLog'] = false;

/**
 * END Configuration
 * Do not change anything beyond this point, unless you really know what you're doing.
 * And if so - submit a pull request so that others can take advantage of your changes too ;)
 */

// Connect to MySQL database first
$mysqli = new mysqli('localhost', $_config['mysqlLogin'], $_config['mysqlPass']);
if ($mysqli->connect_error) {
    doFail('MySQL connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// Determine what was intended to be ran, by looking at the command line
$command = basename($argv[0]);
doLog( "$command called");
switch ($command) {
    case 'pre-freeze-script': preFreeze();
        break;
    case 'post-thaw-script': postThaw();
        break;
    case 'pre-freeze-mysql-lock': mysqlLock();
        break;
    default: doFail("Unknown command $command!");
}

function preFreeze()
{
    global $_config;
    // Used to indicate if the lock acquisition process started or not;
    $started = false;
    for ($i = 1; $i < $_config['snapshotTimeout']; $i++) {
        // Check if we have any long running queries
        if (!$started && findMaxQueryExecution() < $_config['lockWaitTimeout']) {
            doLog("Not started, and no long running queries; Attempting lock acquisition.");
            exec('nohup /usr/sbin/pre-freeze-mysql-lock > /dev/null 2> /dev/null < /dev/null &');
            $started = true;
            // give it a bit of time to connect and stuff
            usleep( 300000);
        }
        if ($started && is_readable($_config['runFile'])) {
            doLog("Lock process started, looks like run file is readable.");
            // Check for the run file, and grab all information
            // We need PID of the process, connection ID to mysql, indication if the lock is started, acquired,
            //      or else (and time of that action)
            $processInfo = unserialize(file_get_contents($_config['runFile']));
            if ($processInfo['lockStatus'] == 'acquired') {
                doLog("Yup, Lock acquired!");
                exit(0);
            }
            if ($processInfo['lockStatus'] == 'waiting'
                && (time() - $processInfo['startTime']) > $_config['lockWaitTimeout']) {
                doLog("Lock status is waiting, and for longer than $_config[lockWaitTimeout] seconds. Killing the process.");
                // we're waiting for lock acquisition for too long, need to kill the process and mysql query
                postThaw();
            }
        }
        doLog( "Iteration $i of $_config[snapshotTimeout]");
        sleep(1);
    }
    doFail(" = TIMEOUT = . We've failed to acquire the lock.");
}

function postThaw()
{
    global $_config, $mysqli;
    $processInfo = unserialize(file_get_contents($_config['runFile']));
    doLog("Ending lock process, and cleaning up");
    // -9 for SIGKILL
    if ($processInfo['PID'] > 0) {
        exec(sprintf('kill -9 %d > /dev/null 2> /dev/null < /dev/null', $processInfo['PID']));
    }
    $mysqli->query( "KILL " . $processInfo['mysqlConnId']);
    unlink($_config['runFile']);
    exit(0);
}

function mysqlLock()
{
    global $_config, $mysqli;

    $run = array();
    // This will be our unix PID so that it can be killed in case it runs too long
    $run['PID'] = getmypid();
    // This will be the connection ID acquiring the lock in case we need to kill it
    if (!($result = $mysqli->query('SELECT CONNECTION_ID()'))) {
        doFail(sprintf('MySQL query error getting connection ID: %s', $mysqli->error));
    }
    $conn = $result->fetch_array();
    $run['mysqlConnId'] = (int)$conn[0];
    if ($run['mysqlConnId'] == 0) {
        doFail(sprintf('Error getting connection ID. Query result: %s', var_export($conn, true)));
    }
    // waiting means we sent the query, but did not get the lock yet
    $run['lockStatus'] = 'waiting';
    $run['startTime'] = time();
    file_put_contents($_config['runFile'], serialize($run));

    doLog("Trying to acquire the lock now...");
    if (!$mysqli->query('FLUSH TABLES WITH READ LOCK')) {
        doFail(sprintf('MySQL query error flushing tables: %s', $mysqli->error));
    }

    // Get master position; Useful when doind slave resync from snapshot or backup
    if ($result = $mysqli->query('SHOW MASTER STATUS')) {
        if ($result->num_rows > 0) {
            $row = $result->fetch_assoc();
            doLog(sprintf('Master file: %s; Position: %d', $row['File'], $row['Position']));
        } else {
            doLog('Skipping replication master status, as server does not seem to be set up as one.');
        }
    } else {
        doLog(sprintf('MySQL query error getting master status: %s', $mysqli->error));
    }

    // If we're here, we have the lock. Write this to the status file.
    $run['lockStatus'] = 'acquired';
    file_put_contents($_config['runFile'], serialize($run));
    // Log will be written by the parent process, so we're not doing it here now.

    // This will ensure that we release the lock from here if the snapshot process takes too long
    sleep($_config['maxLockTime']);
    // No error check, as at this point there is nothing we can do or signal.
    $mysqli->query('UNLOCK TABLES');
    $mysqli->close();

    $run['lockStatus'] = 'released';
    file_put_contents($_config['runFile'], serialize($run));
    doLog(' == Oops! == Lock was released due to the maxLockTime. Check why snapshot took so long.');

    exit(0);
}

function findMaxQueryExecution()
{
    global $mysqli, $_config;
    if (!($res = $mysqli->query('SHOW PROCESSLIST'))) {
        doFail(sprintf('MySQL query error while looking at processlist: %s', $mysqli->error));
    }
    $max = 0;
    while ($row = $res->fetch_assoc()) {
        //
        if ( $row['Time'] > $max && !in_array($row['Command'], $_config['processlistIgnoreCommands'])) {
            $max = $row['Time'];
        }
    }
    dolog(sprintf('Longest running query: %d', $max));
    return $max;
}

function doLog($message)
{
    global $_config;
    $d = DateTime::createFromFormat('U.u', microtime(true));
    $logMessage = $d->format("Y-m-d H:i:s.u ") . $message . PHP_EOL;
    if ($_config['outputLog']) {
        echo $logMessage;
    }
    if ($_config['logFile'] != '') {
        file_put_contents( $_config['logFile'], $logMessage, FILE_APPEND);
    }
}

function doFail($message)
{
    global $_config;
    doLog($message);
    exit($_config['errorExitCode']);
}