Backup Your MySQL Database Using PHP

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.

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!

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…

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++)
{

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:

$return.= ‘DROP TABLE IF EXISTS ‘.$table.’;';

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, & @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))) ? ?

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?

i am very happy to find this code .it relay work fine and very few time to take a backup the database
thanks

Hi.

As everyone, thanks for this simple script. I got it up and running in a matter of seconds.

I plan on doing a daily backup. Is there a way you can automaticaly delete so that there is only 3 or 4 backups in the folder at a time – don’t want it to get to the point where I forget and there are hundres in there !!

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.

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?

for ($i = 0; $i < $num_fields; $i++)

On line 41 you have again the same loop:

for($j=0; $j<$num_fields; $j++)

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?