~~NOTOC~~
====== MySQL Backup with PHP ======

A PHP script that backs up MySQL databases at [[https://all-inkl.com/|ALL-INKL.COM]] (or anywhere else 😉). The script can be run with a KAS cron job.

===== Features =====

  * Backup folder is created automatically.
  * Maximum number of backups adjustable (older ones are deleted automatically).
  * Variable ''$dumpname'' for a better visual differentiation of the backup files (if e.g. hourly, daily and weekly backups should be stored in the same folder for the sake of simplicity).

===== Source Code =====

<file php mysql-backup-allinkl.php>
<?php
/**
 * MySQL backup via PHP for ALL-INKL.COM
 *
 * 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.
 * See also: https:all-inkl.com/wichtig/anleitungen/kas/ssh/dateiverwaltung/aenderung-der-php-cli-version_527.html)
 * 
 * Restore backup:
 * gunzip < /path/to/backupfile.sql.gz | mysql -h localhost -u username -p'password' databasename
 *
 * @author	Helmut Kaczmarek <email@helmutkaczmarek.de>
 * @link	https://wiki.helmutkaczmarek.de/code:php:backup:mysql
 */
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;
}
?>
</file>