Extracting Individual Tables from a Large SQL Backup with PHP

A large MySQL dump may contain only one table that needs further work. Extracting relevant text does not require loading the entire file into an editor or PHP memory. The important distinction is the intended result: a text excerpt for inspection or a database backup that can actually be restored.

A line-based filter checks text lines. An SQL parser also recognises the structure of SQL statements, such as where a quoted value begins and ends. This filter does not perform that analysis. The PHP example below copies selected table sections identified by standard comments. Its output remains a text excerpt for review. A targeted fresh export or a validated restore workflow is preferable when recoverability is the goal.

Why one on/off flag is insufficient

The filter tracks two things separately: the current database and whether the current table should be copied. A database change ends the current selection. Every new table marker requires another selection decision. For example, after a selected orders section, an unselected logs section must turn copying off again. Checking table markers only while copying is disabled would continue copying unrelated tables after the first match.

fgets() alone does not guarantee tiny memory usage. Without a length limit, it reads a whole line, and an extended INSERT can be large. This example reads at most 65,536 bytes per step and examines markers only at the start of a line. Long data lines are copied in chunks.

Required dump format

• An uncompressed text file using the usual mysqldump comment format with database markers, such as an export made with --databases or --all-databases.
• The file contains -- Current Database: and the relevant -- Table structure for table and/or -- Dumping data for table markers. Dumps made with --skip-comments are not supported by this method.
• Exact database and table names, rather than one ambiguous shared prefix. Embedded backticks in identifiers and different generator formats are outside its scope.
• PHP 8 CLI and a local filesystem supporting hard links within one directory. A hard link gives the same file contents another name; here it creates the final name without replacing an existing file. Input, partial output, and final output belong outside publicly accessible web directories.

Compressed dumps first need decompression into a protected workspace with sufficient storage. Inspecting the file header helps identify the format, but does not establish the complete contents, every later table, or all line lengths.

PHP example: explicitly named table sections

php extract.php dump.sql excerpt.txt shop orders customers selects orders and customers from database shop. Neither the output file nor its .part file may already exist.

<?php
// PHP 8 CLI. Produces a text excerpt, not a validated restore file.
umask(0077);
if ($argc < 5) {
    fwrite(STDERR, "Usage: php extract.php input.sql excerpt.txt database table [table...]\n");
    exit(2);
}
[, $input, $output, $database] = $argv;
$tables = array_fill_keys(array_slice($argv, 4), true);
$part = $output . '.part';
$in = $out = false;
$created = false;

try {
    $in = @fopen($input, 'rb');
    if ($in === false) throw new RuntimeException('Cannot open input');
    $out = @fopen($part, 'xb');
    if ($out === false) throw new RuntimeException('Cannot create new partial output');
    $created = true;
    $currentDatabase = null;
    $copy = false;
    $lineStart = true;
    $seen = [];

    while (($chunk = fgets($in, 65537)) !== false) {
        if ($lineStart) {
            if (preg_match('/^-- Current Database: `([^`]+)`\r?\n?$/D', $chunk, $m)) {
                $currentDatabase = $m[1];
                $copy = false;
            } elseif (preg_match('/^-- (?:Table structure|Dumping data) for table `([^`]+)`\r?\n?$/D', $chunk, $m)) {
                $copy = $currentDatabase === $database && isset($tables[$m[1]]);
                if ($copy) $seen[$m[1]] = true;
            } elseif (str_starts_with($chunk, '-- ')) {
                $copy = false;
            }
        }
        if ($copy) {
            $offset = 0;
            $length = strlen($chunk);
            while ($offset < $length) {
                $n = fwrite($out, substr($chunk, $offset));
                if ($n === false || $n === 0) throw new RuntimeException('Write failed');
                $offset += $n;
            }
        }
        $lineStart = str_ends_with($chunk, "\n");
    }
    if (!feof($in)) throw new RuntimeException('Read failed');
    if (array_diff_key($tables, $seen)) {
        throw new RuntimeException('Requested table marker missing; format or names differ');
    }
    if (!fflush($out)) throw new RuntimeException('Flush failed');
    if (!fclose($out)) throw new RuntimeException('Close failed');
    $out = false;
    // Same-directory hard link: fails if the final name already exists.
    if (!@link($part, $output)) throw new RuntimeException('Cannot publish new output');
    if (!unlink($part)) throw new RuntimeException('Output exists, partial link cleanup failed');
    $created = false;
    fwrite(STDERR, "Text excerpt written. Separate restore validation required.\n");
} catch (Throwable $e) {
    if (is_resource($out)) fclose($out);
    if ($created && is_file($part)) unlink($part);
    fwrite(STDERR, $e->getMessage() . "\n");
    exit(1);
} finally {
    if (is_resource($in)) fclose($in);
}

The final output appears only after the input has been read, writes have succeeded, and at least one matching marker has been found for each requested table. A marker proves section selection, not complete SQL objects. An existing result is not overwritten. Errors produce a nonzero exit status rather than reporting a partial excerpt as a finished result.

From an excerpt to a restore

Session settings such as character set and SQL mode, foreign-key dependencies, triggers, views, and routines may sit outside the copied sections. For example, an orders table may refer to customer IDs in a separate customers table. Copying the orders section alone does not include those customer records. Unusually formatted SQL strings can also contain comment-like text. This example does not interpret those SQL structures and is not a universal import filter.

When the original server remains available, mysqldump accepts a database followed by explicitly selected table names. For InnoDB, --single-transaction can provide a consistent backup; other storage engines and concurrent schema changes require an appropriate strategy. When only a dump remains, an isolated restore followed by targeted object export offers a more controlled route. Target version, storage, dependencies, and permissions need checking before import.

Sources and review date

2026-10-24

• PHP: fgets
• PHP: fwrite
• MySQL 8.4: mysqldump