<?php
/**
 * MySQL Backup for ALL-INKL.COM
 * 
 * Copyright (c) 2018 Helmut Kaczmarek <email@helmutkaczmarek.de>
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * 
 * Usage:
 * If the script is to be executed automatically via KAS cron job, it must 
 * be accessible on the web. Alternatively, it can be run manually via 
 * shell with the following command: "php /path/to/script.php"
 * 
 * Pitfall wrong PHP CLI version, then e.g.
 * "/usr/bin/php81 /path/to/script.php" for PHP 8.1
 * 
 * Restore backup:
 * gunzip < /path/to/backupfile.sql.gz | mysql -h localhost -u username 
 * -p'password' databasename
 * 
 * @author Helmut Kaczmarek <email@helmutkaczmarek.de>
 * @link https://www.helmutkaczmarek.de
 * @todo You could also back up the directories to a remote server via 
 * SSH. However, at this point one would probably no longer be dependent on 
 * the "trimmed" KAS cron jobs from ALL-INKL.COM. A bash script and crontab 
 * would of course be the first choice in this case. 
 */
header('Content-Type: text/html; charset=utf-8');
 
// MySQL connection data
$host = 'localhost';
$user = 'username';
$password = 'passwort';
$database = 'databasename';
$dumpname = 'hourly'; // For visual distinction if, for example, "hourly", "daily" and "weekly" backups are stored in the same folder.
 
// Directory for the backups (if not present, then create)
$backupDirectory = '/www/htdocs/vXXXXXX/path/to/folder/';
if (!is_dir($backupDirectory)) {
    mkdir($backupDirectory, 0777, true);
}
 
// Number of backups to keep
$maxBackups = 3;
 
// Create backup file
$backupFile = $backupDirectory . $dumpname . '_' . $database . '_' . date('Y-m-d_H-i-s') . '.sql.gz';
 
// Backup and zip database
$command = "mysqldump -h {$host} -u {$user} -p{$password} {$database} | gzip > {$backupFile}";
exec($command, $output, $returnVar);
 
if ($returnVar === 0) {
    echo "Database backed up successfully: {$backupFile}";
 
    // Get a list of existing backups
    $backupFiles = glob($backupDirectory . $dumpname . '_' . $database . '_*.sql.gz');
    if (count($backupFiles) > $maxBackups) {
        // Sort backups by date (oldest first)
        usort($backupFiles, function ($a, $b) {
            return filemtime($a) > filemtime($b);
        });
 
        // Delete older backups
        $filesToDelete = array_slice($backupFiles, 0, count($backupFiles) - $maxBackups);
        foreach ($filesToDelete as $file) {
            echo "<br>Deleted: " . $file;
            unlink($file);
        }
    }
} else {
    echo "Error saving database: " . $output;
}
?>