Restore MySQL dump (backup) using PHP

8th December 2006
Linux
Comments
https://d.sb/Bj

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 😃

Comments

4th January 2007 at 08:27 PM 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 :)
22nd April 2007 at 07:44 AM steve said:

Plain text? Not with line numbers it isn't.
Nice script, but houw about a "download it here" link.
22nd April 2007 at 09:17 AM 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...
13th November 2007 at 03:34 PM Rahul said:

superb code. This code helped me alot.
keep giving these types of help
14th November 2007 at 01:34 AM 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?
16th November 2007 at 02:15 PM 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.
5th February 2008 at 08:28 PM 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. :)
30th May 2008 at 10:25 AM 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!!!!
30th May 2008 at 04:37 PM 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.
30th July 2008 at 05:15 PM Tt said:

really cool mannn
22nd September 2008 at 06:59 PM 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.
13th October 2008 at 02:58 AM Keith Freedman said:

Thanks.. saved a lot of effort.
6th November 2008 at 06:55 PM zhibauz said:

Thank you so much
2nd December 2008 at 04:03 PM 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 ?
3rd December 2008 at 05:53 PM Daniel15 said:

SQL Queries can span multiple lines, the concatenation sticks it all together.
5th January 2009 at 01:32 PM 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. 18th October 2011 at 06:44 AM 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 ...
27th January 2009 at 11:59 AM Nandy said:

Thanx dude....it helps me a lot.....and please give us updating code..
27th February 2009 at 04:52 PM 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. 28th February 2009 at 05:35 PM Daniel15 said:
  
  Glad I could help :)
8th April 2009 at 12:12 PM m4t said:

So nice!
Thank you very much, this code is awesome and saved my time... ;)
14th April 2009 at 02:45 PM 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.
14th April 2009 at 03:19 PM 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.
9th July 2009 at 12:03 PM Melvin said:

to come up with simple solution to solve a problem faced by so many people is simply awesome. Daniel15, thank you.
22nd July 2009 at 04:24 PM 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!!
27th August 2009 at 04:24 PM vivek said:

very good code for dump
8th October 2009 at 12:53 PM 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?
15th October 2009 at 08:20 AM Davis said:

Buddy Thanks a Lot!!!
The code was of great help :)
25th October 2009 at 08:39 AM Omar said:

Hello
Pretty Great Code , Helped me alot .
27th October 2009 at 04:07 PM Thx said:

Thank for the script, really useful!
27th November 2009 at 03:21 PM Imeda said:

It's Sou easy, script works 100%
Thenk you
5th March 2010 at 12:01 AM breed said:

thank you very much!
i was struggling for a long time on this!
11th March 2010 at 04:53 PM bashar said:

thanks. its really help me. can i expect some help for backup the database into one file?
1. 14th March 2010 at 01:03 PM 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.
2. 29th September 2010 at 04:16 PM Chetan Varshney said:
  
  hi bashar
  this will help you for backup database in on file
  http://davidwalsh.name/back...
22nd March 2010 at 03:22 AM 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
24th March 2010 at 08:50 AM Shuaib K.K said:

Thanks Darling, Its very very use full for biginers
19th August 2010 at 07:02 AM Jason Z said:

Thanks Daniel, Great script!
1. 21st August 2010 at 01:35 PM Daniel15 said:
  
  No worries :)
14th September 2010 at 02:53 AM pablo said:

gracias, gracias, gracias! thank youuuuu, very useful to me.
29th September 2010 at 04:18 PM Chetan Varshney said:

thnx Daniel great job done !
31st October 2010 at 05:42 AM a.Fujita said:

Excellent!! It´s work fine!! Thanks a lot. All the people can use it!!!
2nd November 2010 at 07:19 PM Jawaad said:

Thanks you!!! I also struggle with the whole file to execute the SQL, LOL! this help me so much!
10th December 2010 at 04:20 PM ahmed zain said:

Thank you very much .. so it all was about this ';' ... :) thanks
16th December 2010 at 01:16 AM 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.
:-)
20th December 2010 at 10:16 PM 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...........
11th February 2011 at 08:56 PM Horst Noreick said:

Thanks, your script helped me to upload a joomla database. The access to phpmyadmin wasn't available at this time. :-)
10th June 2011 at 04:01 PM 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. 10th June 2011 at 04:02 PM Daniel15 said:
  
  No worries, you're welcome :)
27th September 2011 at 11:13 PM Mats Lindh said:

Simple, but worked as advertised. Helpful when you suddenly are left without the MySQL console tools. Thanks!
9th December 2011 at 11:24 AM patoun said:

Thank you very much !
It does the job. Much appreciated
P.
21st January 2012 at 06:45 PM 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!
10th March 2012 at 06:53 AM Neil said:

Thanks Dan, and great homepage. I always find it inspiring to see such young talent~ keep up the good work!
8th April 2012 at 01:16 AM 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!!!
8th May 2012 at 08:54 AM 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
25th June 2012 at 11:18 PM Javier said:

Very usefull! Thanks!
8th August 2012 at 10:36 AM philip said:

nice info..thanks man..
2nd October 2012 at 05:40 PM Sumith Harshan said:

Thanks bro.Also this article is describing Import a MySQL database by using database import class.It is very easy.
http://faq.pctrickers.com/i...
And This is helps to backup mysql database as sql file or gz format with full database or specific tables.
http://faq.pctrickers.com/c...
Thanks!
19th October 2012 at 03:54 PM CGFIX said:

ROCK N ROLL MAN!
25th October 2012 at 05:53 AM Pret said:

u rock man :)
1. 30th October 2012 at 02:51 AM Daniel15 said:
  
  Thanks! :D
9th November 2012 at 04:22 PM Bulbul Bilayte said:

thank you very much........................................................
4th December 2012 at 07:22 AM sahil said:

thank you sir...
15th October 2013 at 04:51 AM 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.
15th November 2013 at 07:04 PM Skyfe said:

But what if a value contains the symbol ';'? Wouldn't that mess up the whole process?
1. 30th November 2013 at 11:47 PM 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 :)
5th May 2014 at 05:15 AM 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.
27th August 2014 at 11:53 AM 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.
1st October 2014 at 03:41 AM Ben said:

DEPRECATED -_-
1. 1st October 2014 at 04:42 AM Daniel15 said:
  
  Yes - This blog post is from 2006, things have changed since then :P