Database backup script

[nca]

I cannot get this php script to work. Can anyone tell me what is wrong with it?

<?
include("logininfo.txt");
mysql_connect($SERVER,$USER,$PASSWORD);
mysql_selectdb($DATABASE);
$y=date('Ymd');
$fpath = "/www/sw/$y.sql";
mysql_query("mysqldump --quick -h$SERVER -u$USER -p$PASSWORD $DATABASE > $fpath");
?>

[intenz]

mysql_select_db, not mysql_selectdb

[nca]

Ok, I made that change, and it still will not work. Any other ideas??

[nca]

anyone??? 😕

[ravegti]

does it give you an error of any sort? a parse error on line _??

[Weedpacket]

Could it be perhaps because "mysqldump..." is a system command, not a SQL query?

[nca]

Could I make it a query? Or how can I have php execute a system command?

[goa103]

It depends of your host, some hosts don't allow system calls. The solution would be to build a query from the data, like PhpMyAdmin. However I am not sure about it as I am also looking for a solution to backup my db.

[jplush76]

here is a backup script I use to back up daily, weekly and monthly. hope it helps!

#!/usr/local/bin/php
<?php

/**
* Date: 2/7/2002
*
* This script will backup  a database on a daily,
* weekly and monthly basis.  This script will keep only
* 7 daily backups, 4 weekly backups and 12 monthly backups
*
*/

// This directory you specify below expects the following subdirectories: daily/, weekly/, monthly/
$backup_dir = '/usr/home/jim/backups/';

// MySQL Acccount info. 
// mysql info being viewed.
$mysqlhost = 'localhost';
$mysqluser = 'root';
$mysqlpass = 'rootpasshere';

// The database you want to backup 
$database = 'db1';

// path to mysqldump
$mysqldump = '/usr/local/bin/mysqldump';

// Change this to point to the location of your tar
$tar_cmd = '/usr/bin/tar';

/** You should not have to touch anything below here **/

/***********************
* DAILY BACKUP
***********************/
$backup_file = date('mdY') . '_daily_backup.sql';

$old_backups = getOldBackups($backup_dir . 'daily/');

// Sort results and remove oldest backup
if (sizeof($old_backups) > 6) {
    rsort($old_backups);
    $oldest_backup = array_pop($old_backups);
    unlink($backup_dir . "daily/$oldest_backup");
}

// Dump the database
exec("$mysqldump -h$mysqlhost -u$mysqluser -p$mysqlpass $database > " . $backup_dir . 'daily/' . $backup_file);
exec("$tar_cmd --directory $backup_dir" . "daily/ -czf $backup_dir" . "daily/$backup_file" . '.tar.gz ' . $backup_file);
unlink($backup_dir . 'daily/' . $backup_file);

/***********************
* WEEKLY BACKUP
***********************/
// If at end of week do weekly backup
if (date('w') == '0') {
    if ($old_backups = getOldBackups($backup_dir . 'weekly/')) {
        if (sizeof($old_backups) > 3) {
            rsort($old_backups);
            $oldest_backup = array_pop($old_backups);
            unlink($backup_dir . "weekly/$oldest_backup");
        }
    }
    copy($backup_dir . 'daily/' . $backup_file . '.tar.gz' , $backup_dir . 'weekly/' . str_replace('daily','weekly',$backup_file) . '.tar.gz');
}

/***********************
* WEEKLY BACKUP
***********************/
// If at end of month do monthly backup
if (date('d') == '01') {
    if ($old_backups = getOldBackups($backup_dir . 'monthly/')) {
        if (sizeof($old_backups) == 12) {
            rsort($old_backups);
            $oldest_backup = array_pop($old_backups);
            unlink($backup_dir . "monthly/$oldest_backup");
        }
    }
    copy($backup_dir . 'daily/' . $backup_file . '.tar.gz', $backup_dir . 'monthly/' . str_replace('daily','monthly',$backup_file) . '.tar.gz');
}

/**
* This simply gets all the files in an directory and loads the
* file names into an array
*
*/
function getOldBackups($dir_path)
{
    if (!is_dir($dir_path)) return false;

$fd = opendir($dir_path);
$old_backups = array();
$index = 1;
while (($f = @readdir($fd)) == TRUE) {
    if (!is_dir($f)) {
        clearstatcache();
        $old_backups[$index] = $f;
        $index++;
    }
}
closedir($fd);

return $old_backups;
}

?>

now you can just go to the unix command line:

crontab -e

then

0 5 * lynx -dump http://www.yoursite.com/your_script.php

and boom, every morning at 5am your database will backup compressed 🙂