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 '<h1>MySQL Dump Split to Tables + Remove Keys</h1>'; set_time_limit(600); if(!isset($_GET['dump_file'])){ echo '<form><b>Dump File Path:</b> <input name="dump_file"> <input type="submit" value="go"><select name="operation"><option value="split">split by tables</option><option value="keys">remove keys other than primary</option><option value="both">both</option></select></form>'; }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 '<h3>Splitting File</h3>'; 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 (<code>|)(w+)(</code>|)(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 '<h1>Done</h1>' ?>
More…
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 …

Hi,
Thanks, Very nice stuff. But I cant understand what does this regular expression does?
if(preg_match(‘%split|both%’,$_GET['operation']))
it lets us do one or both operations by specifying with the operation or both as the $_GET['operation']