rahmatdcc.blogspot.com

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