Updated MySQL Dump Splitter and Cleaner

This is post is now quite old and the the information it contains may be out of date or innacurate.

If you find any errors or have any suggestions to update the information please let us know or create a pull request on GitHub

My MySQL dump splitting script has come in handy countless times. Recently I also needed to remove keys from an SQL dump to enable me to import a dump that had keys associated with text columns.

Here is the updated script

*note - now updated again to strip out any drop table statements if splitting


<?php /*MySQL Dump Split to Tables * script supplied by Edmonds Commerce * www.edmondscommerce.co.uk * if you like it, please link back with a do-follow * * Now updated with better regex and also the ability to strip out keys other than the primary / echo ‘

MySQL Dump Split to Tables + Remove Keys

’; set_time_limit(600); if(!isset($_GET[‘dump_file’])){ echo ‘
Dump File Path:
’; }else{ if(preg_match(‘%keys|both%’, $_GET[‘operation’])){ $contents = file_get_contents($_GET[‘dump_file’]); $pattern = ‘%,(s+?)KEY(.
?))%si’; $nokeys = preg_replace($pattern, “, $contents);
$_GET[‘dump_file’] = ‘nokeys.sql’; file_put_contents(‘nokeys.sql’, $nokeys); } if(preg_match(‘%split|both%’,$_GET[‘operation’])){ echo ‘

Splitting File

’; flush(); $handle = fopen($_GET[‘dump_file’], “r”) or die(‘failed openeing source file ’ . $_GET[‘dump_file’]); if ($handle) { while (!feof($handle)) { $line = fgets($handle); if(preg_match(‘%^drop table(.+?);%i’, $line)){ continue; }
if(stristr($line, ‘CREATE TABLE’)){ echo ‘. ‘; if(isset($out)){ fclose($out); unset($out); } preg_match(‘%CREATE TABLE (|)(w+)(|)(s+)($%i’, $line, $matches); $table_name = $matches[2]; $out = fopen(‘output/’.$table_name . ‘.sql’, ‘w’) or die(‘failed to create file ’ . $table_name . ‘.sql’); fwrite($out, $line . “n”) or die(‘failed writing to file ’ . $table_name . ‘.sql’); }else{ if(isset($out)){ fwrite($out, $line . “n”); } }
} fclose($out); fclose($handle); }else{ echo ‘no handle on file - does it exist - permissions…’; } } } echo ‘


‘ ?>



Updated MySQL Dump Splitter and Cleaner | Edmonds Commerce Blog
Branedy » Blog Archive » Wordpress MySQL exercises
Python Script to Backup MySql Databases (Wordpress or other databases)
Importing Large MySQL Databases With BigDump
MySQL Upgrade: 1and1 is not the best host in the world | TheGarage

Tags: mysql dump split