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