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? :D
Update: This also works for mysqldump dumps :)
51 comments
Nice script, but houw about a "download it here" link.
I'll try to remember to add a "Download it here" link...
keep giving these types of help
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?
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!!!!
A great help you are.
May the force be with u.
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 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 ?
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 \'<b>' .
$templine . '</b>\': ' . mysql_error() . '');
$templine = ''; // Reset temp variable to empty
} else {
$templine .= "\n";
}
}
}
}
Thank you very much, this code is awesome and saved my time... ;)
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.
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.
I've been trying to figure out this for a while now, and it seems so simple.
Thank you so much!!
Nice script you've got. But I had a problem with delimiter.
I need to use delimiter for restoring triggers.
Could you help me?
The code was of great help :)
Pretty Great Code , Helped me alot .
Thenk you
i was struggling for a long time on this!
this will help you for backup database in on file
http://davidwalsh.name/backup-mysql-database-php
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 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.
:-)
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...........
i ws searching for such script for a long time ,really great thank's a lot
cheer's
It does the job. Much appreciated
P.