Chunked database exports to bypass max_statement_time

db-export.php

<?php
// ===========================================================================
// Database backup tool for InfinityFree hosting.
//
// GET  -> shows a settings form.
// POST -> runs a keyset-chunked export, writes a .sql file to disk, and reports
//         the filename to fetch over FTP. Nothing large is streamed over HTTP
//         (the host caps large HTTP downloads; FTP is the reliable path).
//
// Why keyset chunking: the host enforces max_statement_time (~4s). One long
// export read gets killed mid-stream and SILENTLY returns partial data. Small
// indexed range queries each finish under the cap, so the dump is complete.
//
// SECURITY: set $ACCESS_PASSWORD below before uploading. The generated .sql
// contains your whole database; it is written with a random filename so it
// can't be guessed and fetched over HTTP by a stranger. Delete it after you
// pull it over FTP (or use the "delete after" note).
// ===========================================================================

// ---- CONFIG: edit these ----
$DB_HOST = 'sqlXXX.infinityfree.com';
$DB_USER = 'if0_12345678;
$DB_PASS = 'your_db_password';
$DB_NAME = 'if0_12345678_dbname';

$ACCESS_PASSWORD = 'CHANGE_ME_before_uploading';  // gate for this tool
$OUTPUT_DIR      = __DIR__;                        // where .sql files are written
// ----------------------------

