Buka folder berikut :
pico /usr/local/bin/monitor_mysql.php
Copy Paste script php berikut :
<?php
// ============================================
// KONFIGURASI
// ============================================
$config = [
'db_host' => 'xxxxx',
'db_port' => xxxx,
'db_user' => 'xxxx',
'db_pass' => 'xxxx',
'service_name' => 'mysql',
// Log hanya berisi kejadian mati/restart
'log_file' => __DIR__ . '/mysql_down.log',
'connect_timeout' => 5,
'restart_cooldown' => 300,
'send_email' => false,
'email_to' => 'admin@example.com',
];
// ============================================
// FUNGSI OUTPUT
// ============================================
function isCli() {
return php_sapi_name() === 'cli';
}
// Hanya untuk info saat MySQL hidup → TIDAK ditulis ke log
function info($message) {
if (isCli()) {
echo "[INFO] {$message}" . PHP_EOL;
}
}
// Untuk kejadian penting (mati/restart/gagal) → ditulis ke log
function writeLog($message, $level = 'INFO') {
global $config;
$timestamp = date('Y-m-d H:i:s');
$line = "[{$timestamp}] [{$level}] {$message}" . PHP_EOL;
@file_put_contents($config['log_file'], $line, FILE_APPEND | LOCK_EX);
if (isCli()) {
echo $line;
}
}
// ============================================
// CEK MYSQL
// ============================================
function isMysqlAlive() {
global $config;
$mysqli = @new mysqli(
$config['db_host'],
$config['db_user'],
$config['db_pass'],
null,
$config['db_port']
);
if ($mysqli->connect_errno) {
return false;
}
$result = @$mysqli->query("SELECT 1");
$mysqli->close();
return $result !== false;
}
function isMysqlServiceActive() {
global $config;
$service = escapeshellarg($config['service_name']);
$output = [];
$return_code = 0;
exec("systemctl is-active {$service} 2>&1", $output, $return_code);
$status = trim(implode('', $output));
return $return_code === 0 && $status === 'active';
}
// Ambil info versi & uptime MySQL (opsional, untuk tampilan saat hidup)
function getMysqlInfo() {
global $config;
$mysqli = @new mysqli(
$config['db_host'],
$config['db_user'],
$config['db_pass'],
null,
$config['db_port']
);
if ($mysqli->connect_errno) {
return null;
}
$info = [
'version' => $mysqli->server_info,
'host' => $mysqli->host_info,
];
// Uptime
$res = @$mysqli->query("SHOW GLOBAL STATUS LIKE 'Uptime'");
if ($res && $row = $res->fetch_row()) {
$info['uptime'] = (int) $row[1];
}
// Jumlah koneksi aktif
$res = @$mysqli->query("SHOW GLOBAL STATUS LIKE 'Threads_connected'");
if ($res && $row = $res->fetch_row()) {
$info['threads_connected'] = (int) $row[1];
}
$mysqli->close();
return $info;
}
function formatUptime($seconds) {
$d = floor($seconds / 86400);
$h = floor(($seconds % 86400) / 3600);
$m = floor(($seconds % 3600) / 60);
$s = $seconds % 60;
$parts = [];
if ($d > 0) $parts[] = "{$d}h";
if ($h > 0) $parts[] = "{$h}j";
if ($m > 0) $parts[] = "{$m}m";
$parts[] = "{$s}s";
return implode(' ', $parts);
}
// ============================================
// RESTART
// ============================================
function restartMysql() {
global $config;
$service = escapeshellarg($config['service_name']);
$cmd = "sudo systemctl restart {$service}";
$output = [];
$return_code = 0;
exec($cmd . ' 2>&1', $output, $return_code);
$output_str = trim(implode("\n", $output));
if ($return_code === 0) {
return ['ok' => true, 'output' => $output_str];
}
return ['ok' => false, 'output' => $output_str, 'code' => $return_code];
}
// ============================================
// COOLDOWN
// ============================================
function canRestart() {
global $config;
$cooldown_file = sys_get_temp_dir() . '/mysql_monitor_last_restart';
if (file_exists($cooldown_file)) {
$last_restart = (int) file_get_contents($cooldown_file);
$elapsed = time() - $last_restart;
if ($elapsed < $config['restart_cooldown']) {
return $config['restart_cooldown'] - $elapsed;
}
}
return true;
}
function markRestartTime() {
$cooldown_file = sys_get_temp_dir() . '/mysql_monitor_last_restart';
file_put_contents($cooldown_file, time(), LOCK_EX);
}
// ============================================
// NOTIFIKASI EMAIL (OPSIONAL)
// ============================================
function sendNotification($subject, $message) {
global $config;
if (!$config['send_email']) {
return;
}
$headers = "From: mysql-monitor@" . gethostname() . "\r\n";
$headers .= "Content-Type: text/plain; charset=UTF-8\r\n";
@mail($config['email_to'], $subject, $message, $headers);
}
// ============================================
// PROSES UTAMA
// ============================================
$mysql_alive = isMysqlAlive();
$service_active = isMysqlServiceActive();
// ============================================
// KONDISI 1: MySQL HIDUP
// ============================================
if ($mysql_alive && $service_active) {
info("MySQL HIDUP ✓");
info("Service : {$config['service_name']} (systemd: ACTIVE)");
info("Host : {$config['db_host']}:{$config['db_port']}");
$mysqlInfo = getMysqlInfo();
if ($mysqlInfo) {
info("Versi : {$mysqlInfo['version']}");
info("Host info: {$mysqlInfo['host']}");
if (isset($mysqlInfo['uptime'])) {
info("Uptime : " . formatUptime($mysqlInfo['uptime']));
}
if (isset($mysqlInfo['threads_connected'])) {
info("Koneksi : {$mysqlInfo['threads_connected']} aktif");
}
}
info("Waktu : " . date('Y-m-d H:i:s'));
info("Log : tidak ada entri baru (MySQL normal)");
// TIDAK menulis ke log, hanya stdout
exit(0);
}
// ============================================
// KONDISI 2: MySQL MATI
// ============================================
writeLog("=== MySQL terdeteksi DOWN ===", 'WARNING');
writeLog("Koneksi MySQL: " . ($mysql_alive ? "OK" : "GAGAL"), 'WARNING');
writeLog("Service systemd: " . ($service_active ? "ACTIVE" : "INACTIVE"), 'WARNING');
$cooldown = canRestart();
if ($cooldown !== true) {
writeLog("Restart dibatalkan (cooldown aktif, tunggu {$cooldown} detik).", 'WARNING');
exit(1);
}
sendNotification(
"[ALERT] MySQL Down di " . gethostname(),
"MySQL terdeteksi mati pada " . date('Y-m-d H:i:s') . "\n" .
"Mencoba restart otomatis..."
);
writeLog("Mencoba restart service {$config['service_name']}...", 'INFO');
$result = restartMysql();
markRestartTime();
if ($result['ok']) {
writeLog("Perintah restart berhasil dijalankan. Menunggu 10 detik...", 'INFO');
sleep(10);
$mysql_alive = isMysqlAlive();
$service_active = isMysqlServiceActive();
if ($mysql_alive && $service_active) {
writeLog("✅ MySQL BERHASIL di-restart dan AKTIF kembali.", 'SUCCESS');
sendNotification(
"[RESOLVED] MySQL berhasil di-restart di " . gethostname(),
"MySQL berhasil di-restart pada " . date('Y-m-d H:i:s')
);
exit(0);
} else {
writeLog("❌ MySQL MASIH MATI setelah restart!", 'ERROR');
if (!empty($result['output'])) {
writeLog("Output: " . $result['output'], 'DEBUG');
}
sendNotification(
"[CRITICAL] MySQL gagal restart di " . gethostname(),
"MySQL masih mati setelah restart. Perlu penanganan manual!"
);
exit(2);
}
} else {
writeLog("❌ Perintah restart GAGAL (exit code: {$result['code']}).", 'ERROR');
if (!empty($result['output'])) {
writeLog("Output: " . $result['output'], 'ERROR');
}
sendNotification(
"[CRITICAL] Gagal restart MySQL di " . gethostname(),
"Perintah restart gagal. Cek konfigurasi sudo."
);
exit(3);
}
Edit crontab seperti berikut :
*/5 * * * * /usr/bin/php /usr/local/bin/monitor_mysql.php >> /var/log/mysql_monitor_stdout.log 2>&1
