backup.php

<?php
/**
 * backup.php
 *
 * Cron-callable database backup script using mysqldump.
 * Creates timestamped gzip-compressed SQL dumps and prunes old backups
 * beyond the configured retention count.
 *
 * Cron example (daily at 2 AM):
 *   0 2 * * * /usr/bin/php /var/www/inventory/backup.php >> /var/log/inventory_backup.log 2>&1
 *
 * Environment variables (.env):
 *   BACKUP_DIR           — Directory for backups (default: ./backups)
 *   BACKUP_RETAIN_COUNT  — Number of backups to keep (default: 14)
 */

require_once __DIR__ . '/includes/config.php';

// Only allow CLI or admin browser access
$is_cli = (PHP_SAPI === 'cli');
if (!$is_cli) {
    require_once __DIR__ . '/includes/load.php';
    if (!isset($_SESSION['user_id'])) {
        http_response_code(403);
        exit('Access denied.');
    }
    $runner = find_by_id('users', (int) $_SESSION['user_id']);
    if (!$runner || (int) $runner['user_level'] > ROLE_ADMIN) {
        http_response_code(403);
        exit('Admin access required.');
    }
}

header('Content-Type: text/plain');

$timestamp = date('Y-m-d H:i:s');
echo "=== Database Backup — $timestamp ===\n\n";

// ---------------------------------------------------------------------------
// Configuration
// ---------------------------------------------------------------------------
$backup_dir    = getenv('BACKUP_DIR')          ?: __DIR__ . '/backups';
$retain_count  = (int) (getenv('BACKUP_RETAIN_COUNT') ?: 14);
$date_stamp    = date('Y-m-d_His');
$filename      = DB_NAME . "_$date_stamp.sql.gz";
$filepath      = rtrim($backup_dir, '/') . '/' . $filename;

// ---------------------------------------------------------------------------
// 1. Ensure backup directory exists
// ---------------------------------------------------------------------------
if (!is_dir($backup_dir)) {
    if (!mkdir($backup_dir, 0750, true)) {
        echo "ERROR: Could not create backup directory: $backup_dir\n";
        exit(1);
    }
}

// Protect backup directory from web access
$htaccess = rtrim($backup_dir, '/') . '/.htaccess';
if (!file_exists($htaccess)) {
    file_put_contents($htaccess, "Require all denied\n");
}

// ---------------------------------------------------------------------------
// 2. Verify mysqldump is available
// ---------------------------------------------------------------------------
$mysqldump = trim(shell_exec('which mysqldump 2>/dev/null') ?? '');
if (empty($mysqldump)) {
    echo "ERROR: mysqldump not found in PATH.\n";
    exit(1);
}

// ---------------------------------------------------------------------------
// 3. Run mysqldump
// ---------------------------------------------------------------------------
// Write a temporary defaults file so credentials don't appear in the process list.
$defaults_file = tempnam(sys_get_temp_dir(), 'mydb_');
file_put_contents($defaults_file, sprintf(
    "[client]\nhost=%s\nuser=%s\npassword=%s\n",
    DB_HOST, DB_USER, DB_PASS
));
chmod($defaults_file, 0600);

$cmd = sprintf(
    '%s --defaults-extra-file=%s --single-transaction --routines --triggers %s 2>&1 | gzip > %s',
    escapeshellarg($mysqldump),
    escapeshellarg($defaults_file),
    escapeshellarg(DB_NAME),
    escapeshellarg($filepath)
);

$output = [];
$return_code = 0;
exec($cmd, $output, $return_code);

// Always remove the temporary defaults file
if (file_exists($defaults_file) && !unlink($defaults_file)) {
    error_log('backup.php: failed to remove temporary mysql defaults file at ' . $defaults_file);
}

if ($return_code !== 0 || !file_exists($filepath) || filesize($filepath) < 100) {
    echo "ERROR: mysqldump failed (exit code $return_code).\n";
    if (!empty($output)) {
        echo implode("\n", $output) . "\n";
    }
    // Clean up empty/broken file
    if (file_exists($filepath) && !unlink($filepath)) {
        error_log('backup.php: failed to remove broken backup file at ' . $filepath);
    }
    exit(1);
}

$size_kb = round(filesize($filepath) / 1024, 1);
echo "Created: $filename ($size_kb KB)\n";

// ---------------------------------------------------------------------------
// 4. Prune old backups beyond retention count
// ---------------------------------------------------------------------------
$files = glob(rtrim($backup_dir, '/') . '/' . DB_NAME . '_*.sql.gz');
if ($files !== false && count($files) > $retain_count) {
    // Sort oldest first
    usort($files, function ($a, $b) { return filemtime($a) - filemtime($b); });
    $to_delete = array_slice($files, 0, count($files) - $retain_count);
    foreach ($to_delete as $old) {
        unlink($old);
        echo "Pruned: " . basename($old) . "\n";
    }
}

echo "\nDone. " . count($files) . " backup(s) on disk, retaining $retain_count.\n";