Backup Your MySQL Database Using PHP
One of the most important tasks any developer needs to do often is back up their MySQL database. In many cases, the database is what drives most of the site. While most web hosts do a daily backup of a customer's database, relying on them to make backups and provide them at no cost is risky to say the least. That's why I've created a database backup function that I can call whenever I want -- including nightly CRONs.
The PHP & MySQL Code
backup_tables('localhost','username','password','blog');
/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
//get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
//cycle through
foreach($tables as $table)
{
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);
$return.= 'DROP TABLE '.$table.';';
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
$return.= "\n\n".$row2[1].";\n\n";
for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysql_fetch_row($result))
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j < $num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace("\n","\\n",$row[$j]);
if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
if ($j < ($num_fields-1)) { $return.= ','; }
}
$return.= ");\n";
}
}
$return.="\n\n\n";
}
//save file
$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}
Of course, you'll need to provide database credentials to the function, as well as an array of tables you'd like to backup. If you provide a "*" or no tables, a complete database backup will run. The script does the rest!
Never take chances when your website is on the line. Make frequent backups or pay the price later!
This is something I’ve been meaning to do for a while now. Dreamhost does do regular backups they claim (have never had to use them), but I should really do this anyway.
Thanks, very informative.
David, would you suggest doing this manually, or maybe setting up a cron to do this on a weekly basis. or maybe having mootools or jquery (hint hint)…to set up a timer to do this…that way it just overrides the last one and you always have a weekly backup of your Db at hand…just in case. any thoughts?
ok, noob checking in here. yes, i’m one of those “hmmm, what does that do…” sort of self-taught posters. most of the time i learn enough get into trouble. ;)
anyway, from what i can ascertain from parsing your code, this should be a very sweet addition to the must-haves.
but, as a noob, i’m not sure i grok where the file is being saved “to”
i notice the save comment at the end, but cannot decipher where it’s actually saved.
a little insight please?
thanks! and thanks again for your blog.
Hi Micheal, It looks like he is saving the output of the file to whichever directory this php code is located at. I might be wrong but the file name is timestamped and given a diffcult to guess name so nobody will stumble upon it and get access to his entire database structure-which would be not good!. If I were to do the same I would be writing this file to an out of server root location.
@Rich: I wouldn’t trust jQuery or Moo to do this — this is definitely a server-side responsibility. Maybe a nightly or weekly CRON would be best. My blog emails me a DB backup weekely.
@Michael: I’m writing a backup to a file called “db-backup-{current time}-{tables md5}.sql” file. That way it’s (theoretically) easy to tell when the backup was last run.
Kind of figured that. Awesome idea though. I usually do it manually very month. this will make life so much easier. Thanks again David!
He David,
What happened with the good old mysqldump command? Even gzipped for saving bandwidth or disk space.
And in PHP code:
Or with specific tables:
Saves you a line or two ;)
Cheers!
Don’t you have to add a second PHP line like system($output); ?
The backticks actually execute the command:
http://php.net/manual/en/language.operators.execution.php
what about this?
Warning: shell_exec() has been disabled for security reasons
Enable shell_exec ?
What if I cant, because hosting is not mine?
if the User have no Server SSH access i think this article for that…
That wont re-create all your indexes n stuff tho will it?
mysqldump is the tool of choice.
Nice article.
Every webmaster should really set up a Cron like that to backup their database !!
I made up a similar script for my websites.
The solution of sending the sql by mail is not a good solution if your site become larger and larger. Or you need to save only recent entries and not all tables. If you have a ftp for storing the backup file, you could copy the file once a week on the ftp.
Mysqldump is not accessible on all hosting services so this script is the solution.
I know mysqldump is not available on every shared hosting. But I choose a more expensive hostng package or VPS when it is not available.
This is IMHO not a security issue, so why should it not be available…
@Martin, PrimeMinister: I chose this route because as the two posts after you mentioned, many hosting providers don’t allow the mysqldump functionality.
bug?
for ($i = 0; $i < $num_fields; $i++) { $return.= 'DROP TABLE '.$table.'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n";loops through and creates multiple Drop Table and Create table mysql statements
me thinks this part:
$return.= 'DROP TABLE '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n";should be outside the
forloopI agree with you primeminister. A hosting provider should make mysqldump available some way or another.
ereg() is slower than preg_match, and is being depreciated in php6.
I would recommend exchanging the ereg() function with preg_match().
Other than that, great script! (as usual)
@ gerry22:
Yup
$return.= 'DROP TABLE '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; Should be above: $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); for ($i = 0; $i < $num_fields; $i++) {@gerry22: Good call. I’ll fix the article as soon as I can.
am i the only person who groaned when I saw the DROP command being used.
seriously, I havent looked closely through the code, but is it dropped a duplicated table or what is the DROP doing?!
It’s there in case you need to restore the table, must like PHPMyAdmin. What’s with the groand?
ok, sorry. I didnt read the code all the way through. Its writing it out rather than actually DROPPING the table in the backup processes. Thats what I get for commenting before I read the code…. :(
Hello David,
I found this script very useful, but i have problems with blob data, the script wont get this data correctly.
Thanks
Nice looking code, i have not tested it yet but i plan on doing so, im looking to modify the code a little to suit my needs (implement it into my members control panel) but il be sure to come back to you with the new work so you can repost it here :)
yeah yeah im one of those hosts that doesnt permit sqldump lol, infact i dont use the main stream control panels for my hosting at all.
Hi Brian, did you find a way to fix the blob data and by using the above script, a file is created but empty.
Your post is a very good. I’ll download and try.
One little modification I made:
Added the IF EXISTS !!
Great script!!!, a live saver!!!
I did exactly the same thing as yourself. I’m also in agreement, this script is very well written and just saved me a tonne of time writing my own!
Many thanks!
Now we just need a restoring script :)
Hi, I think what Michael was trying to say was…where is the actual file itself being saved to…is it the same directory as the the script, do I have to define it…or is there a pre-defined saving camp?
Ta Much.
@Carl: The same directory as the script.
@Carl, & @David: thanks!
Gotcha, Thanks – I’m uploading now ;)
superb article, just what i had spent days looking for.
As someone has previously posted though, how do we restore the database once it has ben backed up?
A straight to the point script, just what i was looking for many thanks.
One quick question, what is the purpose of (md5(implode(‘,’,$tables))) ? ?
@Kelvin: It takes a string of the desired tables and md5’s them for the file name so that files aren’t constantly being overwritten.
Would you mind sharing the script you use to email the file weekly?
If you’re getting an “Out of range value adjusted for column error” when trying to restore your backup, edit lines:
if (isset($row[$j])) { $return.= ‘”‘.$row[$j].'”‘ ; } else { $return.= ‘””‘; }
with this:
if (isset($row[$j])) {
if ($row[$j] == null) {
$return .= ‘null’ ;
} else {
$return .= ‘”‘.$row[$j].'”‘ ;
}
}
else {
$return .= ‘””‘;
}
what is the solution for ” how to transfer the file from one ftp server to the another ftp server directly plz explainning in details if possible for anyone”
nice, just what I was after, Thanks David
@Micheal Fox – I’ve bolted on some script to send the sql, that I found here http://www.sitepoint.com/article/advanced-email-php/
a little modified, only real difference is MIME type
application/x-sql.just add the function below, and call it here in the original script
print("fwrite($handle,$return);sendBackup($filename, $name); fclose($handle);");` print("<?php function sendBackup($newFileName, $name){ // Read POST request params into global vars $to = 'me@example.com; $from = 'me@example.com; $subject = 'Your Backup for '.$name; $message = 'Backup for '.$name; // Obtain file upload vars $fileatt = $newFileName; $fileatt_type = "application/x-sql"; $fileatt_name = $newFileName; $headers = "From: $from"; // Read the file to be attached ('rb' = read binary) $file = fopen($fileatt,'rb'); $data = fread($file,filesize($fileatt)); fclose($file); // Generate a boundary string $semi_rand = md5(time()); $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x"; // Add the headers for a file attachment $headers .= "\nMIME-Version: 1.0\n" . "Content-Type: multipart/mixed;\n" . " boundary=\"{$mime_boundary}\""; // Add a multipart boundary above the plain message $message = "This is a multi-part message in MIME format.\n\n" . "--{$mime_boundary}\n" . "Content-Type: text/plain; charset=\"iso-8859-1\"\n" . "Content-Transfer-Encoding: 7bit\n\n" . $message . "\n\n"; // Base64 encode the file data $data = chunk_split(base64_encode($data)); // Add file attachment to the message $message .= "--{$mime_boundary}\n" . "Content-Type: {$fileatt_type};\n" . " name=\"{$fileatt_name}\"\n" . //"Content-Disposition: attachment;\n" . //" filename=\"{$fileatt_name}\"\n" . "Content-Transfer-Encoding: base64\n\n" . $data . "\n\n" . "--{$mime_boundary}--\n"; // Send the message $ok = @mail($to, $subject, $message, $headers); if ($ok) { echo "Mail sent! "; } else { echo "Mail could not be sent. Sorry!"; } } ?>");Add this code to the end to save on your local drive:
$filename = 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql'; Header("Content-type: application/octet-stream"); Header("Content-Disposition: attachment; filename=$filename"); echo $return;Exporting in xml will create a huge file… am I wrong?
Ahmad Alfy: It definitely would.
i am very happy to find this code .it relay work fine and very few time to take a backup the database
thanks
This script saved my bacon! i had tried a few others but kept getting errors. Had a few errors on this one, but it was with me, not the script. Only thing i need to figure out is how to get it save to a specific folder, but i think i need to use the physical path instead of the relative one.
Thanks for the script!
It is a great script, but what about VIEWS ?
The script recognize a VIEW as a TABLE and I get this:
“DROP TABLE vw_customerorders;
CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW vw_customerorders AS select unl_login.FirstName…….”
“INSERT INTO vw_customerorders VALUES(“…………..”)
For this issue what can be done ?
Thank you for the script and the informations i usually get from your Blog.
This is old question, but might help someone. If you change your SHOW TABLES sql command to: $result = mysql_query(‘SHOW FULL TABLES WHERE Table_type = \’BASE TABLE\”); you will only get tables and not VIEWS.
But how I can use the script for the VIEWs, is it possible?
Thank you
Great script!
Just checked that NULL values are grabbed as “0”… perhaps It would be fixed as per below:
if (isset($row[$j]) && ($row[$j] != NULL)) { $return.= ‘”‘.$row[$j].'”‘ ; } else { $return.= ‘NULL’; }
Again… thank you for your script
I love yor filename pattern.. Thanks, scripts work and make me Understand :)
Thank you very much. I was just googling about this stuff and found this. Not tested yet, but after reading the comments I know it will surely work!!
Thanx and keep up the good work.
This is a really cool script you’ve got Dave, thanks! I’ll be pointing a link at you as soon as I can get it working on my client’s server. It’s working like a dream on my test site, but the DB on the client site is larger and the script keeps timing out 3 tables into the process (I have 16 total). If you have a work around, I’d love to hear it. Again, thanks for the jumpstart
Thanks for sharing that. I’ll give it a try. It does look like a life saver.
And do you have a similar script to run with a cron job to backup the web public directory in a .tar or .tgz file, prefix or suffix with the date?
that would a great backup combo script !!!! and another interesting article.
cheers and thanks
Thanks, saved a load of time and taught a thing or two.
For those worried about having too many backup files stored, add a simple table on your database to store the details of your backups and use this info to purge unwanted files – for example – run the below from your sql window to create the table.
Where David recommends saving using the code:
//save file $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle);If you instead use
$now=time(); //save file - this is using a different format to the one above to help keep the filename samaller in size (personal preference) $handle = fopen('db-backup-'.$now.'.sql','w+'); fwrite($handle,$return); fclose($handle); $sql="INSERT INTO backup (name) VALUES ('db-backup-".$now.".sql')";if (!mysql_query($sql)) //file saved to the database you created above { die('Error: ' . mysql_error()); } //change the LIMIT 14 to be the number of backups you want to keep. For example I run this once a day so saving the 14 latest backups provides a 2 week window. $query="SELECT * FROM backup ORDER BY id DESC LIMIT 14,100"; $result = mysql_query($query); while($row = mysql_fetch_array($result, MYSQL_ASSOC)){ //removes unwanted backup files and then removes their details from the database $name=$row['name']; unlink ($name); $sql="DELETE FROM backup WHERE name='$name'"; if (!mysql_query($sql)) { die('Error: ' . mysql_error()); } };And using this will allow you to only keep a limited amount of backups, with the oldest being deleted as a new one is created. I hope this helps keep your backup-footprint down :)
Great White
Thanks for this script! Worked like a charm.
I would like to ask a question. Could you show us on how to then make something which the user can click on the button to download the file create?
Thanks a lot. Please contact me here or on my email. Thanks.
Nice work,
I tried it instantly and it did back up my DB. But I saw problem with a character encoding. It lost all the character informations, all I can see is lots and lots of ?????????????? marks :-(. I tried with phpmyadmin dump, it don’t show this problem. My sites language is Tibetan and German ( with german chars. no problems ). Any idea or suggestion for fix.
Thanks a lot
Tenzin
Same Problem…
did u found how to fix it?
you just need to set the charset to utf8,
here is how I solved it,
$conn = mysqli_connect($host,$user,$pass,$db); if (!$conn->set_charset("utf8")) { printf("Error loading character set utf8: %s\n", $conn->error); } else { }Not so bad. But I tried to save all db tables with *.
And theses tables without records, were dropped and after that not created.
bug?
I tried a script that ran mysqldump through the PHP system() and it did nothing but create a blank file. This one works like a bloody charm and is better than relying on using a system command, which can vary if moved to a different server. Though I chopped all the login stuff out since each page is always logged into the database that needs backing up
THANKS!
It doesn’t seem to be exhaustiv but Thanks!
What is the goal of the first loop on line 36?
On line 41 you have again the same loop:
Is it necessary to loop two time through the table fields? May be there is something I don’t understand. Thank you.
I have been using this script and hit a glitch when I tried restoring a hacked Joomla site tonight. I noticed some of my articles were getting cut off occasionally when a single or double quote was used in the article. I looked at the backup created by this script and noticed some of the quotes in the SQL backup didn’t get escaped with a “\”, so now I have to manually go through the backup and add them where they were missed so the restore goes through properly. Has anyone run into this problem? I really love this script but now I’m worried about running into a similar issue on a more robust site which would be a nightmare to manually go through and add slashes that were skipped!
Hi I am facing this error please help me
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 128921702 bytes) in /home/webmaste/public_html/dewshare.com/forums/backup.php on line 46
first of all thanks david for these awsome tutorials,you helped a lot in understanding mootools ;).
Right now i’m doing kind of a toolbox where i collect all kinds of useful classes or write them myself,in the last days i did a login with php/mysqli, because i wanted to use the mysqli-prepared-statement functionality.
as i browsed through your tuts today i found the ‘create-a-zip’ and this tut,so i went on and combined these with my mysqli_wrapper_class ( i had to rewrite your backup-script as well :> ).
Right now i’m searching for a way to make an backup-file for every table in the selected database,just a little more fixing.
greetz from germany (yeah,we can code too)
i must commend your effort in displaying thsi code.this i have been looking for for my many weeks now.God bless you.thanks so much.
Actualy there’s a little mistake in line 41. Where it says:
It should say
I hope this helps everybody and specially @Lorand.
For those how use mysqli here’s my modified version:
foreach($tables as $table){ /* Drop previous tables */ $return.= 'DROP TABLE IF EXISTS '.$table.';'; /* Get 'how to' create the table */ $result = $this->link->query('SHOW CREATE TABLE '.$table); $row = $result->fetch_array(MYSQL_NUM); $return.= "\n\n".$row[1].";\n\n"; $result = $this->link->query('SELECT * FROM '.$table); $num_fields = $result->num_rows; for ($i = 0; $i fetch_array(MYSQL_NUM)) { $columns = count($row); $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$columns; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = preg_replace("/\n/","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n\n\n"; }Wow!! it worked like magic. Could you please give a tutorial for exporting tables in a database to excel? will be glad if you would.
thanks
Add these lines at the line 4 and it will can backup UTF-8 databases:
mysql_query('SET NAMES utf8'); mysql_query('SET CHARACTER SET utf8');Byez!
great script… will try it out
Thank you ! It works perfect and i spare time…
thanks, myself and a mate just used this script. very handy. you’ve saved us a lot of effort :)
David,
Nice script! I added a little something I’d like to share. When naming the file, I put the date and table as the name.
//save file if ($tables = '*') { $table="all"; } // if your not backing up all(*) the tables, it appends that table name to the end of the file. $handle = fopen('db-backup-'. date("Ymd") .'-'. $name .'-'. $table. ' .sql','w+');Gives you something like: db-backup-20091216-mydatabase-all.sql
Hope that helps someone, thanks again David
beautiful.. thanks
great script.. can some one give me a script for restoring the DB, i tried a script but only reads the first 3000 lines, my DB its big (141200 lines) ..please i need some help
thanks
Amazing!
My former (free) host had connections issues for phpMyAdmin, so I wasn’t able to export my database. Thanks to you now I saved it!
Thank you for this amazing script.
Aw I just forgot to add that you should modify a line in the script :
$return.= ‘DROP TABLE ‘.$table.’;’;
should be :
$return.= ‘DROP TABLE IF EXISTS ‘.$table.’;’;
in order to avoid any “inexistant talbe” warnings.
Cheers,
The script is not fetching value…. in insert query.
First off nice script give me a starting point for a good backup system.
There should be a
$return = '' ;just aftermysql_select_db($name,$link);this way you don’t get a notice of undefined returnI also did a little mod to you system and added to My $db class
and added a choice of rather to do just the structure or just the data
function backup_tables($host='',$user='',$pass='',$name='',$tables = '*',$struc=false,$data=false) { global $db; $return = ''; //get all of the tables if($tables == '*') { $tables = array(); $result = $db->sql_query('SHOW TABLES'); while($row = $db->sql_fetchrow($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = $db->sql_query('SELECT * FROM '.$table); $num_fields = $db->sql_numfields($result); if($struc)$return.= 'DROP TABLE '.$table.';'; $row2 = $db->sql_fetchrow($db->sql_query('SHOW CREATE TABLE '.$table)); if($struc)$return.= "\n\n".$row2[1].";\n\n"; if($data){ for ($i = 0; $i sql_fetchrow($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = ereg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } } $return.="\n\n\n"; } //save file $handle = fopen('./backups/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); } backup_tables($host,$user,$pass,$db,$tables = '*',false,true); returns only data backup_tables($host,$user,$pass,$db,$tables = '*',true,false); returns only structure backup_tables($host,$user,$pass,$db,$tables = '*',true,true); returns allGreat script! I have to add the tables for it to run though. Leaving
*results in the ‘mysql_num_fields(): supplied argument is not a valid MySQL’ error. Is this because of my host?Also, I’ve just compared a out from this script to one from phpmyadmin.
Text content that had a line break included is being output incorrectly and actually creating a line break in the .sql.
” Cras pulvinar\r\ndui in urna sagittis”
” Cras pulvinar
\ndui in urna sagittis”
Any ideas why this might be?
guys i have problem with utf-8 , i dont know where to add
mysql_query('SET NAMES utf8'); mysql_query('SET CHARACTER SET utf8');someone says in line 4 but i test that and still my records are in ? marks
plz tell exact place of code
Your text editor is UFT-8 compilant?
thanx guys problem was in importing db file in SSH
but still i can not compress the sql file
i wonder how i can create gz file to save bandwidth
Simple as:
gzip yourfile.sql
What would be the solution to let’s say delete 3 days old backups, as they are in this form:
hostname.com-db-backup-‘.date(‘Y-m-d–H-i’).zip
@Lorand: I don’t understand it either. The $i loop seems useless to me, rows are only fetched on the first iteration, the rest do nothing . Am I missing something?
But thanks for the script btw. :)
Another useful thing in the script would be to add:
$return = “SET FOREIGN_KEY_CHECKS=0;\n\n”;
on line 25, and
$return .= “SET FOREIGN_KEY_CHECKS=1;\n”;
on line 53.
So the script manages to update the database, without causing the #1217 – “Cannot delete or update a parent row: a foreign key constraint fails” error.
Regards,
Aleksandar
Excellent article!
I must say this was a lot easier than I thought that it would be.
I am curious, how would I prompt the user to save the file locally?
Keep on truckin’!
64Pacific
Thanks Davids…
It really help me to create a daily backup on my dbase server…
This is very useful, thanks for the time you took to create this function.
Great piece of code, although the UTF-8 characters stored in db are converted into question marks.
Anyone with a solution on this?
@Dejan
Try and store your text fields with utf_general_ci encoding when creating tables, it works for me for Macedonian characters.
Thanks for the script…. It saved my time….
Thank you Dave, you’ve saved my fat a*se!
Very helpful to add database backup functionality..
I got restore functionality
http://dan.cx/blog/2006/12/restore-mysql-dump-using-php/
Hi,
What about innodb tables? How do you back them up?
Thanks
Hi, great work!
I have a table named: ‘pears-1_00-1_49’.
The script bugs because of the special characters in name of the table.
So I fixed it by using sprintf() for the query on three places:
sprintf("SELECT * FROM `%s`;", $table); sprintf("SHOW CREATE TABLE `%s`", $table); sprintf("INSERT INTO `%s` VALUES(", $table);It works for me!
Thanks to all!
Hello David ,
Nice script.I tested it.
Now it is creating sql files backend in that folder.We have to go to that folder to check that it is there or not.
I want option to download that sql file on click of button.How to do that?
One more thing I don’t understand how u use $return.= “\n\n”.$row2[1].”;\n\n”; to get attributes of field.Can u explain me it little bit?
Thanks & Regards,
Sheetal
Hey David,
great info you are providing. I am trying to do a php component for joomla with your code, but I need to specify a suffix on the name for it to only backup the ones that have that suffix.
I want to backup tables that start with
jos_vm
Do you have any idea how can I do this?. or anyone? =).. thanks
The script runs without any problems locally. But when I try to do the same on the web server, I get warnings on
And the back up file is created with just the ‘drop statement’ sometimes or an empty file is returned.
Any clue?
Hey,
Thanks man.your code working perfectly
Regards,
Shafi.SD
very useful stuff, thanks for sharing!
Hi David,
You did well, Its working fine but the sql file have
DROP TABLE ‘table name’
if I was drop the table after import your extract database files its throw error
I was correction that as follows
DROP TABLE IF EXISTS ‘table name’
otherwise excellent
Thank You
Thulasidharan.PT
@primeminister: i tried the code (mysqldump) but nothing happened, can u suggest anything?
Hey!
I am a website developer and I make MLM websites which has so much importance of database.
This is very useful code. Yesterday my client’s website database corrupted somehow and I wasn’t having backup. So If I would have applied this code and given him the module to backup the database himself then I would not be in mess in which I am right now.I had talked to the server support and they have told me to gave me backup file but it will also take time so it is bit unwanted situation.I am in it though I haven’t done anything wrong but one thing is I also haven’t done the right thing.
So from now on I’ll give this functionality to backup to all my new clients and also the existing ones.
I have also modified the code which will give you file to save when you run it. This may be useful to others who has been in situation that I am right now.
backup_tables('hostname','username','password','database_name'); /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*') { global $file; $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); //get all of the tables if($tables == '*') { $tables = array(); $result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return.= 'DROP TABLE if exists '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = ereg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }Hie, the code is giving me syntax error on
window.close();How to prompt to save the file instead into server?
… the problem is that when an entry is NULL i get at the backup ”
Is there a way to get the word NULL instead of ” ??
Hi David..
couldnt thank you enough..so heres an infinite loop for you..cheers!!
while(0<1) { echo "Thank you David"; }anynone know a solution for my problem ?
@allos
follow this if you want NULL to appear in your field instead of ” when the data field is set to NULL
if (isset($row[$j])) {
if($row[$j] == NULL) {
$return .= ‘NULL’ ;
}
else {
$return .= ‘”‘.$row[$j].'”‘ ;
}
}
else {
$return.= ‘””‘;
}
but also remember that if the particular column attribute of your table is set to NOT NULL, then your import function will fail coz it will try to insert a NULL. so make sure that the column allows to insert NULL, in other words it should not have the NOT NULL attribute set when you are creating the table..it should rather have DEFAULT NULL attribute set.
Awsome, worked like a charm, thanks
Simply great.
U made my day david.!!!
Gr8 work. Awsome.
To make it working with PHP 5.3+
replace:
$row[$j] = ereg_replace("\n","\\n",$row[$j]);with:
$row[$j] = preg_replace("#\n#", "\\n", $row[$j]);Another thing to mention, the script is not working if you have a table name with a dash in.
Thanks David for the script!
Thank you very very very very very very very very very much .. :)
hi every one,
When i am tried to save backup file on local drive with below codes. Then file was saved successfully with all the database struction but in the bottom this file also contain my html and php codes. Can any one tell me how i remove these my html page content?
Dear gavshouse,
When i am tried to save backup file on my local drive with given function by adding in bottom of the given backup function. Then file was saved successfully with all the database struction but in the bottom this file also contain my html and php codes. Can you tell me how i remove these my html page content?
When i am tried to save backup file on my local drive with given function by adding in bottom of the given backup function. Then file was saved successfully with all the database struction but in the bottom this file also contain my html and php codes. Can you tell me how i remove these my html page content?
Hey David,
I do see a problem with this. If the data is int like an “id” column it would really screw up your tables. You could check if it’s int in PHP is_int() so you don’t put quotes around it.
As they say, real me do not do backups…
hello i hope you can help me.
i create a database call ‘operativa’ using wamp and need to backup it and then restore. user root no pass.
if you could instanciate the script with an example so i copy paste it to eclipse php.
i dont now so much english so sorry about that.
Thanks David! Works like a Charm!! :)
Hi David,
Just used your script – worked a treat and saved me from a *big* problem.
Best wishes, Paul
if you have problems to import with the ” (quotes) in the SHOW TABLES query, you can replace it with `
$needle = '"'; $haystack = "`"; //replace " with ` while(in_array($needle,$row2)); { $row2 = str_replace($needle,$haystack,$row2); }Hi,
thanks for cool script. My problem was “memory limit exceeded” because of dumping really big DB. I’ve changed few lines to make it append to file after each table, not a whole DB in one time and now it works great. Do You have any similar scipt which would do the “load” function?
Hi Vesela,
could you please help me in what change you made to dump really big DB. I have the same problem and could anyone also help me to create its log file to see what’s happening behind the scenes.
Though, It is great script, was looking for years.
Thanks!
php dont reinvent whell
thks
This worked perfectly for me. Thank you very much!
It’s Good@!
thank you for sharing this script
Love the script, works great with single tables. However, when I attempt * the script kind of stops, no file is written and when I echo out the $table it appears to only attempt processing 50% of the table in my DB.
In PHPAdmin the file comes out at around 4 mg in gzipped uncompressed it’s 25mg. Is there a size of data limitation which can be overcome ?
Thanks, nice script)
Now it’s good to see some easy script to restore the data
Just changed
$handle = fopen('path/to/file/db-backup-'.date("Y-m-d-His").'-'.rand(0,999).'.sql','w+');Created easy script to load dump.
At first modified original script so the create table statement fit it 1 line
$row2 = mysql_fetch_row(mysql_query(‘SHOW CREATE TABLE ‘.$table));
$row2 = str_replace(“\n”,””,$row2);
function load_dump($host,$user,$pass,$name,$filename){
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
$lines = file($filename);
foreach($lines as $line){
if(strlen($line)>1){ // to avoid \n lines
mysql_query($line);
}
}
Is it OK? On 6 Mb table was working ok.
Hi Andrew,
Could you help?
Saw you emailing script, gave it a try, got it to work, only the email sending part however I was not able to get the attachment element to function because when adding extract below as in your script the page crashes. If I rem it out the pages runs ok but with the backup file being attached and re-code to include your script in the backup script and not calling it as a function.
print("fwrite($handle,$return); sendBackup($filename, $name); fclose($handle);");Hi David,
you ROCK! This script is exactly what I was looking for. I’ve used it in my latest WordPress Plugin called Snapshot Backup: it reads out the database, then TAR’s the entire installation and FTP’s it to an off-site location.
I gave you a credit in the admin menu – hope that’s OK?
All the best from London—
JAY
thank you david.
henri
indonesia
it’s great!!!!!!!!!!!!1
Hi
I need help with this line
/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
I want to backup specific table but through the form where i can choose table to backup.
Example:
ALL
TABLE 1
TABLE 2
// php code for db backup
$table_tobackup=$_GET['table'];
backup_tables('localhost','root','****','katalog');
function backup_tables($host,$user,$pass,$name,$tables = '$table_tobackup')
.
.
.
etc
I’m getting this error: Table ‘katalog.$table_tobackup’ doesn’t exist
Any idea how to load that variable $table_tobackup into $tables.
Thanks
Thank you, this script worked for me.
Well, i noticed this script is a bit outdated. Please post a more up-to-date one, oh! Lets hope i don’t have to debug it again. :)
There is a small nonsense in the script.
for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { ... } $return.= ");\n"; } }the outmost for here is pointless. It actually works because it iterates doing something only once, in following iterations
mysql_fetch_rowalways returnsfalse.But this
foris really confusing, it took me a while to find out why it is in the script (or why the script is actually working correct).I think it should be removed from the script here.
Who has this error:
Notice: Undefined variable: return in *BLABLABLA* on line *BLA*
have to change this:
$return.= ‘DROP TABLE IF EXISTS ‘.$table.’;’;
to:
@($return.= “DROP TABLE IF EXISTS “.$table.”;”);
It gives error while replacing the line with @($return.= “DROP TABLE IF EXISTS “.$table.”;”);
for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = ereg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } }<please remove first for loop from the script, its do nothing but just creating confusions to other
I have a question regarding the above script
What is the use of this loop
for ($i = 0; $i < $num_fields; $i++)
i had made some editing in code and checked that this loop will work only 1st time and from next time it's inner part doesn't execute, so anyone can explain me its use !
Looks great script.
Will modify it a little so I can store it in another mysql database instead of in a output file.
Hi!
Is there any easy script to return backup?
This script is fantastic and creates file, but I need script to run it.
Thanks
I got an error like this? Any idea
Warning: fopen(db-backup-1306427001-fe4047d04bbd7e3e2ca225a30121879c.sql) [function.fopen]: failed to open stream: Permission denied in /XXXX/mysqlyedek.php on line 56
Warning: fwrite() expects parameter 1 to be resource, boolean given in /XXXX/mysqlyedek.php on line 57
Warning: fclose() expects parameter 1 to be resource, boolean given in /XXXX/mysqlyedek.php on line 58
Great script, David, thanks for sharing.
Extremely great script.
I had used it but have problem
Deprecated: Function ereg_replace() is deprecated in \www\db_backup\tmp\bckup.php on line 44
For a solution i had updated the code adding additional function
<?php backup_tables('localhost','root','','new_painting'); /* backup the db OR just a table */ function clean($str) { if(@isset($str)){ $str = @trim($str); if(get_magic_quotes_gpc()) { $str = stripslashes($str); } return mysql_real_escape_string($str); } else{ return 'NULL'; } } function backup_tables($host,$user,$pass,$name,$tables = '*') { $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); //get all of the tables if($tables == '*') { $tables = array(); $result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return = 'DROP TABLE '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); //$row[$j] = ereg_replace("\n","\\n",$row[$j]); //$row[$j] = preg_replace("\n","\\n",$row[$j]); $row[$j] = clean($row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($jActually i managed once to delete from table 6000 products by misstake (incomplete query) and even i had backup (week old ) my boss werent really pleased. So i decided to make backup script instead of using phpmyadmin export function. Thank you very much for this, you saved me lots of work :)
I developed in a few minutes a Visual Basic App that uses mysqldump.
This script is brilliant though, but it keeps the backup in the server. If you someday need a backup really fast and, for example, your ISP or the server are down, it is more convenient to have the .sql file next to you in your PC.
I’ve improved this routine into something faster by removing unneeded cycles, changing a couple of workarounds into native and faster functions, and adding bzip2 compression and mail attachment of the file, it runs 2.5times faster than what this one does for me.
This is the code explained:
http://www.ephestione.it/backup-zip-mysql-database-php-email-attachment/
Thanks for posting this! It’s a lifesaver. I made a heavily modified copy to back up a database with over 2GB of data and 100,000 tables.
I’ve got two sections of a PHP file, one that determines what the next table will be and one that actually does the backup. By using redirects and backing up one table at a time bouncing between the two sections of code and passing status / table info in get variables I can back up my database without increased server load, something that both mysqldump and phpmyadmin failed to do!
If you add these lines make copy of Stored Procedures too
//------------ /* Backup Procedure structure*/ $result = mysql_query('SHOW PROCEDURE STATUS'); while($row = mysql_fetch_row($result)) { $procedures[] = $row[1]; } foreach($procedures as $proc) { $return.= "DELIMITER $$\n\n"; $return.= "DROP PROCEDURE IF EXISTS `$name`.`$proc`$$\n"; $row2 = mysql_fetch_row(mysql_query("SHOW CREATE PROCEDURE `$proc`")); $return.= "\n".$row2[2]."$$\n\n"; $return.= "DELIMITER ;\n\n"; } //------------ /* Backup Procedure structure*/Hi David, Great script that has served me well for a while – but since the upgrade to php 5.3 the
ereg_replace()has been deprecated. You probably know this, but thought I’d drop it in as this post still seems to be getting plenty of hits after three years on air! Way to go…Changing
ereg_replace()topreg_replace()should cut the mustard, just don’t useereg_replace()to do it!Danny
Thank you for the great codeĐ
I add this line in order to save file to sub-folder
$path='/home/httpd/vhosts/xxxxx.com/httpdocs/rucno/back_up/files/';$handle = fopen($path.'/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
if (fwrite($handle, $return) === FALSE) {
echo "cannot write to file ";
exit;
}
echo "Done! ";
fclose($handle);
Hey David, That’s a very specific and small code.
Thanks man !!!
Just one thing: when copy the text it might convert > to <
It took me a while to figure it out.
What I ment:
it might convert
< to
<It took me a while to figure it out.
From looking at this code alone, I can see it is very useful.
I have bookmarked this webpage so I can implement it when I get some free time (hopefully later today).
Rather than saving it as:
'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql'I prefer to save it as:
'db-backup-'.date(d-m-y-H-i-s).sql'Since I can’t convert timestamps in my head (not that good at maths yet hehe)
That’s just down to personal preference though :p
Thanks Again!
Oops, forgot my quotes in the date() function in the above comment. Never mind ^.^
This is a good script, however if you manage several databases you may want to get a notification when the backup task fails rather than check this manually. In addition, sending the backup files to a remote server is usually necessary and for this you would need to add another script.
At work I used to run several scripts for my backups but looking for backup tools I found MySQLBackupFTP (http://mysqlbackupftp.com/), the only disadvantage with the free version is that it only allows you to schedule 2 databases but other than that it is very helpful. It can ftp your backups, compress the files, send notifications and you can even connect to a PhpMyAdmin instance.
Hi !
if i have 200 tables , and each table have 1000 records .
error : max_execution_time
how i will fix this error ?
pls , help me .
any one have completed backup code now in running form , i am unable to collect all piceses and run fine,
please complete script
thanks in advance
Thnx it works great!!!!!!!
There are just too many mods and errors mentioned, and corrections, and with this page changing all the quotes and apostrophies(sp?) to other codes, I give up, going elsewhere… :)
AND you cant read the code in those darn BLACK BOXES… damn!
Thanx it works fine………
Hi! Thanks! I am using it and it works great.
I have wrapped this code with a nice PHP config file, and published it on my website as open source
http://softov.org/webdevtools
Your credits are there of course. But what is the licence for this peace of code? GPL? GPLv3?
Thanks
Ori a
Many thanks for this great little script!
how to make the download option to download the backup file appear when the php script has run? please help me
please anyone help me…. how to make download option for downloading the backup file appear after the php script run?
If you use the wrapper I wrote for this script you’ll need to put a www-visible folder in $backupmysql[‘folder’]
and select any file name you need and put it in $backupmysql[‘filename’]
A direct link for this wrapper (might not work in far future)
http://softov.org/webdevtools/?a=backup-mysql.php
thank you so muchhh!! :DD…
thanks a lot :)
Thank you! You saved me some time!
Hi – great work – works like a charm although I am having trouble with larger db’s…?
I have one that is showing at 300MB – is there an easy fix to get this working?
Cheers
Using your blog Dave, I found a solution…
I set the script its own limits and presto!
Thank you for the nice and simple code. But does this also support large mysql databases???
Nice quick little script David, While there is a bunch of ways of addressing this I like to have as much control as possible and this is a good base. I have ammended/hacked it into line with my intentions, would advise anyone with bigger db’s to use this script, but put limited dumping in, e.g. when $return hits x number of characters dump to the file. This is easy to do and will be useful two fold, low memory environments won’t fall over and for higher efficiency.
Anyhow cheers David.
Also….I had to replace “” with NULL on the insert of blank fields, by way of how I setup things.
Hi David!
Congrats for the great script.
Most probably you can see the trackback of this article in one of mines. I just added it.
I hope you don’t mind that I used some parts of your script to make a PHP helper. If you want me to remove it, please let me know.
Basically it’s a PHP file that anyone can upload to their website, access it, fill the form with the database connection credentials and hit the backup button. At this moment only a full database backup is available.
If anyone is interested, it can be download from here: http://mihaifrentiu.com/mysql-backup-php-helper
Thank you again for sharing this script.
Hi PHEARIN,
If you are talking about the helper I mentioned above, then most probably you didn’t unpack the archive.
Great script. Here’s a few mods to get it working on my local apache setup and bring it up to date a bit and make PhpMyAdmin happier importing it:
(replacing depreciated ereg_replace)
change:
$row[$j] = ereg_replace("\n","\\n",$row[$j]);to:
$row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);(easier importing to PhpMyAdmin)
remove:
(zipity zip it and simplify date format)
replace:
$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle);with:
$gzdata = gzencode($return, 9); $handle = fopen('db-'.date("ymd").'.sql.gz','w+'); fwrite($handle, $gzdata); fclose($handle);Very usefull script!! thank you!!
Here is a modified script that backup your databaseS. That would backup all of your databases, if you have more than one! It also creates directory like “backup_2012-02-23” (so that you can see them in alphabetic/time order) in which every databases is saved in its own .sql file like “dbname.sql”!
http://pastebin.com/NshY4qA2
This is for a daily backup!
For those who are having trouble with binary or blob (images and other non-text data).
Covert the binary to hex using
$binary_data = hex2bin($binary_data);
Then append “_binary ox” before inserting it to the query (without the double quotes!)
$return.= '_binary 0x'.$row[$j] . ',' ;
goo.gl/z4DCq
To David: “goo.gl/z4DCq” This is a legit URL. You may want to update your site.
How do I convert to binary in the.
Cos I don’t know where to put the variable binary_dat
It could be a security hole. because any one can download that database backup file.
is int it ? if not i need some explanation please!
Hello all,
This script is not working for the table with plenty of rows eg. my table have 74000+ rows….. any suggestions….
Sorry for a probably stupid question.
I made a backup .sql file with the script.
Now let’s say my original database was totally wiped out, doesn’t exist anymore.
How do I restore it from this backup??
If I create a new totally empty database and try to import this file it gives error messages because it seems to expect that all the tables already exist in the DB and it can just enter the data. But of course they don’t since the DB was wiped out.
Is there a command to tell restore all tables and fields from the backup file?
Kathy
can u please tell me how to save it to local drive
Good! Thanks david~!
You should use DROP TABLE IF EXISTS. Will work fine when the table exists and when it doesn’t!
Great script, thanks a bunch.
For those that have tables with dashes/hyphens in their name (ex. Customer-Payment), all you need to do is add back ticks (“) around the table name in the MySQL code to get the script to work 100%.
So instead of
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));You would need this below instead if you have dashes/hyphens in your databases table names. It won’t affect results if you don’t have dashes either.
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE `'.$table.'`'));And you will need to replace the other lines in the script using the above technique with the back ticks (“) –
//for ($i = 0; $i < $num_fields; $i++) //{ while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO `'.$table.'` VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); //$row[$j] = ereg_replace("\n","\\n",$row[$j]); //$row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]); $row[$j] = preg_replace("#\n#", "\\n", $row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } // } $return.="\n\n\n"; } //save file $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle);Hi!
Is there any easy script to return backup?
This script is fantastic and creates file, but I need script to run it.
Thanks
How about using mysql_real_escape_string instead of addslashes ?
Hi David,
Is this applicable to huge database? Say, like 8gb?
Thanks,
Hello,
I suggest using
mysql_query("SHOW FULL TABLES WHERE `Table_type` = 'BASE TABLE';");And another go with
mysql_query("SHOW FULL TABLES WHERE `Table_type` = 'VIEW';");And
"SHOW CREATE VIEW"; (without selecting data from the views);Hi,
Is this applicable in large database like more than 1gb?
Thanks,
Great stuff David. You got me out of a tight spot. Thanks.
you’re missing a bracket, and some of this doesn’t make sense. Ah, shame… if only I could just use shell_exec()… blah.
Great time saver! Will definitely set this up as a cron and sleep easier at night!
Great script, thanks for creating/posting this!
Thanks David
Your script is great.
I just tested to transform your script to PDO:
It works, however SHOW CREATE TABLE seems not to accept a PDO-Parameter for TABLE
Thank you
Marcus
This code is very much helpful for developers and users as wel. It helps me a lot to get backups of my mysql databases. Thanks a lot.
Thanks a lot, Great Article.
Sharing my improvements:
(sorry – my linefeeds got smashed-up, I am trying to improve)
I programmed a small extension to handle numeric values explicitly.
This leads to:
– better standard conformity (no quotes for numeric values)
– less file space required (removing quotes)
– speedup (skip escaping for numeric values)
HowTo:
1. Create an array with all numeric types.
2. Get attribute-types for actual table, strip redundant information (in brackets).
3. Setup an array $numflag indicating numeric datatype for each attribute
4. Assign numeric values without quotes.
CODE Snippets:
//1..at the top of function $numtypes=array('tinyint','smallint','mediumint','int','bigint','float','double','decimal','real'); //2.. at the top of table-loop $typesarr=mysql_fetch_array(mysql_query('SHOW COLUMNS FROM '.$table)); //3.. below 2 for($i=0; $i<$num_fields; $i++) { $acttype=trim(preg_replace('/\s*\([^)]*\)/', '', $typesarr[$i]['Type']));// strip brackets from type if (is_numeric(array_search($acttype,$numtypes))){//numeric type false cannot be checked correctly $numflag[$i]=1;}else{$numflag[$i]=0;} } //4.. in the fields loop (innermost) if ($numflag[$j]==1){$return.= $row[$j] ; } else{ $row[$j] = addslashes($row[$j]); $row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]); $return.= '"'.$row[$j].'"' ; }Thanks to David and all other Contributors
Marcus
Hi,
i’m just afraid of this line,
$return .= ‘DROP TABLE ‘.$table.’;’;
what if i gets problem with the code after dropping a table. . .
I was having trouble with the whole NULL, NOT NULL situation and here is my solution:
for($j=0; $j<$num_fields; $j++) { if(is_null($row[$j])) { $return .= 'NULL' ;} else { $row[$j] = addslashes($row[$j]); $row[$j] = preg_replace("/\n/","\\n",$row[$j]); if(isset($row[$j])) {$return .= '"'.$row[$j].'"' ;}else {$return .= '""';} } if ($j<($num_fields-1)) { $return.= ','; } }Also if you want to encode UTF-8 characters properly instead of the question marks add this line:
mysql_set_charset('utf8',$link);
Right after:
$link = mysql_connect($host,$user,$pass,true);
So it becomes:
$link = mysql_connect($host,$user,$pass,true);
mysql_set_charset('utf8',$link);
Hi, thanks for the script with little modifications it works perfect for me :) Perfect stylized and soft :)
Can’t we use MySqlAdmin to export the database to our system from the website
Thanks for you code, it’s really make me headache since there is no system() command can be used in Synology NAS. Your code is work perfectly for me.
GREAT JOB
Great script, I’ve found it quite handy when I don’t have access to the DB via SSH or phpMyAdmin. One issue though, when exporting large databases (in my case, it was 98 MB of data), it gives a “memory exhausted” error.
It can be fixed by simply creating the file handle before the table loop, and writing to it for every new string, instead of writing the whole thing at the end. This kept the script from building up a 98 MB string in memory. Also, as several other people have mentioned, I needed “IF EXISTS” on the DROP statement because I was importing it into an empty schema.
thank bro, its work fine on my project..
Thank you for great script. A timesaver!
David my question is when did you update the code last time?
There exists some comments that gives tips about refactoring lines.
Did you have opportunity to check and edit code.
Keep up the good work!
Hi!
Is there any script for postgresql database backup & restore?
what is query in postgresql for “Show create table” like mysql?
Please help me.
Thanks
i just confused why no body answer the errors,
for tese errors
Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in C:\alpstanda\xampp\htdocs\training\Eswari\backup.php on line 33
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\alpstanda\xampp\htdocs\training\Eswari\backup.php on line 37
just add
if (empty($result)) continue;
after $result in foreach and for this error
Warning: fopen(jegga/dbbackup_19.08.2011_06:03:22_.sql) [function.fopen]: failed to open stream: Invalid argument in C:\alpstanda\xampp\htdocs\training\Eswari\backup.php on line 63
just create the jegga folder becuse fopen can create files but not folders
HOSSEIN, you have some warning messages. First check and double-check the lines that give warnings – backup.php line 33, 37 and 63. If you still want help, please post the lines you get warnings for.
You seem to have colons “:” in the file name. I think colons are forbidden, try replacing them with dots “.” or dashes “-“.
sir how can i automaticaly delete the previous backup?
MCMC, change the line near the end of the function
$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');to
$handle = fopen('db-backup.sql','w+');and try
unlink('db-backup.sql');Do you have any idea why it produces
CREATE TABLE `cards`instead ofCREATE TABLE 'cards'?Also I get Greek letters as questionmarks. Instead of ατομικός I get ????????.
I tried text-mode translation flag (‘t’), and ‘b’ to force binary mode in fopen with same results.
Is this working only in English?
Thank you so much. I use it with a little change. it works great :X
it’s very small and very good. best backup php script
thanks
im a php noob. can somebody tell me how can i work this code like trigger a button in a php page? thx
Quite old post yet still useful. Works like a charm however the code has a bug. It doesn’t close the mysql connection. In my case, I used this code in a Joomla site, backup went fine however Joomla failed to execute any queries from thereon. After closing the connection, it started working fine. Code below.
mysql_close($link);
Cheers!
Thank you for this. I altered the drop statement to be: drop if exists so I could drop the backup into an empty database for debugging.
Great code, great work. It works great, even with HTML Text in my exports. Just as good as the PHPMyAdmin export feature, but now I can automate it!
Thank you again.
Dear David thank you for great script.
Manually I made this modifications:
* Added
$return = '' ;before
$return.= ‘DROP TABLE ‘.$table.’;’;
* Changed this
$row[$j] = ereg_replace("\n","\\n",$row[$j]);to this:
$row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);* Added this to last line:
mysql_close($link);* Also there exists comments about the tables that have dashes inside its name.
Do you plan to edit the original script?
Regards
what if I only want to backup a specific table or tables from the database and not the whole database?
$return.= ‘DROP TABLE IF EXISTS ‘.$table.’;’;
IF EXISTS would be a great addition, otherwise will generate error when importing the sql.
Good article, I can also suggest to use XCloner ‘s great tool for MySQL / Full site backup management.
Can you please help me with an issue. I am having a table with around 1.7GB data. I used your function(with out the DROP functionality), but as the size is large its creating error. I have access to phpmyadmin and FTP of the hosting server. So where should I modify to extract this huge amount of data?
Great script, got me started, and I needed as well the utf8 encoding.
I also disabled foreign key constraint checks, and wrapped the sql dump into a transaction, by adding before the main foreach loop:
And after the same loop:
After I initially commented I appear to have clicked on the -Notify me when new comments are added- checkbox and now each time a comment is added
I recieve four emails with the same comment. Perhaps there is a way
you are able to remove me from that service? Thanks a
lot!http://www.ign.com/boards/threads/partners-season-1-episode-5-online-2-broke-guys.
452738729/
You could definitely see your expertise in the article you write.
The sector hopes for more passionate writers like you who aren’t afraid to mention how they believe. Always follow your heart.http://www.ign.com/boards/threads/revolution-season-1-episode-7-online-the-childrens-crusade.452738745/
Thanks a lot for this script! It helps me a lot.
Danny
thanks a lot for the script!
This will really help with a personal project I’m working on where regular DB backups are a must
:D
thanks for sharing this function. i am appreciated :)
Hi, I wish for to subscribe for this webpage to take
latest updates, therefore where can i do it please help.
A highly configurable version of this tool at
http://tablefield.com/backup-mysql
David, you are a life saver, thank you very much for this script!
I have taken most people’s suggestions into consideration (still using mysql rather than mysqli) and below you can find the complete script:
<?php // Specify the values below to make the script work. backup_tables('localhost','username','password','db_name'); function backup_tables($host,$user,$pass,$name,$tables = '*') { $link = mysql_connect($host,$user,$pass); // Check via PhpMyAdmin to see what character your database is using mysql_query('SET NAMES utf8'); mysql_query('SET CHARACTER SET utf8'); mysql_select_db($name,$link); $return = '' ; //get all of the tables if($tables == '*') { $tables = array(); $result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; //if (empty($result)) continue; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return.= 'DROP TABLE IF EXISTS '.$table.';'; // If you are getting errors related to fetching, uncomment the next row: //if (empty($result)) continue; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; // If you are getting errors related to fetching, uncomment the next row: //if (empty($result)) continue; for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; // If you are getting errors related to fetching, uncomment the next row: //if (empty($result)) continue; $columns = count($row); for($j=0; $j<$columns; $j++) { $row[$j] = addslashes($row[$j]); //$row[$j] = ereg_replace("\n","\\n",$row[$j]); $row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($jThis works fine with my web hosting provider.
Suggestions for improvement:
1. Can anyone rewrite the script with using mysqli (not just a row or two but post the whole thing)? This is because future PHP versions may end up not supporting the older mysql command.
2. This is related to OpenCart CMS only: Three tables from OpenCart was not possible to export (fetching errors). This was due to the tables’ name being ‘option’, ‘order’ and ‘return’. MySQL language is viewing that as boolean arguments. The only solution was to rename those tables (e.g. via PHPMyAdmin) to for example option_admin etc. and then re-linking those in the website (by searching all web files for: DB_PREFIX . “option` , DB_PREFIX . “option` , DB_PREFIX . “option` and replacing that with DB_PREFIX . “option_admin` , etc.
Thank you again, Dave and others for the numerous feedbacks!
Hmm, it was cut of cut off. So here is the rest (starting with the line that was cut off):
if ($j
Hmm, it was cut of cut off. So here is the rest (starting with the line that was cut off):
if ($j
I have been browsing online more than 2 hours today, yet I
never found any interesting article like yours. It is pretty
worth enough for me. Personally, if all web owners and bloggers made good content as you did, the
internet will be much more useful than ever before.
|
I couldn’t resist commenting. Perfectly written!|
But what can you do other than grin and bare it!
thanx alot for this choco-pie…
Thank you. Thank you Thank you.
I bow down to you over and over.
It works. WOOT!
one correction put an if exists in your drop statements!
but other than that great script!!!!
This design is wicked! You definitely know how to keep
a reader entertained. Between your wit and your videos, I was
almost moved to start my own blog (well, almost.
..HaHa!) Fantastic job. I really enjoyed what you had to say, and
more than that, how you presented it. Too cool!
Added some compression to reduce the size of the backups. This works well when importing using phpMyAdmin.
The code changes form:
//save file $handle = fopen(BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle);To the following where I created a boolean flag to which I pass as a parameter in the function:
if($compression) { $zp = gzopen(BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql.gz', "w9"); gzwrite($zp, $return); gzclose($zp); } else { //save file $handle = fopen(BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); }Fully updated script using MYSQLI, added compression in the SQL insert statements, added Column names to Insert Statement by Table, added ‘IF EXISTS’ to DROP TABLE lines in case you are moving servers or inserting on a clean slate to remove any possible errors. Added the Compression as supplied by ‘Gerard’ above.
Does anyone know if you can add a password to the GZip through PHP so it can be safely emailed? I have had bit of a search around but have not been able to find much on this topic, or if you know of an alternative method to GZip that would be great as well if I can add encryption, it’s not one of those areas I’ve delved into much.
//You should test your script, once confirmed all working, add '@' in front of all 'mysqli' commands to stop any errors being displayed. //Script Variables $compression = true; $BACKUP_PATH = ""; //I have this in a file elsewhere, makes it easier to keep consistent across pages. $user = ""; $pass = ""; $host = "localhost"; $db = ""; $conn = mysqli_connect($host,$user,$pass,$db); backup_tables($conn); /* backup the whole db by default ('*') OR a single table ('tableName') */ function backup_tables($conn,$tables = '*') { //get all of the tables if($tables == '*') { $tables = array(); $result = mysqli_query($conn,'SHOW TABLES'); while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through data $return = ""; foreach($tables as $table) { $result = mysqli_query($conn,'SELECT * FROM '.$table); $num_fields = mysqli_num_fields($result); $return.= 'DROP TABLE IF EXISTS '.$table.';'; $row2 = mysqli_fetch_row(mysqli_query($conn,'SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; $return.= 'INSERT INTO '.$table." ("; $cols = mysqli_query($conn,"SHOW COLUMNS FROM company"); $count = 0; while ($rows = mysqli_fetch_array($cols, MYSQLI_NUM)) { $return.= $rows[0]; $count++; if ($count < mysqli_num_rows($cols)) { $return.= ","; } } $return.= ")".' VALUES'; for ($i = 0; $i < $num_fields; $i++) { $count = 0; while($row = mysqli_fetch_row($result)) { $return.= "\n\t("; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); //$row[$j] = preg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $count++; if ($count < mysqli_num_rows($result)) { $return.= "),"; } else { $return.= ");"; } } } $return.="\n\n\n"; } //save file if ($compression) { $zp = gzopen($BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql.gz', "w9"); gzwrite($zp, $return); gzclose($zp); } else { $handle = fopen($BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); } }Thanks David, and Todd Wiggins. I merged David’s original and Todd’s mysqli copy, removing compression and unique db specific info, but keeping mysqli, IF EXISTS, and download option. Also like to start with an include for db connection, but inline $con is still there, just commented out…
/* $user = "yourusername"; $pass = "yourpassword"; $host = "localhost"; $db = "yourdatabase"; $con = mysqli_connect($host,$user,$pass,$db); // let's get the variable $con from our included config file instead... */ include '../inc/config-db.php'; backup_tables($con); /* backup the whole db (‘*’) OR specific table(s) (‘tableName’ or 'tableOne, tableTwo') */ function backup_tables($con,$tables = 'categories, customers, dispatch, freight, location_rates, notes, quotes, text_boxes, variables') { // $link = mysql_connect($host,$user,$pass); // mysql_select_db($name,$link); //get all of the tables if($tables == '*') { $tables = array(); $result = mysqli_query($con,'SHOW TABLES'); while($row = mysqli_fetch_row($result, MYSQLI_NUM)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = mysqli_query($con,'SELECT * FROM '.$table); $num_fields = mysqli_num_fields($result); $return.= 'DROP TABLE IF EXISTS '.$table.';'; $row2 = mysqli_fetch_row(mysqli_query($con,'SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysqli_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = ereg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n\n\n"; } //save file $handle = fopen('rvg-db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); $filename = 'rvg-db-backup-'.time().'.sql'; Header("Content-type: application/octet-stream"); Header("Content-Disposition: attachment; filename=$filename"); echo $return; } mysqli_close($con);my lifesaver!!!!!!!! tnx tnx tnx!!!!!!!!!
how about with restore? with upload file-style?
MySQLi Version https://gist.github.com/paulomcnally/4756938
how do you make those save file prompts? the clients want to be able to save the backup file to any directory he wants through those download/save prompts.
and how to rename the sql file with dates only? not with those different characters..
Hi, I’ve read this code and comments and compiled it for simple web page,
which I can put on server and use for regular backup, ie by wget.
I removed unnecesary `for` loop by $i, and added filtering by table prefix.
I tested it with SMF forum database and it works. Of course there is no VIEW
etc. support.
Usage with wget:
wget --content-disposition --post-data="auth=password" http://server.org/backup_db.php db_server='localhost'; $db_bkp->db_user='user'; $db_bkp->db_pass='pass'; $db_bkp->db_name='base'; $db_bkp->db_prefix='prefix_'; /* cfg --end*/ if( isset( $_POST['auth'] ) && $_POST['auth']==$auth ) { $db_bkp->sql(); } else { echo( ' db_server.'-'.$db_bkp->db_user.'-'.$db_bkp->db_name .'.sql"> '); } class backup_tables { public $db_server; public $db_user; public $db_pass; public $db_name; public $db_prefix; function sql( $filename=NULL ) { $len = strlen( $this->db_prefix ); $return='-- Backup from `backup_tables` class.'."\n" .'-- db server: `'.$this->db_server.'`'."\n" .'-- db user: `'.$this->db_user.'`'."\n" .'-- db name: `'.$this->db_name.'`'."\n"; if( $len > 0 ) { $return.='-- tables: `'.$this->db_prefix.'*`'."\n"; } $return.='-- Date: '.date( DATE_RFC822 )."\n\n\n"; $link = mysql_connect( $this->db_server, $this->db_user, $this->db_pass ); mysql_query( "SET NAMES 'utf8'" ); mysql_select_db( $this->db_name, $link ); /*get all of the tables*/ $tables = array(); $result = mysql_query( 'SHOW TABLES' ); while( $row = mysql_fetch_row( $result ) ) { if( $this->db_prefix == substr( $row[0], 0, $len ) ) { $tables[] = $row[0]; } } /*cycle through*/ foreach( $tables as $table ) { $result = mysql_query( 'SELECT * FROM '.$table ); $num_fields = mysql_num_fields( $result ); $return.= 'DROP TABLE IF EXISTS '.$table.';'; $row2 = mysql_fetch_row( mysql_query( 'SHOW CREATE TABLE '.$table ) ); $return.= "\n\n".$row2[1].";\n\n"; while( $row = mysql_fetch_row( $result ) ) { $return.= 'INSERT INTO '.$table.' VALUES( '; for( $j=0; $j<$num_fields; $j++ ) { $row[$j] = mysql_real_escape_string( $row[$j] ); if( isset( $row[$j] ) ) { $return.= '"'.$row[$j].'"'; } else { $return.= '""'; } if( $jdb_server.'-'.$this->db_user.'-'.$this->db_name; if( $len > 0 ) { $filename.='-'.$this->db_prefix; } } header( 'Content-Type: application/octet-stream' ); header( "Pragma: public" ); header( "Expires: 0" ); header( "Cache-Control: must-revalidate, post-check=0, pre-check=0" ); header( "Content-Type: text/x-sql" ); header( 'Content-Disposition: attachment; filename="'.$filename.'.sql"' ); echo( $return ); } } ?>Hello Piotr,
Does it open a prompt window for downloading?
Hi
Thanks for your valuable code.
This is working fine.
Thanks again.
Thank you for the valuable code share.
/* Version with VIEW */ /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*') { $first_table_veriable=$tables; $return=null; $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); // BASE TABLE SAVE //get all of the tables if($tables == '*') { $tables = array(); $result = mysql_query("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'"); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return.= 'DROP TABLE IF EXISTS '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = ereg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n\n\n"; } // WIEW SAVE //get all of the tables $tables = $first_table_veriable; if($tables == '*') { $tables = array(); $result = mysql_query("SHOW FULL TABLES WHERE Table_type = 'VIEW'"); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table) { $result = mysql_query('SELECT * FROM '.$table); $return.= 'DROP TABLE IF EXISTS '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE VIEW '.$table)); $return.= "\n\n".$row2[1].";\n\n"; $return.="\n\n\n"; } //save file $file_name='db-backup.sql'; $handle = fopen($file_name,'w+'); fwrite($handle,$return); fclose($handle); return $file_name; }i see some error when try to restore backup.
[Err] 1292 – Incorrect datetime value: ” for column ‘PaymentDate’ at row 1
[Err] 1366 – Incorrect integer value: ” for column ‘Priority’ at row 1
i think that the order of the fields is incorrect
i find the solution:
where a filed value is null you should put NULL instead of “”
there was another problem
this script doesn’t work correctly with unicode text!
Thanks a lot man, i have set it up for my own blog and my server using crone job, though my host already provide me functionality of doing it from server but after setting it in crone job im free…, :)
I have used automysqlbackup and Dropbox. It is very simple to install and configure: http://webdevnote.com/article/create-a-backup-for-your-ubuntu-server-with-automysql-backup-and-dropbox-6.html
Hello,
There is a problem, can you help?
If the DEFAULT is NULL !!!
Sample:
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`real_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`password` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
AND
If the data record is like the following
Email the field blank !!!
INSERT INTO user VALUES(‘1′,’Adem’,’Adem’,”,’$2a$12$YQ8O6YBynJPwJXmHdtArp’);
BACKUP,
Add to null, If the DEFAULT is NULL !!!
INSERT INTO user VALUES(‘1′,’Adem’,’Adem’, null,’$2a$12$YQ8O6YBynJPwJXmHdtArp’);
I’m sorry, my English is bad
Best regards
Thanks for your valuable code.
This is working.
if foreign constraint fail, just add line
…..
$return = ‘SET FOREIGN_KEY_CHECKS=0; ‘;
//cycle through
foreach($tables as $table)
{
…….
Hello There. I found your blog using msn. This is an extremely well written article.
I’ll make sure to bookmark it and come back to read more of your useful information. Thanks for the post. I’ll certainly return.
Hi,
very good script, I’m just wondering how to avoid the “INSERT INTO” statements when I’ve to backup a database with federated tables.
is not backup database of utf-8
Garnish with a mini-candy cane hanging along the rim of glass or prior to pouring the drink
into glass, crush the candy cane and wet down the glass
rim. In addition to that, here is something else you wouldn’t think of. Just as in other addictions, conquering the beast takes information, a well-developed plan, support and a whole lot of tenacity.
Hi there!
I was testing this in a large database ( the one that has more it has 600.000+ records) and it fails…
any workaround in this script to limit the query count? and maybe add more loops?
Thanks!
//here it is in code display
require 'login.php'; $DBH = new PDO("mysql:host=$db_hostname;dbname=$db_database; charset=utf8", $db_username, $db_password); //put table names you want backed up in this array. //leave empty to do all $tables = array(); backup_tables($DBH, $tables); function backup_tables($DBH, $tables) { $DBH->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_TO_STRING ); //Script Variables $compression = false; $BACKUP_PATH = ""; $nowtimename = time(); //create/open files if ($compression) { $zp = gzopen($BACKUP_PATH.$nowtimename.'.sql.gz', "w9"); } else { $handle = fopen($BACKUP_PATH.$nowtimename.'.sql','a+'); } //array of all database field types which just take numbers $numtypes=array('tinyint','smallint','mediumint','int','bigint','float','double','decimal','real'); //get all of the tables if(empty($tables)) { $pstm1 = $DBH->query('SHOW TABLES'); while ($row = $pstm1->fetch(PDO::FETCH_NUM)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through the table(s) foreach($tables as $table) { $result = $DBH->query('SELECT * FROM '.$table); $num_fields = $result->columnCount(); $num_rows = $result->rowCount(); $return=""; //uncomment below if you want 'DROP TABLE IF EXISTS' displayed //$return.= 'DROP TABLE IF EXISTS `'.$table.'`;'; //table structure $pstm2 = $DBH->query('SHOW CREATE TABLE '.$table); $row2 = $pstm2->fetch(PDO::FETCH_NUM); $ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]); $return.= "\n\n".$ifnotexists.";\n\n"; if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; //insert values if ($num_rows){ $return= 'INSERT INTO `'.$table."` ("; $pstm3 = $DBH->query('SHOW COLUMNS FROM '.$table); $count = 0; $type = array(); while ($rows = $pstm3->fetch(PDO::FETCH_NUM)) { if (stripos($rows[1], '(')) {$type[$table][] = stristr($rows[1], '(', true); } else $type[$table][] = $rows[1]; $return.= $rows[0]; $count++; if ($count rowCount())) { $return.= ", "; } } $return.= ")".' VALUES'; if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; } while($row = $result->fetch(PDO::FETCH_NUM)) { $return= "\n\t("; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); //$row[$j] = preg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { //if number, take away "". else leave as string if (in_array($type[$table][$j], $numtypes)) $return.= $row[$j] ; else $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $count++; if ($count rowCount())) { $return.= "),"; } else { $return.= ");"; } if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; } $return="\n\n-- ------------------------------------------------ \n\n"; if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; } $error1= $pstm2->errorInfo(); $error2= $pstm3->errorInfo(); $error3= $result->errorInfo(); echo $error1[2]; echo $error2[2]; echo $error3[2]; if ($compression) { gzclose($zp); } else { fclose($handle); } }sorry, here is the final version with bugs ironed out and made a bit better. tested and works perfectly. enjoy.
require_once 'login.php'; $DBH = new PDO("mysql:host=$db_hostname;dbname=$db_database; charset=utf8", $db_username, $db_password); //put table names you want backed up in this array. //leave empty to do all function backup_tables($DBH, $BACKUP_PATH, $tables) { $DBH->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL ); //Script Variables $compression = false; $nowtimename = time(); //create/open files if ($compression) { $zp = gzopen($BACKUP_PATH.$nowtimename.'.sql.gz', "a9"); } else { $handle = fopen($BACKUP_PATH.$nowtimename.'.sql','a+'); } //array of all database field types which just take numbers $numtypes=array('tinyint','smallint','mediumint','int','bigint','float','double','decimal','real'); //get all of the tables if(empty($tables)) { $pstm1 = $DBH->query('SHOW TABLES'); while ($row = $pstm1->fetch(PDO::FETCH_NUM)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through the table(s) foreach($tables as $table) { $result = $DBH->query("SELECT * FROM $table"); $num_fields = $result->columnCount(); $num_rows = $result->rowCount(); $return=""; //uncomment below if you want 'DROP TABLE IF EXISTS' displayed //$return.= 'DROP TABLE IF EXISTS `'.$table.'`;'; //table structure $pstm2 = $DBH->query("SHOW CREATE TABLE $table"); $row2 = $pstm2->fetch(PDO::FETCH_NUM); $ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]); $return.= "\n\n".$ifnotexists.";\n\n"; if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; //insert values if ($num_rows){ $return= 'INSERT INTO `'."$table"."` ("; $pstm3 = $DBH->query("SHOW COLUMNS FROM $table"); $count = 0; $type = array(); while ($rows = $pstm3->fetch(PDO::FETCH_NUM)) { if (stripos($rows[1], '(')) {$type[$table][] = stristr($rows[1], '(', true); } else $type[$table][] = $rows[1]; $return.= "`".$rows[0]."`"; $count++; if ($count rowCount())) { $return.= ", "; } } $return.= ")".' VALUES'; if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; } $count =0; while($row = $result->fetch(PDO::FETCH_NUM)) { $return= "\n\t("; for($j=0; $jquote($row[$j]); } else { $return.= 'NULL'; } if ($j<($num_fields-1)) { $return.= ','; } } $count++; if ($count rowCount())) { $return.= "),"; } else { $return.= ");"; } if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; } $return="\n\n-- ------------------------------------------------ \n\n"; if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; } $error1= $pstm2->errorInfo(); $error2= $pstm3->errorInfo(); $error3= $result->errorInfo(); echo $error1[2]; echo $error2[2]; echo $error3[2]; if ($compression) { gzclose($zp); } else { fclose($handle); } }Thank you for script. It is great for shared hosting.
But if you have root access you can use mysqldump. If you want best root option you can use Percona xtra backup for free.
Great! thanks a lot. It helped me a lot. May I suggest a compression after?
I found this on the net for compressing and adjusted it according to my needs. Hope this helps.
if (!(substr($file,-4) != ".sql")) { $fp = fopen( $file, "r" ); $data = fread ( $fp, filesize( $file ) ); fclose( $fp ); $dstFileName=$file.'.gz'; $zp = gzopen( $dstFileName, "w9" ); gzwrite( $zp, $data ); gzclose( $zp ); // !!!!!!!!!!!! unlink ($file); }Hello,
What is the reason for this error?
Parse error: syntax error, unexpected ‘rowCount’ (T_STRING) in D:\EasyPHP\data\localweb\dbyedekle.php on line 86
Line: if ($count rowCount())) {
Hello,
Such an option is it possible?
combine all tables into one backup file: Yes/No
Regards
Hi , thanks for this , I needed a way to backup my BD on a shared hosting so I couldn´t use MySQLDump .
I extended your solution to backup the tables AND the triggers for those tables.I´ll share it so hopefully it will come handy for someone :)
function backupDB($backup_dir) { $con = mysqli_connect ( DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DBNAME ); // get all of the tables $tables = array (); $result = mysqli_query ( $con, 'SHOW TABLES' ); while ( $row = mysqli_fetch_array ( $result ) ) { $tables [] = $row [0]; } // cycle through the tables foreach ( $tables as $table ) { $result = mysqli_query ( $con, "SELECT * FROM `$table`"); $num_fields = mysqli_num_fields ( $result ); $num_rows = mysqli_num_rows( $result ); $return .= "--\n-- Structure for the table $table\n--\n\n"; $return .= "DROP TABLE IF EXISTS `$table`;"; $row2 = mysqli_fetch_array ( mysqli_query ( $con, "SHOW CREATE TABLE `$table`" ) ); $return .= "\n\n" . $row2 [1] . ";\n\n"; if ($num_rows > 0) { $return .= "--\n-- Data dump for the table $table\n--\n\n"; } $i = 0; while ( $row = mysqli_fetch_array ( $result ) ) { if ($i == 0) { $return .= "INSERT INTO `$table` VALUES\n"; } $i++; for($j = 0; $j < $num_fields; $j ++) { if ($j == 0) { $return .= '('; } $row [$j] = addslashes ( $row [$j] ); $row [$j] = mysqli_real_escape_string ( $con, $row [$j] ); if (isset ( $row [$j] )) { $return .= '"' . $row [$j] . '"'; } else { $return .= '""'; } if ($j < ($num_fields - 1)) { $return .= ','; } } if ($i 0) { $return .= "--\n-- Triggers for the table `$table`\n--\n\n"; // cycle through the triggers of the table foreach ( $triggers as $trigger ) { $result = mysqli_fetch_array ( mysqli_query ( $con, "SHOW CREATE TRIGGER $trigger" ) ); $return .= "\nDROP TRIGGER IF EXISTS `$trigger`;\n"; $return .= $result [2] . ";\n"; } $return .= "\n\n"; } } // save file $handle = fopen ( $backup_dir, 'w+' ); fwrite ( $handle, $return ); fclose ( $handle ); }Thank’s David it works..
Good blog post. I absolutely love this website.
Continue the good work!
ereg_replac is now deprecated how do I overcome this error for this line of script from the above code.
$row[$j] = ereg_replace("\n","\\n",$row[$j]);It may not work anymore. Default package is php 5.5.5 , mysql_* functions are removed.
Thank you for posting the script its very handy!
what would be useful is if the script in the post was updated with the bug fixes suggested in the comments
also putting an updated on X would help us know its been updated,
thanks again for the helpful script
Hi there ,
All some script :) .. I don’t know much and the coding but my hosting has stopped the mysqldump way of backup .. I used this to email ..
Could someone recode for me .. so it will be able to send the back up files to a email address ??
Thanks
Great script. Today I discovered the hard way that my ISP runs only weekly backups :-(. In few minutes I had my own backup running daily.
WOW just what I was looking for. Came here by searching for membatalkannya
@dave the script work perfectly, can’t it be possible to save to external device instead of saving to the server. anybody with nice suggestion?
100% Work!
very good.. this is what I searching for.
thankyou, David :)
The script does correctly export the file with UTF-8 data? it seems that no..
Here is my solution:
$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ''; $dbname = 'a_wp_test'; function backup_tables($host,$user,$pass,$name,$tables = '*') { $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); mysql_query("SET NAMES 'utf8'"); //get all of the tables if($tables == '*') { $tables = array(); $result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } $return=''; //cycle through foreach($tables as $table) { $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return.= 'DROP TABLE '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = str_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n\n\n"; } //save file $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); } backup_tables($dbhost,$dbuser,$dbpass,$dbname);hello sir!!! your script really saved me!! i’ve been searching for backup and restore scripts for weeks… and yours worked flawlessly.. i’ve made some modifications though, to make it work with mysqli..
can you help me with the restore script sir? our panelists insisted on a file upload-style restore database and tried various scripts i found on the internet, but so far not successful.
Thanks for the script! this works fine. First of all, is better backup also the structure of db (tables, field, and type and size for each field)
when i run this code i received an error on these lines
$result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result));Thanks Good script!!!
But it generate an error when i run the srcipt with large database which contain may be 2 lac data or more.this error is shown
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 133693567 bytes) in C:\wamp\www\Backup\index.php on line 62
what can i do…????
Thanks so much buddy.. you saved my day
The method above but very dark indeed. I have a problem when my database size greater than 800M An error notification:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 133 693 567 bytes)
I can not use mysqldump for database through ADOdb connected to a different server.
I have a solution using phpMyadmin but also not be too large because space.
Who artillery appropriate solution please just help!
Hello,
How do I change replace?
"CREATE TABLE" ===> "CREATE TABLE IF NOT EXISTS"query('SHOW CREATE TABLE '.$table)->fetch_row(); $return .= "\n".$row2[1].";\n"; $return .= "\n--\n" ; $return .= '-- Tablonun veri dökümü `' . $table . '`' . "\n" ; $return .= "--\n\n" ; ?>Hello to everyone,
How do I use this? “ORDER BY TABLE_NAME ASC”
Thanks
Nice script but I’m having some problems with it…
I’ve got a database with 11 tables, the backup-script only saves the last of the tables to the backup-file.
Why is this?
There is an error on line 45
$row[$j] = ereg_replace("\n","\\n",$row[$j]);The ereg_replace is deprecated in PHP5.4
Just fyi.. fixed that and the code works great! Many Thanks to you for the nice work!
Here is the MYSQLI solution:
$dbName = 'testdb'; $link = mysqli_connect('localhost', 'root', 'root', $dbName, (int)'3306', null); if (!$link) { echo mysqli_connect_error(); exit(1); } $query = 'SHOW TABLES'; $result = mysqli_query($link, $query); $tables = array(); $buffer = ''; while ($row = mysqli_fetch_row($result)) { $tables[] = $row[0]; } foreach ($tables as $table) { $howManyColumns = "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$dbName' AND table_name='$table';"; $executeHowManyColumns = mysqli_query($link, $howManyColumns); $numberOfColumns = mysqli_fetch_row($executeHowManyColumns)[0]; $creationQuery = "SHOW CREATE TABLE$table"; $executeCreationQuery = mysqli_query($link, $creationQuery); $createTableQuery = mysqli_fetch_row($executeCreationQuery)[1] . ';'; $buffer .= "\n$createTableQuery\n"; $tableContent = "SELECT * FROM$table"; $tableContentResult = mysqli_query($link, $tableContent); $rows = mysqli_data_seek($tableContentResult, 0); if (false !== $rows) { $buffer .= "\nINSERT INTO$tableVALUES\n"; } for ($i = 0; $i < $numberOfColumns; $i++) { while ($entry = mysqli_fetch_row($tableContentResult)) { $buffer .= '('; for ($j = 0; $j 0) { $buffer .= ','; } $buffer .= "'$element'"; } $buffer .= ");\n"; } } } $buffer = trim($buffer) . "\n";Sorry, I forgot to write the if statement:
if ($j > 0) { $buffer .= ','; } $buffer .= "'$element'";Hi all, if you are looking for a more advanced backup scheme, we have just developed web3backup; it’s a PHP script that backs up MySQL, SVN, local files with binary incremental backup, and rotates daily, weekly, monthly and yearly backups. You can get it at:
http://www.exteon.ro/en/products/programming-tools/web3backup
It gives error
Undefined variable: return in E:\web\xampp\htdocs\libmgmt\scripts\backupdb-Copy.php on line 37
while running the script
This script is unable to add foreign key constraints while importing in mysql.
Hence is not importing all the tables.
Awesome thanks for the script. For anyone having issues importing this sql after its saved. If your using foreign keys relationships you will want to list out the tables in this function:
Instead of using the star, otherwise they will write to the sql file in alphabetical order and when imported tables which rely on other tables will error out. If you list them out like ‘questions, answers’ it will create them in order fixing any missing foreign key errors.
This script may not work if database is relational database.
For Example:
I Used Following Script For Backup:
To fix the problem of encoding I used this:
This worked for me , I hope it will be useful for you guys
Hello, will this be also applicable if my database has Foreign Key? I encounter an error while restoring it. Thanks :)
I modified this code to created a script that can be run via cron, and can schedule different time for backing up diffrent databases.
http://anasthecoder.blogspot.ae/2015/10/phpautomateddbbackup.html
hello sir!!! your script really saved me!! i’ve been searching for backup and restore scripts for weeks… and yours worked flawlessly.. i’ve made some modifications though, to make it work with mysqli..
can you help me with the restore script sir? our panelists insisted on a file upload-style restore database and tried various scripts i found on the internet, but so far not successful.
http://www.mi-web.org/miembros/92256-tarotist/perfil
What about restore????
Saved my life :)
I made and tried out an up-to-date mysqli version of David scripts, with an HTML form to configure it.
https://github.com/ninuzzo/mysqlphpdump
Replace
$row[$j] = ereg_replace("\n","\\n",$row[$j]);with
$row[$j] = str_replace(array("\n","\r"),array('\\n','\\r'),$row[$j]);A) to catch
\rreturns from Windows. And B)str_replaceis slightly faster.Hi
I am looking for a database backup script that backups multiple databases with different credentials, if possible to do using PDO or mysqli, has anyone got a script or know of a php script that does this please
Thank you in advance
Ian
Thank you for such a nice script. I would suggest if we can change
DROP TABLEtoDROP TABLE IF EXISTto avoid error while uploading backedup database into blank database.Also could you please refresh code with replacing depricated functions like mysql and preg_replace?
Thanks again
Can you show KEY & PRIMARY key Backup code.
replace line 33 with this :
I know this is an old post, but it was very helpful to me. I did get the
ereg_replacedeprecated error.I replaced this:
$row[$j] = ereg_replace("\n","\\n",$row[$j]);with this:
$row[$j] = preg_replace("/\n/","\\n",$row[$j]);It works for me:) thank you very much. I modified end of file to gzip results:
this code works like a charm. More kudos to you david.
Why there is a dot with
$return.Can someone comment the complete and working update form of this code as soon as possible. Thanks so much in advance.
Updated for php7
/* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*'){ $link = mysqli_connect($host,$user,$pass,$name); //get all of the tables if($tables == '*'){ $tables = array(); $result = mysqli_query($link,'SHOW TABLES'); while($row = mysqli_fetch_row($result)){ $tables[] = $row[0]; } } else{ $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through foreach($tables as $table){ $result = mysqli_query($link,'SELECT * FROM '.$table); $num_fields = mysqli_num_fields($result); $return.= 'DROP TABLE '.$table.';'; $row2 = mysqli_fetch_row(mysqli_query($link,'SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysqli_fetch_row($result)){ $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j < $num_fields; $j++) { $jfromdb = $row[$j]; $jfromdb = addslashes($jfromdb); $jfromdb = preg_replace("/\n/","\\n",$jfromdb);//'/looking for/', 'replace with', $in this text if (isset($jfromdb)) { $return.= '"'.$jfromdb.'"' ; } else { $return.= '""'; } if ($j < ($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n\n\n"; } //save file $handle = fopen('dbbackups/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); }hi, how can execute the back up code?..is it a javascript to be added in a file or 2 seperate php file to back up
Having troubles with the blob data… Any help?.
The blob data doesn’t come in plain text.
Well.. this seems to be good piece of code but this will fail when the database size is good enough. Under such situation – the code tries to put the data in memory – wont be able to hold it long .. it will fail with out of memory exception.
Here is the code of mysqli supported:
$db_host = 'localhost'; $db_user = 'root'; $db_pass = ''; $db_name = 'your_database_name'; $file_path = 'backup/'.$db_name.'.sql'; backup_database($db_host, $db_user, $db_pass, $db_name, $file_path); function backup_database($host, $user, $pass, $name, $file_path) { $tables = '*'; $mysqli = new mysqli($host,$user,$pass,$name); if ($mysqli->connect_errno) { printf("Connect failed: %s\n", $mysqli->connect_error); } else { //get all of the tables if($tables == '*') { $tables = array(); $result = $mysqli->query('SHOW TABLES', MYSQLI_USE_RESULT); while($row = $result->fetch_array(MYSQLI_ASSOC)) { $key = @key($row); $tables[] = $row[$key]; } $result->close(); } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through $output = ''; foreach($tables as $table) { $insert_sql = ''; $result = $mysqli->query('SELECT * FROM '.$table, MYSQLI_USE_RESULT); $num_fields = $result->field_count; for ($i = 0; $i fetch_row()) { $insert_sql.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $j < $num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = str_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $insert_sql.= '"'.$row[$j].'"' ; } else { $insert_sql.= '""'; } if ($j close(); $output .= 'DROP TABLE IF EXISTS '.$table.';'; $result = $mysqli->query('SHOW CREATE TABLE '.$table, MYSQLI_USE_RESULT); $row = $result->fetch_row(); $result->close(); $output .= "\n\n".$row[1].";\n\n"; $output.= $insert_sql; $output.="\n\n\n"; } //save file $handle = fopen($file_path, 'w+'); fwrite($handle, $output); fclose($handle); } $mysqli->close(); }Hello,
What needs to be added to the code to back up all data structures of audit_trail?
Thank you
Hello,
How to include PDO and triggers in database backup
Hi! Thanks for the great post, very informative.