Backing up Database Using a Click Button

[gazzzzzza]

Here is some code I found which I use to backup the database. Seems to work well and is simple to use and configure. I put a button linking to it in my admin area (which was created seperately from the oscommerce admin). I had to find some code as I could not seem to get the oscommerce one to work when I transferred it over to my new admin area. The saved backup file is saved with athe filename indicating the date/time it was backed up which is also quite nice. Hopefully this may come in handy for someone. All that needs modifying is the backup director, and the connection details.

 

 

---- START CODE

 

<?php

 

// BACKUP DB

 

set_time_limit(0);

 

// Enter your MySQL access data

$host= 'xxxx';

$user= 'xxxx';

$pass= 'xxxx';

$db= 'xxxx';

 

 

/* CREATE THE BELOW DIRECTORY BEFORE RUNNING SCRIPT BECAUSE IT WILL NOT ERROR IF IT CANNOT FIND THE DIRECTORY, IT WILL JUST FAIL TO SAVE THE BACKUP */

 

$backupdir = 'backups';

 

// Compute day, month, year, hour and min.

$today = getdate();

$day = $today[mday];

if ($day < 10) {

$day = "0$day";

}

$month = $today[mon];

if ($month < 10) {

$month = "0$month";

}

$year = $today[year];

$hour = $today[hours];

$min = $today[minutes];

$sec = "00";

 

// Execute mysqldump command.

// It will produce a file named $db-$year$month$day-$hour$min.gz

// under $DOCUMENT_ROOT/$backupdir

// echo $DOCUMENT_ROOT; (web dir)

// BACKUP DIR MUST EXIST BEFORE RUN CODE - NO ERRORS ARE DISPLAYED

 

system(sprintf(

'mysqldump --opt -h %s -u %s -p%s %s | gzip > %s/%s/%s-%s%s%s-%s%s.gz',

$host,

$user,

$pass,

$db,

getenv('DOCUMENT_ROOT'),

$backupdir,

$db,

$year,

$month,

$day,

$hour,

$min

));

// echo '+DONE';

 

?>

 

 

----- END CODE

[fredfillah]

I schedule my database backups using the shell script below and cron (available on UNIX servers).

 

#!/bin/bash
# filename: ~/scripts/backup_db.sh

export FILE=db_backup_weekly-`date +%Y%m%d`

mysqldump -u <mysql_username> -p'<password>' <db_name> ~/www/<admin_dir>/backups/$FILE.sql
gzip ~/www/<admin_dir>/backups/$FILE.sql

 

Here's the cron entry

0 1 * * 1 ~/scripts/backup_db.sh
50 1 * * 1  find ~/www/<admin_dir>/backups -mtime +30 -exec rm -f {} \;

 

You will have to substitute your info for the terms in <>. This will backup the DB once/week and remove files that are older than 30 days.

 

-Fred