Page 1 of 1

Cron job - backup DB

Posted: Tue Aug 24, 2004 4:02 am
by fresh
Hey, I have been reading a little about cron jobs and setting them up to do some useful things, but none have covered the concept of backing up a DB.. I know how to setup the server to run the PHP page at specific times, but the exporting to my local drive part has went over my head lol :)

thanks in advance ;)

Posted: Tue Aug 24, 2004 9:41 am
by pickle
You don't need PHP to backup your database (probably). MySQL has a program called [mysql_man]mysqldump[/mysql_man] that can be used to backup a database. I'm not 100% sure on the syntax, but it's something like this:

Code: Select all

> mysqldump -u user_with_access -p database_name > file_to_dump_to.sql
It will ask for your password, and then start backing up. If you just want the structure and not the data, use a "-d" flag.

Posted: Tue Aug 24, 2004 11:17 pm
by fresh
yeah but will it still need to be manually?

Posted: Wed Aug 25, 2004 1:47 am
by m3mn0n
Not if you turn that into a PHP program and cron it. ;)

Posted: Wed Aug 25, 2004 7:17 am
by fresh
hey, I found this, hows this look?

Code: Select all

<?php
  // Enter your MySQL access data
  $host= 'dbhost';
  $user= 'dbuser';               
  $pass= 'dbpassword';
  $db=   'db';
  $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
  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';
?>

Posted: Wed Aug 25, 2004 10:32 am
by m3mn0n
Like a bunch of PHP code. :)

Posted: Wed Aug 25, 2004 12:28 pm
by fresh
lol.. i meant did it look safe? I don't want to delete my DB or anything bad like that.. 8O