GZip MySQL Database Backup with PHP and Cron

By: McSwindler

I know that I need to backup my database every once and a while, but I’m lazy and forgetful. I end up backing up maybe 4 or 5 times a year. Ideally, I want to backup every month at least. The bad news is, my host (1&1) doesn’t have any automatic backup, so I had to make my own. I tried Googling, but didn’t find anything that really fit my need.

I did find a script by David Walsh that almost worked. I used most of the original script, but had to remodel it a bit to fit my needs.

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
<?php
//usage: $ usr/bin/php dbBackup.php dbname
//get db info
switch($argv[1]){
    case 'dbname':
        $host = 'lol.cake.com';
        $user = 'user';
        $pass = 'password';
        $db = 'name';
        $backupFile = 'dir/prefix_'.date('m-d-Y').'.sql';
        break;
    default:
        exit(0);
        break;
}

//connect to db
$con = mysql_connect($host, $user, $pass);
mysql_select_db($db, $con);

//get all the tables
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
    $tables[] = $row[0];


//cycle through tables
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 = gzopen($backupFile.'.gz','wb9');
gzwrite($handle,$return);
gzclose($handle);

//close
mysql_close($con);
?>

The script is simple enough to setup and supports multiple db setups. Simply add new cases for each database. Each case is determined by a short keyword which comes from the args on the command line, but can easily be changed to a GET or POST variable. Within each case is the database info and the file you want to save the backup to. Important: You do NOT need to add ‘.gz’ to the filename, it is added at the bottom when the gzip stream is opened.

Now to actually get the script running automatically you have to add a cron entry, you will need SSH access of course. Simple enough, you just need to full path to the script from root (cd to the dir of the script and type pwd), and you need the path to php (usually usr/bin/php or use which php). Add a new entry to cron like the following.

0 0 1 * * /usr/bin/php /htdocs/dbBackup.php dbname

This entry will run on the first of every month at 00:00 (midnight), for multiple databases you will need to add a new entry for each only changing the ‘dbname’ short keyword which you set in the switch cases. Now, you are all set to get backups each month like me, enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
Δ
Back to Top