Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Use a PHP script to monitor SQL statements logged inside MySQL/MariaDB
<?php
// DataTable.net initialization options
// https://datatables.net/reference/option/
// Turn on MySQL/MariaDB global sql statement debugging
// https://stackoverflow.com/a/14403905/374316
// Setup db admin user.
if (!empty($_GET['dbrootuser'])) {
$dbusername = $_GET['dbrootuser'];
} else {
$dbusername = 'changeme::root_username';
}
// Setup db admin password.
if (!empty($_GET['dbrootpassword'])) {
$dbrootpassword = $_GET['dbrootpassword'];
} else {
$dbrootpassword = 'changeme::db_root_password';
}
$servername = "localhost";
$dbname = "mysql";
// Create connection
$conn = new mysqli($servername, $dbusername, $dbrootpassword, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Enbale/Disable MySQL debugging.
if (!empty($_GET['debugging'])) { // false,true
$debugging = $_GET['debugging'];
if ($debugging) {
$debugging_setup = "SET global log_output = 'table'";
$result = $conn->query($debugging_setup);
}
$debugging_setup = "SET global general_log = $debugging";
$result = $conn->query($debugging_setup);
}
// Filter sql statement coming from a specific dbuser.
if (!empty($_GET['filterdbuser'])) {
$filterdbuser = $_GET['filterdbuser'];
} else {
$filterdbuser = 'changeme::moodle_db_admin_user';
}
$sql = "SELECT event_time, argument FROM general_log WHERE user_host LIKE '%" . $filterdbuser . "%' ORDER BY event_time ASC";
$result = $conn->query($sql);
echo "<table id='sqllog' style='width: 100%' class='display'><thead><tr><td>time</td><td>sql</td></tr></thead><tbody>";
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["event_time"]. "</td><td>" . $row["argument"]. "</td></tr>";
}
} else {
echo "0 results";
}
$conn->close();
echo "</tbody></table>";
echo "<script src='https://code.jquery.com/jquery-1.12.4.min.js' integrity='sha256-ZosEbRLbNQzLpnKIkEdrPv7lOy9C27hHQ+Xp8a4MxAQ=' crossorigin='anonymous'></script>";
echo "<link rel='stylesheet' type='text/css' href='https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css' media='screen' />";
echo "<script src='https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js'></script>";
echo "<script> $(document).ready( function () {
$('#sqllog').DataTable({
'lengthMenu': [ [10, 35, 50, -1], [10, 35, 50, 'All'] ]
});} ); </script>";
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.