Updated MySQL Dump Splitter and Cleaner

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 …