Backup mysql database with php and zip it

Many php applications prefer to backup the mysql database from within the application and save it as an archive.


The mysqldump commandline utility can be used to perform this function of backing up a mysql database as sql file.

The command would be like this :

mysqldump --user=$username --password=$password --opt $db_name > $dir/backup.sql

The above command shall dump the database named $db_name into a sql file in directory $dir. This sql file shall be next zipped using php's Ziparchive class.

In the above example backups/backup.sql is the path to the sql file. Next this backup.sql file is zipped using the ZipArchive library class of php.

Code :

#Function to backup database to a zip file
function backup() 
{
  $suffix = time();
  #Execute the command to create backup sql file
  exec("mysqldump --user={$username} --password={$password} --quick --add-drop-table --add-locks --extended-insert --lock-tables --all {$db} > backups/backup.sql");
  
  #Now zip that file
  $zip = new ZipArchive();
  $filename = "backups/backup-$suffix.zip";
  if ($zip->open($filename, ZIPARCHIVE::CREATE) !== TRUE) {
   exit("cannot open <$filename>n");
  }
  $zip->addFile("backups/backup.sql" , "backup.sql");
  $zip->close();
  #Now delete the .sql file without any warning
  @unlink("backups/backup.sql");
  #Return the path to the zip backup file
  return "backups/backup-$suffix.zip";
} 







Last Updated On : 1st May 2009

Subscribe to get updates delivered to your inbox

Leave a comment