Skip to content

Instantly share code, notes, and snippets.

@Maxdw
Last active April 21, 2021 15:30
Show Gist options
  • Save Maxdw/7e2cf43182ff81530203a8c6704b8d08 to your computer and use it in GitHub Desktop.
Save Maxdw/7e2cf43182ff81530203a8c6704b8d08 to your computer and use it in GitHub Desktop.
<?php
// php mysql-dump-file-splitter.php bigdump.sql
// outputs dump files in ./out/
// php mysql-dump-file-splitter.php bigdump.sql dryrun
// does not output dump files but runs through the process
// options
define('OUTPUT_DIR', __DIR__ . DIRECTORY_SEPARATOR . 'out' . DIRECTORY_SEPARATOR);
$memlimit = '96'/*MB*/ * 1024 * 1024;
$skiptables = explode(',', 'users'); // skips users table
// preparation
$res = fopen(__DIR__ . DIRECTORY_SEPARATOR . $_SERVER['argv'][1], 'r');
$groupcount = -1;
$count = 1;
$groups = [];
$filecount = 1;
$tablenames = [];
$firstlines = [];
/**
* Outputs new SQL dump file
*/
function dumpgroups(&$groups, &$filecount, &$tablenames)
{
if (isset($_SERVER['argv'][2]) && $_SERVER['argv'][2] === 'dryrun') {
return;
}
foreach ($groups as $number => $group) {
if (!isset($tablenames[$number])) {
continue;
}
if ($group) {
$path = OUTPUT_DIR . $tablenames[$number] . '-' . $filecount++ . '.sql';
file_put_contents($path, $group);
echo 'writing ' . $path . PHP_EOL;
$groups[$number] = '';
}
}
}
// parses SQL
while (($line = fgets($res)) != false) {
$count++;
if (isset($_SERVER['argv'][2]) && $_SERVER['argv'][2] === 'dryrun' && strpos($line, 'Table structure for tabl') !== false) {
echo $line;
}
if (
preg_match('/^-- Table structure for table \`(.+?)\`$/i', trim($line), $matches, PREG_OFFSET_CAPTURE) === 1 ||
preg_match('/^-- Dump completed on/i', trim($line), $matches) === 1
) {
if (isset($tablenames[$groupcount])) {
dumpgroups($groups, $filecount, $tablenames);
}
if (!isset($matches[1][0])) {
break;
}
$tablename = $matches[1][0];
$tablenames[++$groupcount] = $tablename;
$firstlines[$groupcount] = $count;
if (!in_array($tablename, $skiptables)) {
echo 'reading table ' . $tablename . PHP_EOL;
}
}
if (!isset($groups[$groupcount])) {
$groups[$groupcount] = '';
}
if (isset($tablenames[$groupcount]) && in_array($tablenames[$groupcount], $skiptables)) {
if (isset($firstlines[$groupcount]) && $firstlines[$groupcount] === $count) {
echo 'skipped table ' . $tablenames[$groupcount] . PHP_EOL;
}
continue;
}
$groups[$groupcount] .= $line;
if (memory_get_usage(true) >= $memlimit) {
dumpgroups($groups, $filecount, $tablenames);
}
}
fclose($res);
echo 'END';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment