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!
Comments
Be Heard!
Share your thoughts without being a jerk! And wrap your code in <code> tags, f00!
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.
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.
@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 happend with the good old mysqldump command? Even gzipped for saving bandwith or diskspace.
And in PHP code:
<?php
$output = `mysqldump –add-drop-table database-name | gzip > db.sql.gz`;
?>
Or with pecific tables:
<?php
$output = `mysqldump –add-drop-table database-name table1,table2,table3 | gzip > db.sql.gz`;
?>
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?
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 “for” loop
I 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.
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!!!
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.
CREATE TABLE `backup` (
`id` tinyint(10) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
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
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:
for($j=0; $j<$num_fields; $j++)
It should say
$columns = count($row);
for($j=0; $j<$columns; $j++)
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 after
mysql_select_db($name,$link);
this way you dont get a notice of undefind return
I 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 all
Great 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
16 while($row = mysql_fetch_row($result))
30 $num_fields = mysql_num_fields($result)
38 while($row = mysql_fetch_row($result)
Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource
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.= '""'; }
if ($j
window.close();
Hie, the code is giving me syntax error on window.close();
How to prompt to save the file instead into server?
I use your script … but i have a problem.
I have a table that has a unique cell so i get the backup file right like this
`userEMAIL` varchar(255) DEFAULT NULL,
…
UNIQUE KEY `userEMAIL` (`userEMAIL`)
and data like
INSERT INTO serial VALUES(“44″,”",”",”1″,”0″,”",”0″,”0″,”0″,”0″);
The key is the third one .. “”
When i import the sql with ..phpMyAdmin i get that is a duplicate entry and it stops..Any idea how to fix this?
… 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,
I’ve added transaction lines to the script and DROP TABLE IF EXISTS which prevents from getting errors after importing the dump into an empty database.Here it is:
backup_tables($mysql_serwer,$mysql_uzytkownik,$mysql_haslo,$mysql_baza);
/* 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 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
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_row always returns false.
But this “for” is 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.”;”);
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";
}
}</code
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 ($j
Actually 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*/
Sorry
//------------ /* 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";
$rowX = mysql_fetch_row(mysql_query("SHOW CREATE PROCEDURE `$proc`"));
$return.= "\n".$rowX[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() to preg_replace() should cut the mustard, just don’t use ereg_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
[...] account. Therefore, it’s sometimes necessary to use a PHP script to back up a MySQL database. David Walsh has done a nice job of putting together a PHP script to backup your MySQL databases. Below is a [...]
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:
$return.= 'DROP TABLE '.$table.';';(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.
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);
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:
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{
$aarr[$j] = addslashes($row[$j]);
$aarr[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);
$return.= '"'.$row[$j].'"' ;
}
Thanks to David and all other Contributors
Marcus
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