{"id":122,"date":"2026-07-17T12:45:43","date_gmt":"2026-07-17T10:45:43","guid":{"rendered":"https:\/\/www.lukaswojcik.com\/blog\/?p=122"},"modified":"2026-09-10T10:54:22","modified_gmt":"2026-09-10T08:54:22","slug":"extracting-specific-tables-from-massive-sql-dumps-using-php","status":"publish","type":"post","link":"https:\/\/www.lukaswojcik.com\/blog\/en\/it-networks\/extracting-specific-tables-from-massive-sql-dumps-using-php\/","title":{"rendered":"Extracting Specific Tables from Massive SQL Dumps using PHP"},"content":{"rendered":"\n

Sometimes only one database or a specific set of tables needs to be restored 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.<\/p>\n\n\n\n

Instead of struggling with desktop software or complex command-line tools like awk<\/code> or sed<\/code>, we can use a simple, memory-efficient PHP script to extract exactly what we need.<\/p>\r\n\r\n\r\n\r\n

\n\"Seven\n
The database marker sets the switch, the table marker corrects it \u2014 everything in between is copied unchanged.<\/figcaption>\n<\/figure>\r\n\n\n\n

The Solution: Stream Processing<\/strong><\/p>\n\n\n\n

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()<\/code> do). By using PHP’s fgets()<\/code> function, we can stream and process the file line by line.<\/p>\n\n\n\n

The script below looks for the standard mysqldump<\/code> comments that indicate the start of our target database or table (e.g., -- Current Database: <\/code>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.<\/p>\n\n\n\n

Here is the complete script:<\/p>\n\n\n\n

<?php\nini_set('memory_limit', '2G');\n\n$inputFile = 'dump_5GB.sql';\n$outputFile = 'db_fragment.sql';\n$targetName = 'db';\n\n$in = fopen($inputFile, 'r');\n$out = fopen($outputFile, 'w');\n\n$isExtracting = false;\n\nwhile (($line = fgets($in)) !== false) {\n    if (strpos($line, '-- Current Database: `' . $targetName . '`') === 0 || \n        strpos($line, 'USE `' . $targetName . '`;') === 0) {\n        $isExtracting = true;\n    } elseif (strpos($line, '-- Current Database: `') === 0 || \n              strpos($line, 'USE `') === 0) {\n        $isExtracting = false;\n    }\n\n    if (!$isExtracting) {\n        if (strpos($line, '-- Table structure for table `' . $targetName . '`') === 0 ||\n            strpos($line, '-- Table structure for table `' . $targetName . '_') === 0) {\n            $isExtracting = true;\n        } elseif (strpos($line, '-- Table structure for table `') === 0) {\n            $isExtracting = false;\n        }\n    }\n\n    if ($isExtracting) {\n        fwrite($out, $line);\n    }\n}\n\nfclose($in);\nfclose($out);\n<\/code><\/pre>\n\n\n\n
This method keeps memory usage practically flat\u2014well under a few megabytes\u2014and processes gigabytes of SQL data in just a few seconds.<\/p>\n\n\n
\n
Sources<\/h2>\n