How to Create Backups of MySQL via PHP

Posted on November 10, 2018 at 6:47 pm

If you website is hosted on a web hosting like OVH, you should make sure to periodically backup your MySQL database as additional precaution, just in case the automated backup offered by the hosting provider fails. With OVH, for example, you can create cronjobs that execute PHP files located in your FTP home directory, here are a few PHP files I created:

Use this PHP script to create backups of a MySQL database:

$username = 'username';
$password = 'password';
$hostname = 'localhost';
$database = 'dbname';
 
$backupFile = '/home/user/mysql-backups/'.date("Y-m-d-H-i-s").$database.'.sql.gz';
 
$start = microtime(true);
 
echo "Starting MySQL backup to ".htmlspecialchars($backupfile)."...<br />";
 
if ( file_exists($backupfile) ) unlink($backupfile);
 
$command = 'mysqldump -u "'.$username.'" -p"'.$password.'" -h "'.$hostname.'" '.$database.' | gzip -9 > '.$backupfile.' 2>&1';
 
system($command, $result);
 
if ( file_exists($backupfile) )
{
	echo "Created file ".htmlspecialchars($backupfile)." (".number_format(filesize($backupfile))." bytes)...<br />";
}
else
{
	echo "Cannot find file ".htmlspecialchars($backupfile)."...<br />"; 
}
 
$time_elapsed = microtime(true) - $start;
 
echo "Backup completed in ".sprintf('%02d:%02d:%02d', (round($time_elapsed)/3600),(round($time_elapsed)/60%60), round($time_elapsed)%60)."...<br />";

Just add a task in crontab to run it every day.

Then you can use this PHP script to cleanup old backup files:

echo "Deleting *.gz files older than 2 weeks...<br />";
 
$path = '/home/user/mysql-backups/';
 
$now = time();
 
$files = glob($path."*.gz");
 
foreach ($files as $file) {
    if (is_file($file)) {
      if ($now - filemtime($file) >= 60 * 60 * 24 * 15) { // 15 days
	    echo "Deleting ".htmlspecialchars($file)."...<br />";
        unlink($file);
      }
    }
  }
 
echo "Operation completed!<br />";

Again, add another task in crontab to run it every day.

Updated on November 12, 2018 at 10:30 am

Receive updates via email

Other Posts

Updated Posts