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 
thanks.. i was thinking ‘why this thing saying there is an error’, i was giving whole file to mysql_query with file_get_contents
Plain text? Not with line numbers it isn’t.
Nice script, but houw about a “download it here” link.
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…
superb code. This code helped me alot.
keep giving these types of help
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?
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.
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.
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!!!!
Thanks Daniel. Making the same mistake, I was. using file_get_contents.
A great help you are.
May the force be with u.
really cool mannn
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.
Thanks.. saved a lot of effort.
Thank you so much
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 ?
SQL Queries can span multiple lines, the concatenation sticks it all together.
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”;
}
}
}
}
Thanx dude….it helps me a lot…..and please give us updating code..
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!
Glad I could help
So nice!
Thank you very much, this code is awesome and saved my time…
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.
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.
to come up with simple solution to solve a problem faced by so many people is simply awesome. Daniel15, thank you.
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!!
very good code for dump
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?
Buddy Thanks a Lot!!!
The code was of great help
Hello
Pretty Great Code , Helped me alot .
Thank for the script, really useful!
It’s Sou easy, script works 100%
Thenk you
thank you very much!
i was struggling for a long time on this!
thanks. its really help me. can i expect some help for backup the database into one file?
Backing up is a bit harder. I’d suggest to use the “mysqldump” command via exec() or shell_exec() or system() or similar.
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
Thanks Darling, Its very very use full for biginers
Thanks Daniel, Great script!
No worries