MySQL Database Heartbeat Checker with Telegram Notification in PHP

.database-heartbeat.env

# DATABASE CREDENTIALS
DB_HOST=localhost
DB_USER=YOUR-USER
DB_PASS=YOUR-USER-PASSWORD
DB_NAME=YOUR-DATABASE-NAME # Optional

# TELEGRAM CONFIGURATION
# CREATE YOUR BOT AND GET ITS IDS FROM BotFather ON TELEGRAM
TELEGRAM_CHANNEL_ID=-123...9
TELEGRAM_BOT_TOKEN=7089...4g

# SERVICE CONFIGURATION
SERVICE_NAME=YOUR-SERVICE-NAME
MAX_FAILURES=2
COUNTER_FILE=/tmp/mysql_check_counter

database-heartbeat.php

<?php

/**
 * MySQL/MariaDB Health Monitor + Telegram Notification (PHP version)
 * ------------------------------------------------------------
 * Reads configuration from a .env file.
 * Checks DB connectivity via mysqli.
 * Sends Telegram alert if DB is down or restarted.
 * Resets failure counter when DB is back up.
 *
 * Schedule exemple with CRONTAB
 * @use `crontab -e` on your terminal to edit the CRONTAB file
 * * * * * * /bin/php /path/to/database-heartbeat.php >> /var/log/database-heartbeat.log 2>&1
 */

// === Load .env file ===
$envFile = __DIR__ . "/.database-heartbeat.env";
if (!file_exists($envFile)) {
    die("Error: .env file not found. Aborting.\n");
}

// Parse .env into associative array
$env = [];
foreach (file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $line) {
    if (strpos(trim($line), "#") === 0) continue; // skip comments
    [$key, $value] = array_map("trim", explode("=", $line, 2));
    $env[$key] = $value;
}

// === Configuration from .env ===
$dbHost  = $env["DB_HOST"] ?? "localhost";
$dbUser  = $env["DB_USER"] ?? "root";
$dbPass  = $env["DB_PASS"] ?? "";
$dbName  = $env["DB_NAME"] ?? "";

$telegramChannelId = $env["TELEGRAM_CHANNEL_ID"] ?? "";
$telegramBotToken  = $env["TELEGRAM_BOT_TOKEN"] ?? "";
$serviceName       = $env["SERVICE_NAME"] ?? "My Service";

$maxFailures = (int) ($env["MAX_FAILURES"] ?? 2);
$counterFile = $env["COUNTER_FILE"] ?? "/tmp/mysql_check_counter";

if (!$telegramChannelId || !$telegramBotToken) {
    die("Error: Telegram configuration is missing. Aborting.\n");
}

// === Initialization ===
if (!file_exists($counterFile)) {
    file_put_contents($counterFile, time() . " 0");
}

$counterFileContent = file_get_contents($counterFile);
$counterFileContent = explode(" ", trim($counterFileContent));

if (count($counterFileContent) === 1) {
    // Backward compatibility with previous format
    $counter = (int) $counterFileContent[0];
} else {
    $lastCheckTimestamp = (int) $counterFileContent[0];
    $counter = (int) $counterFileContent[1];
    
    if ($lastCheckTimestamp > time() - 60) {
        die("Info: Last execution was less than a minute ago. Skipping.\n");
    }
}

// === Health Check ===
$isUp = false;

try {
    $mysqli = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
    $isUp = !$mysqli->connect_errno;
} catch (Exception $e) {
    $isUp = false;
}

if ($isUp) {
    echo "MySQL/MariaDB is up and running.\n";
    file_put_contents($counterFile, time() . " 0");
    exit(0);
}

echo "MySQL/MariaDB is down.\n";
file_put_contents($counterFile, time() . " " . ($counter + 1));

$notificationMessage = "MySQL/MariaDB is down for $serviceName.";

// === Restart logic ===
if ($counter >= $maxFailures) {
    echo "Restarting MySQL/MariaDB...\n";
    exec("service mysql restart 2>&1", $out, $status);
    
    if ($status !== 0) {
        echo "Failed to restart MySQL/MariaDB for $serviceName! Retrying in 1 minute.\n";
    }
    
    echo "MySQL/MariaDB has been restarted.\n";
    $notificationMessage = "MySQL/MariaDB was restarted for $serviceName.";
    file_put_contents($counterFile, time() . " 0");
}

// === Send Telegram notification ===
$telegramUrl = "https://api.telegram.org/bot$telegramBotToken/sendMessage";
$postData = [
    "chat_id" => $telegramChannelId,
    "text"    => $notificationMessage,
];

$ch = curl_init($telegramUrl);
curl_setopt_array($ch, [
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_POST           => true,
    CURLOPT_POSTFIELDS     => $postData,
    CURLOPT_TIMEOUT        => 10,
]);
curl_exec($ch);
curl_close($ch);