Extracting Specific Tables from Massive SQL Dumps using PHP
Have you ever needed to restore just one database or a specific set of tables from a multi-gigabyte (e.g., 5GB) MySQL dump? Opening a file of that size in standard text editors like VS Code or Notepad++ usually leads to extreme lag or a complete system crash.
Instead of struggling with desktop software or complex command-line tools like awk or sed, we can use a simple, memory-efficient PHP script to extract exactly what we need.
The Solution: Stream Processing
The trick to handling massive files in PHP is to avoid loading the entire file into memory at once (which functions like file_get_contents() do). By using PHP’s fgets() function, we can stream and process the file line by line.
The script below looks for the standard mysqldump comments that indicate the start of our target database or table (e.g., -- Current Database: db). When it finds our target, it toggles a flag to start writing those lines to a new file. As soon as it encounters the start of a different database or table, it turns the flag off.
Here is the complete script:
PHP
<?php
ini_set('memory_limit', '2G');
$inputFile = 'dump_5GB.sql';
$outputFile = 'db_fragment.sql';
$targetName = 'db';
$in = fopen($inputFile, 'r');
$out = fopen($outputFile, 'w');
$isExtracting = false;
while (($line = fgets($in)) !== false) {
if (strpos($line, '-- Current Database: `' . $targetName . '`') === 0 ||
strpos($line, 'USE `' . $targetName . '`;') === 0) {
$isExtracting = true;
} elseif (strpos($line, '-- Current Database: `') === 0 ||
strpos($line, 'USE `') === 0) {
$isExtracting = false;
}
if (!$isExtracting) {
if (strpos($line, '-- Table structure for table `' . $targetName . '`') === 0 ||
strpos($line, '-- Table structure for table `' . $targetName . '_') === 0) {
$isExtracting = true;
} elseif (strpos($line, '-- Table structure for table `') === 0) {
$isExtracting = false;
}
}
if ($isExtracting) {
fwrite($out, $line);
}
}
fclose($in);
fclose($out);
This method keeps memory usage practically flat—well under a few megabytes—and processes gigabytes of SQL data in just a few seconds.
