Edmonds Commerce : Web Development, Design, SEO

MySQL Dump Partial Restore - Split MySQL Dump into Tables

April 9th, 2008
Read More mysql, php

MySQL dumps are often used as a quick and easy way of backing up an entire MySQL database in one go. However, they are only really designed to restore whole databases as well. So what if you only want to restore certain tables?

I have written this nice little PHP script which will take your dump file and split it into lots of little sql files - one for each table. You can then restore which ever tables you want.

Simply create a php file in the same directory as your dump file. Name it something easy to remember. Then go to it in your web browser, input the name of the dump file and click go.

As this is a fairly massive task (depending on the size of your dump file) you may find it works better on your local test server with some generous php_ini settings for max_execution_time etc.

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. echo '<h1>MySQL Dump Split to Tables</h1>';
  9. if(!isset($_GET['dump_file'])){
  10.     echo '<form><b>Dump File Path:</b> <input name="dump_file"> <input type="submit" value="go"></form>';
  11. }else{
  12.     $handle = @fopen($_GET['dump_file'], "r");
  13.     if ($handle) {
  14.         while (!feof($handle)) {
  15.             $line = fgets($handle);
  16.             if(strstr($line, 'CREATE TABLE')){
  17.                 if(isset($out)){
  18.                     fclose($out);
  19.                     unset($out);
  20.                 }
  21.                 preg_match('%CREATE TABLE `(.+?)`%', $line, $matches);
  22.                 $table_name = $matches[1];
  23.                 $out = fopen($table_name . '.sql', 'w');
  24.                 fwrite($out, $line . "\n");
  25.             }else{
  26.                 if(isset($out)){
  27.                     fwrite($out, $line . "\n");
  28.                 }
  29.             }   
  30.         }
  31.         fclose($out);
  32.         fclose($handle);
  33.     }   
  34. }
  35. echo '<h1>Done</h1>'
  36. ?>

Feed | Respond | Trackback

3 Responses to “MySQL Dump Partial Restore - Split MySQL Dump into Tables”

  1. marius Says:
    May 10th, 2008 at 8:50 am

    Excellent piece of coding. Had an error in the mysqldump that prevented to whole backup from being restored. With your script it was easy to do a partial restore.

    Thanks, Marius

  2. admin Says:
    May 10th, 2008 at 1:16 pm

    glad you liked it :-)

  3. Max Says:
    June 13th, 2008 at 2:19 pm

    Hey Edmond, thanks for the link. Adds nicely to my blog post on my way of doing partial restore.

Leave a Reply



Got any Edmonds Commerce related questions? Click Here...


0844 357 0201

Web Design : Edmonds Commerce






web design UK commercial web design