ini_set('display_errors', 1

$isPost = ($_SERVER['REQUEST_METHOD'] === 'POST');

if (!$isPost) {
    render_form();
    exit;
}

// ---------------- POST: run the export ----------------

header('Content-Type: text/plain; charset=utf-8');

// Auth
if (!hash_equals($ACCESS_PASSWORD, (string)($_POST['password'] ?? ''))) {
    http_response_code(403);
    echo "Access denied: wrong password.\n";
    exit;
}

// Settings from the form
$onlyTable = trim((string)($_POST['table'] ?? ''));
$onlyTable = $onlyTable === '' ? null : preg_replace('/[^A-Za-z0-9_]/', '', $onlyTable);
$chunk     = (int)($_POST['chunk'] ?? 500);
if ($chunk < 10)   $chunk = 10;
if ($chunk > 5000) $chunk = 5000;
$dropTables   = isset($_POST['drop']);         // include DROP TABLE statements
$includeData  = !isset($_POST['structure_only']);

$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if ($mysqli->connect_errno) {
    echo "Connect failed: " . $mysqli->connect_error . "\n";
    exit;
}
$mysqli->set_charset('utf8mb4');

// Random, unguessable filename so the dump can't be fetched over HTTP by URL guessing.
$rand     = bin2hex(random_bytes(8));
$baseName = 'backup_' . $DB_NAME . '_' . date('Ymd_His') . '_' . $rand . '.sql';
$outPath  = rtrim($OUTPUT_DIR, '/') . '/' . $baseName;

$fh = fopen($outPath, 'w');
if (!$fh) { echo "Cannot open output file for writing.\n"; exit; }

fwrite($fh, "-- Backup of `$DB_NAME`\n-- " . date('Y-m-d H:i:s') . "\n");
fwrite($fh, "SET FOREIGN_KEY_CHECKS=0;\n\n");

// Which tables
$tables = [];
if ($onlyTable !== null) {
    $tables[] = $onlyTable;
} else {
    $res = $mysqli->query("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'");
    while ($r = $res->fetch_row()) { $tables[] = $r[0]; }
    $res->free();
}

$grandTotal = 0;
$report = [];

foreach ($tables as $table) {
    $got = exportTable($mysqli, $fh, $table, $chunk, $dropTables, $includeData);
    $grandTotal += $got;

    if ($includeData) {
        $cRes = $mysqli->query("SELECT COUNT(*) c FROM `$table`");
        $exp  = $cRes ? (int)$cRes->fetch_assoc()['c'] : -1;
    } else {
        $exp = $got; // structure-only: nothing to compare
    }
    $report[$table] = [$got, $exp];
}

fwrite($fh, "\nSET FOREIGN_KEY_CHECKS=1;\n");
fwrite($fh, "\n-- Complete: $grandTotal rows across " . count($tables) . " tables\n");
fclose($fh);

// Report
$allMatch = true;
echo "Backup finished.\n";
echo str_repeat('=', 60) . "\n";
foreach ($report as $t => $p) {
    [$g, $e] = $p;
    $ok = ($g === $e);
    if (!$ok) $allMatch = false;
    printf("%-40s %8d / %-8d %s\n", $t, $g, $e, $ok ? 'OK' : '*** MISMATCH ***');
}
echo str_repeat('=', 60) . "\n";
echo "Total rows: $grandTotal\n";
echo "File size:  " . number_format(filesize($outPath)) . " bytes\n\n";

if ($allMatch) {
    echo "All row counts verified. Fetch this file over FTP:\n\n";
} else {
    echo "*** WARNING: some tables did not match COUNT(*). Inspect before trusting. ***\n\n";
}
echo "  " . $baseName . "\n\n";
echo "Location on server:\n  " . $outPath . "\n\n";
echo "Delete it from the server once you've downloaded it.\n";

$mysqli->close();
exit;

// ---------------------------------------------------------------------------

function exportTable(mysqli $mysqli, $fh, string $table, int $chunk, bool $drop, bool $includeData): int {
    $res = $mysqli->query("SHOW CREATE TABLE `$table`");
    if (!$res) { fwrite($fh, "-- ERROR reading structure of `$table`\n"); return 0; }
    $create = $res->fetch_assoc()['Create Table'];
    $res->free();

    fwrite($fh, "\n-- ----------------------------\n-- Table `$table`\n-- ----------------------------\n");
    if ($drop) fwrite($fh, "DROP TABLE IF EXISTS `$table`;\n");
    fwrite($fh, $create . ";\n\n");

    if (!$includeData) return 0;

    $pk = detectPagingKey($mysqli, $table);
    if ($pk === null) {
        return exportTableOffset($mysqli, $fh, $table, $chunk);
    }

    $lastId  = null;
    $total   = 0;
    $pkIndex = null;

    while (true) {
        if ($lastId === null) {
            $stmt = $mysqli->prepare("SELECT * FROM `$table` ORDER BY `$pk` ASC LIMIT ?");
            $stmt->bind_param('i', $chunk);
        } else {
            $stmt = $mysqli->prepare("SELECT * FROM `$table` WHERE `$pk` > ? ORDER BY `$pk` ASC LIMIT ?");
            $stmt->bind_param('si', $lastId, $chunk);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result->num_rows === 0) { $stmt->close(); break; }

        if ($pkIndex === null) {
            foreach ($result->fetch_fields() as $ci => $c) {
                if (strcasecmp($c->name, $pk) === 0) { $pkIndex = $ci; break; }
            }
            if ($pkIndex === null) { fwrite($fh, "-- ERROR: PK '$pk' not found\n"); $stmt->close(); return $total; }
        }

        while ($row = $result->fetch_row()) {
            fwrite($fh, buildInsert($mysqli, $table, $row));
            $lastId = $row[$pkIndex];
            $total++;
        }
        $stmt->close();
    }
    fwrite($fh, "\n");
    return $total;
}

function exportTableOffset(mysqli $mysqli, $fh, string $table, int $chunk): int {
    $offset = 0; $total = 0;
    while (true) {
        $stmt = $mysqli->prepare("SELECT * FROM `$table` LIMIT ?, ?");
        $stmt->bind_param('ii', $offset, $chunk);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result->num_rows === 0) { $stmt->close(); break; }
        while ($row = $result->fetch_row()) {
            fwrite($fh, buildInsert($mysqli, $table, $row));
            $total++;
        }
        $stmt->close();
        $offset += $chunk;
    }
    fwrite($fh, "\n");
    return $total;
}

