Backup MySQL using PHP

A PHP script for backing up your MySQL databases. Can easily be added to a cron job to have scheduled database backups made.

The PHP script below is based on the Back up Mysql database via PHP snippet found on DZone.com, but modified to suit my needs for versioning rather than creating a new filename every day.

<?php
// Extra versions to keep
define('VERSIONS', 14);

// Backup directory name
define('BACKUP_DIR', 'db_backup' ) ; 

// Database Credentials
define('HOST', 'mysql_server_hostname' ) ; 
define('USER', 'mysql_user_name' ) ; 
define('PASSWORD', 'mysql_password' ) ; 
define('DB_NAME', 'mysql_database_name' ) ; 



###########################  
//END  OF  CONFIGURATIONS  
###########################
// Set execution time limit
if(function_exists('max_execution_time'))
{
	if( ini_get('max_execution_time') > 0 )
	{
		set_time_limit(0) ;
	}
}


// Check if directory is already created and has the proper permissions
if (!file_exists(BACKUP_DIR)) mkdir(BACKUP_DIR , 0700) ;
if (!is_writable(BACKUP_DIR)) chmod(BACKUP_DIR , 0700) ; 

// Create an ".htaccess" file , it will restrict direct accss to the backup-directory . 
$content = 'deny from all' ; 
$file = new SplFileObject(BACKUP_DIR . '/.htaccess', "w") ;
$file->fwrite($content) ;

$mysqli = new mysqli(HOST , USER , PASSWORD , DB_NAME) ;
if (mysqli_connect_errno())
{
   printf("Connect failed: %s", mysqli_connect_error());
   exit();
}

// Introduction information
$return .= "--\n";
$return .= "-- A Mysql Backup System \n";
$return .= "--\n";
$return .= '-- Export created: ' . date("Y/m/d") . ' on ' . date("h:i") . "\n\n\n";
$return .= "--\n";
$return .= "-- Database : " . DB_NAME . "\n";
$return .= "--\n";
$return .= "-- --------------------------------------------------\n";
$return .= "-- ---------------------------------------------------\n";
$return .= 'SET AUTOCOMMIT = 0 ;' ."\n" ;
$return .= 'SET FOREIGN_KEY_CHECKS=0 ;' ."\n" ;
$tables = array() ; 

// Exploring what tables this database has
$result = $mysqli->query('SHOW TABLES' ) ; 

// Cycle through "$result" and put content into an array
while ($row = $result->fetch_row()) 
{
	$tables[] = $row[0] ;
}

// Cycle through each  table
foreach($tables as $table)
{
	// Get content of each table
	$result = $mysqli->query('SELECT * FROM '. $table) ; 
	
	// Get number of fields (columns) of each table
	$num_fields = $mysqli->field_count  ;
	
	// Add table information
	$return .= "--\n" ;
	$return .= '-- Tabel structure for table `' . $table . '`' . "\n" ;
	$return .= "--\n" ;
	$return.= 'DROP TABLE  IF EXISTS `'.$table.'`;' . "\n" ; 
	
	// Get the table-shema
	$shema = $mysqli->query('SHOW CREATE TABLE '.$table) ;
	
	// Extract table shema 
	$tableshema = $shema->fetch_row() ; 
	
	// Append table-shema into code
	$return.= $tableshema[1].";" . "\n\n" ; 
	
	// Cycle through each table-row
	while($rowdata = $result->fetch_row()) 
	{ 
		// Prepare code that will insert data into table 
		$return .= 'INSERT INTO `'.$table .'`  VALUES ( '  ;
		
		// Extract data of each row 
		for($i=0; $i<$num_fields; $i++)
		{
			$return .= '"'.$rowdata[$i] . "\"," ;
		}
		
		// Let's remove the last comma 
		$return = substr("$return", 0, -1) ; 
		$return .= ");" ."\n" ;
	}
	$return .= "\n\n" ; 
}

// Close the connection
$mysqli->close() ;

$return .= 'SET FOREIGN_KEY_CHECKS = 1 ; '  . "\n" ; 
$return .= 'COMMIT ; '  . "\n" ;
$return .= 'SET AUTOCOMMIT = 1 ; ' . "\n"  ; 



$fileName = DB_NAME ;


// Moving old backup versions
$j = VERSIONS;
while ($j > 0)
{
	if ($j == VERSIONS)
	{
		$fileNameOldest = BACKUP_DIR . '/' . $fileName . '_v' . $j . '.zip' ;
		// if oldest version exist, delete the file
		if (file_exists($fileNameOldest)) unlink($fileNameOldest);
	}
	else
	{
		if ($j == 1)
		{
			$fileNameFrom = (BACKUP_DIR . '/' . $fileName . '.zip') ;
		}
		else
		{
			$fileNameFrom = (BACKUP_DIR . '/' . $fileName . '_v' . $j . '.zip') ;
		}
		$fileNameTo   = (BACKUP_DIR . '/' . $fileName . '_v' . ($j + 1) . '.zip') ;
		if (file_exists($fileNameFrom)) rename($fileNameFrom, $fileNameTo);
	}
	$j--;
}


//$file = file_put_contents($fileName , $return) ; 
$zip = new ZipArchive() ;
$resOpen = $zip->open(BACKUP_DIR . '/' .$fileName.".zip" , ZIPARCHIVE::CREATE) ;
if( $resOpen )
{
	$zip->addFromString( $fileName , "$return" ) ;
}
$zip->close() ;
$fileSize = get_file_size_unit(filesize(BACKUP_DIR . "/". $fileName . '.zip')) ; 

$message = <<<msg
<html>
	<head>
		<title>DB BACKUP</title>
	</head>
	<body>
		<h1>BACKUP completed</h1>
		<p>
			Database backup has completed.<br/>
			The backup is stored in ZIP archive: <b>  $fileName.zip  </b> ($fileSize)
		</p>
		<p>
			This zip archive can't be accessed via a web browser, as it's stored into a protected directory.
		</p>
		<p>
			It's highly recomended to transfer this backup to another filesystem , use your favorite FTP client to download the archieve.
		</p>
	</body>
</html>
msg;
echo $message ; 

// Function to append proper Unit after file-size . 
function get_file_size_unit($file_size)
{
	switch (true)
	{
		case ($file_size/1024 < 1) :
			return intval($file_size ) ." Bytes" ;
			break;
		case ($file_size/1024 >= 1 && $file_size/(1024*1024) < 1)  :
			return intval($file_size/1024) ." KB" ;
			break;
		default:
		return intval($file_size/(1024*1024)) ." MB" ;
	}
}