Backup mysql database with php and zip it

By | May 1, 2009

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";
}
About Silver Moon

A Tech Enthusiast, Blogger, Linux Fan and a Software Developer. Writes about Computer hardware, Linux and Open Source software and coding in Python, Php and Javascript. He can be reached at [email protected].

Leave a Reply

Your email address will not be published. Required fields are marked *