Here’s a great little script that will back-up your database to a zipped .gz file and store it in a folder on your server….

<?php

  // Set Vars
  $dbname = 'DATABASE_NAME';
  $dbhost = 'localhost';
  $dbuser = 'USER_NAME';
  $dbpass = 'PASS_WORD';

  // Connect to DB
  $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
  mysql_select_db($dbname);

  // Back-up DB
  $backupFile = 'db-backups/myBackUp_'.date("G-i_d-m-Y").'.gz';
  $command = "mysqldump --opt -h $dbhost -u$dbuser -p$dbpass $dbname | gzip > $backupFile";
  system($command);

  // Close connection
  mysql_close($conn);

?>

Note that the variable $backupFile specifies the path where the file is to be stored as well as the actual filename. Also the date format is set to time and date, but if it was just set to ‘day’, then the following week, it’d replace the file allowing you to keep 7 days of back-ups to save server space… The same is true if you used the ‘day number’, i.e. 20 for the 20th of the month. You’d end up with a folder with 31 files allowing you to store back-ups up to a month old….

The credit goes to www.php-mysql-tutorial.com