The other day, I was looking for an easy way to restore a MySQL dump (or backup, whatever you like to call it) in PHP. I've previously used a segment of the code from PHP MySQL Backup V 2.2 for this, but it didn't seem to support FULLTEXT indicies that well. So, I searched around, but couldn't find anything. I even asked on the PHP IRC channel, and they suggested to use shell_exec to call mysql (unfortunately, I've disabled shell_exec for security reasons). Looking closer, I noticed that this was actually quite easy to do. Here's the code I wrote to restore a phpMyAdmin MySQL dump (not sure if it works with mysqldump dumps):

< ?php
/*
 * Restore MySQL dump using PHP
 * (c) 2006 Daniel15
 * Last Update: 9th December 2006
 * Version: 0.2
 * Edited: Cleaned up the code a bit. 
 *
 * Please feel free to use any part of this, but please give me some credit :-)
 */
 
// Name of the file
$filename = 'test.sql';
// MySQL host
$mysql_host = 'localhost';
// MySQL username
$mysql_username = 'root';
// MySQL password
$mysql_password = '';
// Database name
$mysql_database = 'test';

//////////////////////////////////////////////////////////////////////////////////////////////

// Connect to MySQL server
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
// Select database
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());

// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line)
{
	// Skip it if it's a comment
	if (substr($line, 0, 2) == '--' || $line == '')
		continue;

	// Add this line to the current segment
	$templine .= $line;
	// If it has a semicolon at the end, it's the end of the query
	if (substr(trim($line), -1, 1) == ';')
	{
		// Perform the query
		mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
		// Reset temp variable to empty
		$templine = '';
	}
}

?>

See? How easy is that? 😄

Update: This also works for mysqldump dumps 😃

Short URL for sharing: https://d.sb/Bj. This entry was posted on 8th December 2006 and is filed under Linux, PHP. You can leave a comment if you'd like to, or subscribe to the RSS feed to keep up-to-date with all my latest blog posts!