function detectPagingKey(mysqli $mysqli, string $table): ?string {
    $res = $mysqli->query("SHOW KEYS FROM `$table` WHERE Key_name = 'PRIMARY'");
    $pkCols = [];
    while ($r = $res->fetch_assoc()) { $pkCols[] = $r['Column_name']; }
    $res->free();
    if (count($pkCols) === 1) return $pkCols[0];

    $res = $mysqli->query("SHOW KEYS FROM `$table` WHERE Non_unique = 0");
    $byKey = [];
    while ($r = $res->fetch_assoc()) { $byKey[$r['Key_name']][] = $r['Column_name']; }
    $res->free();
    foreach ($byKey as $cols) { if (count($cols) === 1) return $cols[0]; }
    return null;
}

function buildInsert(mysqli $mysqli, string $table, array $row): string {
    $vals = [];
    foreach ($row as $v) {
        $vals[] = $v === null ? 'NULL' : "'" . $mysqli->real_escape_string($v) . "'";
    }
    return "INSERT INTO `$table` VALUES (" . implode(',', $vals) . ");\n";
}

// ---------------------------------------------------------------------------

function render_form(): void {
    ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Database Backup</title>
<style>
  :root { color-scheme: light dark; }
  body {
    font-family: system-ui, -apple-system, Segoe UI, Roboto, sans-serif;
    max-width: 560px; margin: 40px auto; padding: 0 20px; line-height: 1.5;
  }
  h1 { font-size: 1.4rem; margin-bottom: 0.25rem; }
  p.sub { color: #666; margin-top: 0; font-size: 0.9rem; }
  label { display: block; margin: 18px 0 4px; font-weight: 600; font-size: 0.9rem; }
  input[type=text], input[type=password], input[type=number] {
    width: 100%; padding: 8px 10px; box-sizing: border-box; font-size: 1rem;
    border: 1px solid #999; border-radius: 6px;
  }
  .row { display: flex; align-items: center; gap: 8px; margin-top: 14px; }
  .row input[type=checkbox] { width: auto; }
  .row label { margin: 0; font-weight: normal; }
  .hint { color: #888; font-size: 0.8rem; margin-top: 3px; }
  button {
    margin-top: 24px; padding: 10px 18px; font-size: 1rem; font-weight: 600;
    border: 0; border-radius: 6px; background: #2563eb; color: #fff; cursor: pointer;
  }
  button:hover { background: #1d4ed8; }
  .note {
    margin-top: 24px; padding: 12px 14px; background: rgba(120,120,120,0.12);
    border-radius: 6px; font-size: 0.85rem;
  }
</style>
</head>
<body>
  <h1>Database Backup</h1>
  <p class="sub">Exports the database in small chunks (safe under the server time limit) and writes a .sql file to fetch over FTP.</p>

  <form method="POST" action="">
    <label for="password">Access password</label>
    <input type="password" id="password" name="password" required autocomplete="off">

    <label for="table">Single table (optional)</label>
    <input type="text" id="table" name="table" placeholder="leave blank to back up the whole database">
    <div class="hint">e.g. wp_posts &mdash; blank exports every table.</div>

    <label for="chunk">Rows per query</label>
    <input type="number" id="chunk" name="chunk" value="500" min="10" max="5000">
    <div class="hint">Lower this (e.g. 100) if a table has very large rows and times out.</div>

    <div class="row">
      <input type="checkbox" id="drop" name="drop" checked>
      <label for="drop">Include <code>DROP TABLE</code> statements</label>
    </div>
    <div class="row">
      <input type="checkbox" id="structure_only" name="structure_only">
      <label for="structure_only">Structure only (no data)</label>
    </div>

    <button type="submit">Start backup</button>
  </form>

  <div class="note">
    The backup runs on the server and may take a while for large databases. When it finishes,
    this page will show the generated filename and a per-table row-count check. Download that
    file over FTP, then delete it from the server.
  </div>
</body>
</html>
    <?php
}