27.16. How to backup your MySQL database using only the browser

You want to do regular dumps of your MySQL database, but you are lazy and would like to automate the task.

If your PHP installation permits execution of commands through system() (e.g. you are not running in safe mode, or, if you are, safe_mode_exec_dir contains all the commands you need), you can trigger a backup of your MySQL database, just by pointing your browser to the following script (see How to backup your MySQL database using only the browser):

<?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';
?>

Upload it to your backup directory (see code) and just reload it with your browser each time you want to do a backup.

	Use cron on the client, if it is not available on the server
	If you are not allowed cron access on the web server (to start mysqldump directly there), you can set up your own cron job (on your home system!) to periodically call the above script.

	Poor man's cron
	If you don't have cron, or a similar functionality on your system, you can still modify the above script to inform the browser to reget the file every xxx hours. A poor man's cron, so to say.

	Protect your backup directory!
	Of course, the backup directory should at least be protected with a .htaccess file.

	Beware of the PHP execution limit!
	If your PHP has some timeout limit set (often 30 sec), you will not be able to dump really large databases (larger than, say, 2-5MB) through this method. It is the same limitation, as with the phpMyAdmin DB backup option. The only alternative you have in this case is telnet or ssh access, see Section 26.2.2.

Note that phpMyAdmin (Section 3.3) also offers an excellent backup capability through the browser, with many options, like compression, structure and data backups, various backup file formats etc. However, you cannot automate backup creation with phpMyAdmin.