Comments

  1. Avatar for furkan furkan said:

    thanks.. i was thinking 'why this thing saying there is an error', i was giving whole file to mysql_query with file_get_contents :)

  2. Avatar for steve steve said:

    Plain text? Not with line numbers it isn't.

    Nice script, but houw about a "download it here" link.

  3. Avatar for Daniel15 Daniel15 said:

    I don't really like the current syntax highlighing script I've got at the moment, so I've removed it (it's just plain text now).
    I'll try to remember to add a "Download it here" link...

  4. Avatar for Rahul Rahul said:

    superb code. This code helped me alot.
    keep giving these types of help

  5. Avatar for Cristiano Cristiano said:

    Really a great code, helped me a lot, but as the database is growing am having trouble restoring.
    The delay creates an error of time limit on the browser of 60 seconds and does not update.
    Now I am using set_time_limit and ignore_user_abort and is helping a bit.
    The script is the mysqldump with approximately 90mb.
    It improve it?

  6. Avatar for Daniel15 Daniel15 said:

    I'm not sure how to improve it for large dump files, as I've never had a MySQL dump that big. Perhaps you should consider using BigDump for it.

  7. Avatar for Sampaguita Sampaguita said:

    I'd suggest to take MySQL Dumper for big databases. I already used it for several large scale MySQL dumps (up to 1.8GB) and it works just fine. :)

  8. Avatar for Huy Huy said:

    THANK YOU THANK YOU THANK YOU VERY MUCH!!!!

    My project is due in next 1 hour and I was very struggling with writting an installer. Your piece of code is really helpful to me, saved my day!

    Thank you!!!!

  9. Avatar for Master Yoda Master Yoda said:

    Thanks Daniel. Making the same mistake, I was. using file_get_contents.
    A great help you are.
    May the force be with u.

  10. Avatar for Tt Tt said:

    really cool mannn

  11. Avatar for speedovation speedovation said:

    it's really nice code.
    Yea its easy when u look at code but take hours to develop something like this.I've wasted my whole day finding something good to restore database.

    Thank you so much.

  12. Avatar for Keith Freedman Keith Freedman said:

    Thanks.. saved a lot of effort.

  13. Avatar for zhibauz zhibauz said:

    Thank you so much

  14. Avatar for ozgun ozgun said:

    it's really nice and easy to understand.
    Thanks for tips and correcting my mistakes your source code really debuged me.
    And if you don't mind i want to ask a question about code why you are using $templine? And why you are concenating it with current line ?

  15. Avatar for Daniel15 Daniel15 said:

    SQL Queries can span multiple lines, the concatenation sticks it all together.

  16. Avatar for Andy Andy said:

    Hi Daniel,

    Nice script, I had a problem with DELIMITER in sqldump though, anyhow I did fix it and came up with a modified script to handle DELIMITER, here goes hope it can help others.
    (WARNING: somewhat tested code, but no guarantees, sorry)

    USAGE:
    well I like to define my constant variables i.e. DB_USER, DB_NAME, etc
    it's a function, just pass the file_name to this SQLRestore function

    define("CONNECT_STRING", "localhost:3306");
    define("DB_USER", "root");
    define("DB_NAME", 'my_db');
    define("DB_PASS", "");

    function SQLRestore($filename) {

    if(!$conn) $conn = mysql_connect (CONNECT_STRING, DB_USER, DB_PASS) or die();

    // Temporary variable, used to store current query
    $templine = '';
    $DELIMITER = ';';
    $D_LEN = 1;

    // Read in entire file
    $lines = file($filename);
    if(!$lines) return 1;

    // Loop through each line
    foreach ($lines as $line_num => $line) {

    $line = trim($line);

    if(substr($line, 0, 9) == 'DELIMITER') { // change delimiter
    $DELIMITER = str_replace('DELIMITER ', '', $line);
    $D_LEN = strlen($DELIMITER);
    continue;
    }

    // Only continue if it's not a comment
    if(substr($line, 0, 2) != '--' && $line != '') { // Add this line to the current segment
    $templine .= $line;

    // If it has a DELIMITER at the end, it's the end of the query
    if(substr($line, -$D_LEN, $D_LEN) == $DELIMITER) {
    mysql_query(rtrim($templine, $DELIMITER))
    or print('Error performing query \'' .
    $templine . '
    \': ' . mysql_error() . '');
    $templine = ''; // Reset temp variable to empty
    } else {
    $templine .= "\n";
    }
    }
    }
    }

    1. Avatar for yudi sulistiyo yudi sulistiyo said:

      hi andy i have try ur script there works perfectly for my case with triger and using delimiter... and daniel u re brilliant i found what i want here for long time searching but here i can found it and now problem soleved. thanks so much daniel and andy ...

  17. Avatar for Nandy Nandy said:

    Thanx dude....it helps me a lot.....and please give us updating code..

  18. Avatar for Soulitude Soulitude said:

    Pal I was looking desperately to find a solution to restore mysql using php without using mysqldump! You're the only one where I found what I was looking for! Thank you very much!

    1. Avatar for Daniel15 Daniel15 said:

      Glad I could help :)

  19. Avatar for m4t m4t said:

    So nice!

    Thank you very much, this code is awesome and saved my time... ;)

  20. Avatar for Tejas Mehta Tejas Mehta said:

    Hello Daniel15,

    I checked you blog and found that you have solution for restoring .sql file into database with php coding. but i am not able to findout that where you have placed link for downloading file. My problem is that i have to run one cron at a time which just import data into new table. if this is possible from you code than i will be thank full to you.

    please reply me soon on my id with link to download your code.

    Thank you and gr8 blog.

  21. Avatar for Tejas Mehta Tejas Mehta said:

    hey Daniel15,

    sorry for my last comment. actually my browser was not displaying your code. after i commented it redirected me and display proper code. your code is superb and it helped me. it has only one problem. it is not neglecting lines with /* text_text */ but that can be understandable. i will try to add it in my code.

    Thank you very much.

  22. Avatar for Melvin Melvin said:

    to come up with simple solution to solve a problem faced by so many people is simply awesome. Daniel15, thank you.

  23. Avatar for Stephen Ainsworth Stephen Ainsworth said:

    You have saved me alot of hard work.

    I've been trying to figure out this for a while now, and it seems so simple.

    Thank you so much!!

  24. Avatar for vivek vivek said:

    very good code for dump

  25. Avatar for samanta samanta said:

    Hi Daniel,

    Nice script you've got. But I had a problem with delimiter.
    I need to use delimiter for restoring triggers.
    Could you help me?

  26. Avatar for Davis Davis said:

    Buddy Thanks a Lot!!!
    The code was of great help :)

  27. Avatar for Omar Omar said:

    Hello
    Pretty Great Code , Helped me alot .

  28. Avatar for Thx Thx said:

    Thank for the script, really useful!

  29. Avatar for Imeda Imeda said:

    It's Sou easy, script works 100%
    Thenk you

  30. Avatar for breed breed said:

    thank you very much!
    i was struggling for a long time on this!

  31. Avatar for bashar bashar said:

    thanks. its really help me. can i expect some help for backup the database into one file?

    1. Avatar for Daniel15 Daniel15 said:

      Backing up is a bit harder. I'd suggest to use the "mysqldump" command via exec() or shell_exec() or system() or similar.

  32. Avatar for Kumi Kumi said:

    Daniel,

    Thanks for your hard work, your script is very helpful, but I would like to know one thing, I tried hard to implement your script in my CMS, I want users to be able to generate and restore back ups freely, I've implemented the back generator successfully, and also restore, I've added some pieces of code that will check if these tables exist and drop them, then create the new tables in the database but when I attempt to allow the users to browse the sql file from their computer it rejects, I've changed the filename variable into $filename = $_FILES[file]; nothing solved, when i use $_POST and browse the file in the same directory in my local server it works.

    Please help to fix this

    Thank you

  33. Avatar for Shuaib K.K Shuaib K.K said:

    Thanks Darling, Its very very use full for biginers

  34. Avatar for Jason Z Jason Z said:

    Thanks Daniel, Great script!

    1. Avatar for Daniel15 Daniel15 said:

      No worries :)

  35. Avatar for pablo pablo said:

    gracias, gracias, gracias! thank youuuuu, very useful to me.

  36. Avatar for Chetan Varshney Chetan Varshney said:

    thnx Daniel great job done !

  37. Avatar for a.Fujita a.Fujita said:

    Excellent!! It´s work fine!! Thanks a lot. All the people can use it!!!

  38. Avatar for Jawaad Jawaad said:

    Thanks you!!! I also struggle with the whole file to execute the SQL, LOL! this help me so much!

  39. Avatar for ahmed zain ahmed zain said:

    Thank you very much .. so it all was about this ';' ... :) thanks

  40. Avatar for Moji Moji said:

    Dear all,
    Thanks for useful script.
    i think this code will have some problems in a case, when we have a semicolon in the end of a text body, such as:

    INSERT INTO `tbl1` ( `id`, `title`, `body`) VALUES ( '5', 'test', 'this is a text with break line

    and line feed and so on;

    good luck;

    bye.
    ');

    I think we need a specific regular expression to solve this problem, please help to solve this problem.
    :-)

  41. Avatar for Waqar Waqar said:

    Hi Daniel,
    I am using you code to solve my import database problem. When i tried to browse database file from local drive and submit it then it gives bellow error:

    Warning: file(db-backup.sql) [function.file]: failed to open stream: No such file or directory in D:\xampp\htdocs\waqar\important_task\db_backup\import.php on line 29

    Warning: Invalid argument supplied for foreach() in D:\xampp\htdocs\waqar\important_task\db_backup\import.php on line 31

    And when i put my database file in the same directory and repeat the process to submit file then it will successfully import database.

    Can i fix this problem without putting the database file in the same directory? Please assist me to fix this problem.

    Regarding Waqar
    Wating for your response...........

  42. Avatar for Horst Noreick Horst Noreick said:

    Thanks, your script helped me to upload a joomla database. The access to phpmyadmin wasn't available at this time. :-)

  43. Avatar for dipak kumar burnwal dipak kumar burnwal said:

    thank's dude great work really helped me lot
    i ws searching for such script for a long time ,really great thank's a lot
    cheer's

    1. Avatar for Daniel15 Daniel15 said:

      No worries, you're welcome :)

  44. Avatar for Mats Lindh Mats Lindh said:

    Simple, but worked as advertised. Helpful when you suddenly are left without the MySQL console tools. Thanks!

  45. Avatar for patoun patoun said:

    Thank you very much !
    It does the job. Much appreciated

    P.

  46. Avatar for Viggie Viggie said:

    Many thanks. Saved time to install tables in db server without control panel access. You motivated me to give back to the community soon!

  47. Avatar for Neil Neil said:

    Thanks Dan, and great homepage. I always find it inspiring to see such young talent~ keep up the good work!

  48. Avatar for Thales Thales said:

    Thank you very much!!! Realy you saved my time... Can you send me a tip to use a file select input to select user the file instead of static string of $filename?

    thanks!!!

  49. Avatar for devinta devinta said:

    Please help me with this error

    Warning: file(test.sql) [function.file]: failed to open stream: No such file or directory in C:\wamp\www\New folder\backup.php on line 33

    Warning: Invalid argument supplied for foreach() in C:\wamp\www\New folder\backup.php on line 35

  50. Avatar for Javier Javier said:

    Very usefull! Thanks!

  51. Avatar for philip philip said:

    nice info..thanks man..

  52. Avatar for CGFIX CGFIX said:

    ROCK N ROLL MAN!

  53. Avatar for Pret Pret said:

    u rock man :)

    1. Avatar for Daniel15 Daniel15 said:

      Thanks! :D

  54. Avatar for Bulbul Bilayte Bulbul Bilayte said:

    thank you very much........................................................

  55. Avatar for sahil sahil said:

    thank you sir...

  56. Avatar for Terence Monteiro Terence Monteiro said:

    Thanks. This helped me add an installer to my application, since it doesn't depend on the setting the mysql path, it is easier to make it platform independent.

  57. Avatar for Skyfe Skyfe said:

    But what if a value contains the symbol ';'? Wouldn't that mess up the whole process?

    1. Avatar for Daniel15 Daniel15 said:

      Yeah, that'd mess it up. You'd need a proper lexer/parser to avoid that issue. You can just use the command-line `mysql` tool if you have command line access :)

  58. Avatar for Nyabuto Geofrey Nyabuto Geofrey said:

    Thanks Dan after weeks of tussles, finally av got the answer. using mysql to restore the back up doesnt work at all.

  59. Avatar for Vedran Brnjetić Vedran Brnjetić said:

    Thanks mate so much. Saved me from headaches. My current client doesn't want to change his hosting, and MyAdmin is broken on their outdated cpanel and I don't have access through telnet, but I managed to create a db and user and imported the database through website.

  60. Avatar for Ben Ben said:

    DEPRECATED -_-

    1. Avatar for Daniel15 Daniel15 said:

      Yes - This blog post is from 2006, things have changed since then :P