PHP database backup

Tags: , , ,

I recently added in a way for admin users to download a backup of the database to a CMS I work on.  I came up with something between this solution and this one.  Add this little snippet after a database connection and change “table_1″ and “table_2″ to whichever tables you’d like to output, and add as many tables as you need.

What I like about this script is that it doesn’t save a copy to the server so folks can’t nab it if they know the entire URL, but it can be easily integrated to a web based interface.  It also uses “create table if not exists” which was missing from the other two scripts.  I think a backup should create tables in case you loose the table structure, but it shouldn’t delete the existing data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php
function datadump ($table) {
    $result .= "# Dump of $table \n";
    $result .= "# Dump DATE : " . date("d-M-Y") ."\n\n";
    
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $result .= "\n\n".str_replace('CREATE TABLE','CREATE TABLE IF NOT EXISTS',$row2[1]).";\n\n";
    
    $query = mysql_query("select * from $table");
    $num_fields = @mysql_num_fields($query);
    $numrow = mysql_num_rows($query);
    
    for ($i =0; $i<$numrow; $i++) {
        while($row = mysql_fetch_row($query)) {
            $result .= "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])) $result .= "\"$row[$j]\"" ; else $result .= "\"\"";
                if ($j<($num_fields-1)) $result .= ",";
            }
            $result .= ");\n";
        }
    }
    return $result . "\n\n\n";
}
 
$content = datadump('table_1').datadump('table_2');
 
$file_name = "database_backup.sql";
Header("Content-type: application/octet-stream");
Header("Content-Disposition: attachment; filename=$file_name");
echo $content;
?>

Permalink » 3 Comments

Cloud Backup

Tags: , , , , ,

I’ve been looking into cloud storage recently and decided to list out the prices and pros and cons of the more well known cloud storage services out there today to help me make my choice.

A few weeks after I started poking around I received a newsletter from Dreamhost letting me know they give out 50GB of free backup space to all their customers.  You can also add on more space for .10/GB with no charges for transferring files…so that’s who I’m going with 😛  But I thought I might as well finish up my reviews.

Drop Box
20$/m for 100GB
Pros: It has a web interface and a desktop program, you can share files with multiple people, and has a plugin for QNAP drives
Cons: Pretty expensive and has a 100GB limit

Amazon S3
9.30-13$/m for 100 GB
Pros: Only pay for what you use, pretty decent prices, can be used to serve files for your website, and has a lot of ways to access your data
Cons: You have to pay for transferring files which could add up

Carbonite
$5/mo for unlimited
Pros: Pretty cheap and unlimited
Cons: Price is per computer, so if you have a lot of them it can add up and you won’t be able to share your files.  there’s no free version so you can’t really get a feel for it before you buy.  I’m not a big fan of trial memberships.

Mozy
$5/mo for unlimited
Pros: Cheap & unlimited
Cons:  I looked at the free account and I didn’t like the interface too much, seems like the only way to access your files if from installing a program on your computer

I’m leaning towards Amazon S3, it’s cheap and can be accessed from anywhere and could be used for servicing files for a website as well so it’s pretty flexible with what you can do with it.  I worry about racking up some fees with file transfers though.

I also like Dropbox since it’s user friendly and there’s no surprise fees involved, but it’s pretty expensive and can only go up to 100GB.  You can get more space from them for referrals, but I don’t see that helping me too much.

Permalink » No comments

Backup URL

Tags:

Backup URL is a web service that’s similar to the wayback machine.  It will “create a copy of any website that you can share and view any time knowing it will last forever.” All you need to do is input the URL of any page and it will make a backup of the HTML and give you a link to hold onto.  I tried it out with contropa.com and the result was spot on. But like the wayback machine it doesn’t save the images…so it’s not truly a copy that will last forever…  Once the publisher deletes the images the website they’ll be deleted from Backup URL as well.  Although, if you’re looking on keeping a copy of articles then that wouldn’t really matter :)

http://backupurl.com/

Permalink » No comments