Our blog

 

MySQL Dump Partial Restore – Split MySQL Dump into Tables

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

More Reading:

7 Comments

marius
May 10th, 2008

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
 

admin
May 10th, 2008

glad you liked it :-)

 

Max
June 13th, 2008

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

Joe
July 13th, 2009

Any way to just pull out one entire data base and not just all the tables?
 

mitchy
August 8th, 2011

Nice thank you!
Extended this to run on command line.
Optionally output verbosely.
Careful regarding file overwrites.

TODO
Restart reading dump file from last position left.
Stats and info from dump file.
Only extract specific tables or matching mask.
Extract databases
WYSIWYG style 'Find Start & End Delimiters'
Output shortcut commands for better dumping, importing, monitoring, mass cmds etc.
 

Izak
November 24th, 2011

Hmm... this doesn't work for me... I doubt it is supposed to look like this when i open it in browser: http://shrani.si/f/2x/4X/11GT8NYg/captured-photo.jpg
 

جامعة تشرين
February 9th, 2012

I didn't find php_ini :(

 

 

Leave a Reply