Our blog

 

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

PLAIN TEXT
PHP:
  1. <?php
  2. /*MySQL Dump Split to Tables
  3. * script supplied by Edmonds Commerce
  4. * www.edmondscommerce.co.uk
  5. * if you like it, please link back with a do-follow
  6. *
  7. * Now updated with better regex and also the ability to strip out keys other than the primary
  8. */
  9. echo '<h1>MySQL Dump Split to Tables + Remove Keys</h1>';
  11. if(!isset($_GET['dump_file'])){
  12.     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>';
  13. }else{
  14.     if(preg_match('%keys|both%', $_GET['operation'])){
  15.         $contents = file_get_contents($_GET['dump_file']);
  16.         $pattern = '%,(s+?)KEY(.*?))%si';
  17.         $nokeys = preg_replace($pattern, '', $contents);       
  18.         $_GET['dump_file'] = 'nokeys.sql';
  19.         file_put_contents('nokeys.sql', $nokeys);
  20.     }
  21.     if(preg_match('%split|both%',$_GET['operation'])){
  22.         echo '<h3>Splitting File</h3>';
  23.         flush();
  24.         $handle = fopen($_GET['dump_file'], "r") or die('failed openeing source file ' . $_GET['dump_file']);
  25.         if ($handle) {
  26.             while (!feof($handle)) {
  27.                 $line = fgets($handle); 
  28.                 if(preg_match('%^drop table(.+?);%i', $line)){
  29.                     continue;
  30.                 }               
  31.                 if(stristr($line, 'CREATE TABLE')){
  32.                     echo '. ';
  33.                     if(isset($out)){
  34.                         fclose($out);
  35.                         unset($out);
  36.                     }
  37.                     preg_match('%CREATE TABLE (`|)(w+)(`|)(s+)($%i', $line, $matches);
  38.                     $table_name = $matches[2];
  39.                     $out = fopen('output/'.$table_name . '.sql', 'w') or die('failed to create file ' . $table_name . '.sql');
  40.                     fwrite($out, $line . "n") or die('failed writing to file ' . $table_name . '.sql');
  41.                 }else{
  42.                     if(isset($out)){
  43.                         fwrite($out, $line . "n");
  44.                     }
  45.                 }      
  46.             }
  47.             fclose($out);
  48.             fclose($handle);
  49.         }else{
  50.             echo 'no handle on file - does it exist - permissions...';
  51.         }
  52.     }
  53. }
  54. echo '<h1>Done</h1>'
  55. ?>

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 ...

More Reading:

2 Comments

Math
October 21st, 2009

Hi,

Thanks, Very nice stuff. But I cant understand what does this regular expression does?
if(preg_match('%split|both%',$_GET['operation']))
 

admin
October 21st, 2009

it lets us do one or both operations by specifying with the operation or both as the $_GET['operation']
 

 

Leave a Reply