mysql-query-log.php

<?php
/**
 * @author  Leonid Vinikov
 * @version 1.0.3
 *
 * Created at: 2018-01-19
 * Updated at: 2018-05-03
 * Idea: https://github.com/jdorn/sql-formatter/blob/master/lib/SqlFormatter.php
 * Updates:
 * 2018-05-03 - grep added - version 1.0.1
 * 2018-05-30 - sql formater added - version 1.0.2
 * 2018-06-11 - add hide binary
 */


define( "FORMAT_SQL", "1" );
define( "HIDE_BINARY", "1" );

if ( FORMAT_SQL ) {
	$remotescript = 'http://raw.githubusercontent.com/jdorn/sql-formatter/master/lib/SqlFormatter.php';
	$content = file_get_contents( $remotescript );
	file_put_contents( '/tmp/' . basename( $remotescript ), $content );
	require( '/tmp/' . basename( $remotescript ) );
}

$logFolder = "queryLog/";
$logFile = "query-" . date( 'Y-m-d' ) . ".log";

function makeLog( $logFolder, $logFile ) {
	$path = $logFolder . $logFile;

	if ( ! file_exists( "./$logFolder" ) ) {
		mkdir( "./$logFolder" );
	}

	if ( ! file_exists( $path ) ) {
		file_put_contents( $path, "" );
	}

	return $path;
}

function writeLog( $logPath, $output ) {
	echo $output;
	file_put_contents( $logPath, $output, FILE_APPEND );
}

function getProcessList( $mysqli, $grep = null ) {
	$processList = array();

	$result = $mysqli->query( "show full processlist" );

	while ( $row = $result->fetch_array( MYSQLI_NUM ) ) {
		if ( ! empty( $grep ) && ! strstr( $row[7], $grep ) ) {
			continue;
		}

		unset( $row[1] );
		unset( $row[2] );
		unset( $row[3] );
		unset( $row[5] );
		unset( $row[6] ); // state

		if ( HIDE_BINARY ) {
			if ( strstr( $row[7], '_binary' ) ) {
				continue;
			}
		}

		if ( FORMAT_SQL ) {
			$row[7] = SqlFormatter::format( PHP_EOL . $row[7] );
		}

		unset( $row[8] );
		$processList[] = implode( "\t", $row );
	}

	# remove self `show full processlist`
	//array_pop($processList);

	return implode( "\n", $processList );
}

# MAIN :
$grep = null;

if ( isset( $argv[1] ) ) {
	$grep = $argv[1];
}

$mysqli = new mysqli( "localhost", "admin", "mysql", "" );

if ( $mysqli->connect_errno ) {
	exit( "error: cannot connect MySQL Server." . PHP_EOL );
}

$logPath = makeLog( $logFolder, $logFile );

echo "info: logPath: `$logPath`" . PHP_EOL;
echo "------------------------------------------------------------------------------------" . PHP_EOL;

$processList = getProcessList( $mysqli );

if ( empty( $processList ) ) {
	exit( "error: empty process list." . PHP_EOL );
}

$processListLastChecksum = crc32( $processList );

writeLog( $logPath, $processList . PHP_EOL );

do {
	$processList = getProcessList( $mysqli, $grep );
	if ( $processListLastChecksum == crc32( $processList ) ) {
		continue;
	}

	writeLog( $logPath, "info: process list checksum changed" . PHP_EOL );
	writeLog( $logPath, "------------------------------------------------------------------------------------" . PHP_EOL );

	writeLog( $logPath, $processList . PHP_EOL );
	writeLog( $logPath, "------------------------------------------------------------------------------------" . PHP_EOL );

	$processListLastChecksum = crc32( $processList );
} while ( true );