jump to navigation

A PHP Script to Backup Mysql Databases (for Shared Hosted Websites) December 9, 2012

Posted by Tournas Dimitrios in PHP.
trackback

Backup should be an essential part of our web-administration tasks , there are too many stories of people who have lost all of their files due to system crashes . We shouldn’t fall into the old paradigm of “it will never happen to me.” or “my web-host handles these kind of tasks for me” or “I don’t know how to backup data” . There is no reason not to implement our own backup-strategy , even if our web-host claims to have the most advanced computer systems that prevent any disaster . This multi-part article will present simple practical code-examples to carry out our own backup’s . This first part handles our database files , I have chosen MySQL as an example , as this is the most used Database in use today (especially on shared hosts) . MySQL has  excellent tools for backups ,  but for security reasons , shared hosts disable directly access to these tools and offer only a graphical interface (for instance , PHPMyAdmin ) as an alternative solution . This multi-part article  uses “native” PHP functions which are part of every PHP installation (common modules like : SPL  , Zip , Curl and mysqli )  .

Prerequisites : Actually there are no special requirements to understand the logic of the following PHP-script . The reader should already have a basic knowledge of a FTP-clients (to upload the script into the server) and he/she should know how to find information  on PHP’s official manual .

A quick view how the script is structured :

  • The configuration section : Configure your own Database credentials and customize the name of the stored archive .
  • createNewArchive function : Creates an archive of a Mysql database
  • getFileSizeUnit function : gets an integer value and returns a proper Unit (Bytes , KB , MB)
  • getNameOfLastArchieve function: Scans the “BackupDir” and returns the name of last created Archive
  • allowCreateNewArchive function  : Compares two time-stamps (Yesterday , lastArchive) . Returns “TRUE” , If the latest Archive is older than 24Hours .

The script :

<?php

#####################
//CONFIGURATIONS
#####################
// Define the name of the backup directory
define('BACKUP_DIR', './myBackups' ) ;
// Define  Database Credentials
define('HOST', 'localhost' ) ;
define('USER', 'root' ) ;
define('PASSWORD', '' ) ;
define('DB_NAME', 'test' ) ;
/*
Define the filename for the Archive
If you plan to upload the  file to Amazon's S3 service , use only lower-case letters .
Watever follows the "&" character should be kept as is , it designates a timestamp , which will be used by the script .
*/
$archiveName = 'mysqlbackup--' . date('d-m-Y') . '@'.date('h.i.s').'&'.microtime(true) . '.sql' ;
// Set execution time limit
if(function_exists('max_execution_time')) {
if( ini_get('max_execution_time') > 0 ) 	set_time_limit(0) ;
}

//END  OF  CONFIGURATIONS

/*
 Create backupDir (if it's not yet created ) , with proper permissions .
 Create a ".htaccess" file to restrict web-access
*/
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 access to the backup-directory .
$content = 'deny from all' ;
$file = new SplFileObject(BACKUP_DIR . '/.htaccess', "w") ;
$written = $file->fwrite($content) ;
// Verify that ".htaccess" is written , if not , die the script
if($written <13) die("Could not create a \".htaccess\" file , Backup task canceled")  ;
// Check timestamp of the latest Archive . If older than 24Hour , Create a new Archive
$lastArchive = getNameOfLastArchieve(BACKUP_DIR)  ;
$timestampOfLatestArchive =  substr(ltrim((stristr($lastArchive , '&')) , '&') , 0 , -8)  ;
if (allowCreateNewArchive($timestampOfLatestArchive)) {
// Create a new Archive
createNewArchive($archiveName) ;
} else {
echo 'Sorry the latest Archive is not older than 24Hours , try a few hours later '  ;
}

###########################
// DEFINING  THE FOUR  FUNCTIONS
// 1) createNewArchive : Creates an archive of a Mysql database
// 2) getFileSizeUnit  : gets an integer value and returns a proper Unit (Bytes , KB , MB)
// 3) getNameOfLastArchieve : Scans the "BackupDir" and returns the name of last created Archive
// 4) allowCreateNewArchive : Compares two timestamps (Yesterday , lastArchive) . Returns "TRUE" , If the latest Archive is onlder than 24Hours .
###########################
// Function createNewArchive
function createNewArchive($archiveName){
$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"  ;
//$file = file_put_contents($archiveName , $return) ;
$zip = new ZipArchive() ;
$resOpen = $zip->open(BACKUP_DIR . '/' .$archiveName.".zip" , ZIPARCHIVE::CREATE) ;
if( $resOpen ){
$zip->addFromString( $archiveName , "$return" ) ;
    }
$zip->close() ;
$fileSize = getFileSizeUnit(filesize(BACKUP_DIR . "/". $archiveName . '.zip')) ;
$message = <<<msg
  <h2>BACKUP  completed ,</h2><br>
  the archive has the name of  : <b>  $archiveName  </b> and it's file-size is :   $fileSize  .

 This zip archive can't be accessed via a web browser , as it's stored into a protected directory .

  It's highly recomended to transfer this backup to another filesystem , use your favorite FTP client to download the archieve .
msg;
echo $message ;
} // End of function creatNewArchive

// Function to append proper Unit after a file-size .
function getFileSizeUnit($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 round(($file_size/1024) , 2) ." KB" ;
        break;
	default:
	return round($file_size/(1024*1024) , 2) ." MB" ;
}
} // End of Function getFileSizeUnit

// Funciton getNameOfLastArchieve
function getNameOfLastArchieve($backupDir) {
$allArchieves = array()  ;
$iterator = new DirectoryIterator($backupDir) ;
foreach ($iterator as $fileInfo) {
   if (!$fileInfo->isDir() && $fileInfo->getExtension() === 'zip') {
        $allArchieves[] = $fileInfo->getFilename() ;

    }
}
	return  end($allArchieves) ;
} // End of Function getNameOfLastArchieve

// Function allowCreateNewArchive
function allowCreateNewArchive($timestampOfLatestArchive , $timestamp = 24) {
	$yesterday =  mktime() - $timestamp*3600 ;
	return ($yesterday >= $timestampOfLatestArchive) ? true : false ;
} // End of Function allowCreateNewArchive

Final thoughts :

Upload the script into a publicly available directory and access it via a browser (for instance http://www.example.com/mybackup.php) . Prior the backup , it will check the time elapsed from the last backup and cancel the process if it’s lesser that 24-Hours . The compressed Archive (zip) is stored into the “myBackups” directory and protected from public access with a “.htaccess” file . A good security measure would be to transfer the archive to another file-system . A later part  of this article  will present a practical example that transfers the archive to an online-backup service ( Amazon’s S3 or DropBox) .

Comments»

1. cara - December 10, 2012

you continually amazing me with amount of content you turn out without sacrificing quality.

2. Lindinha - December 11, 2012

please keep this blog always updated with your great information and ideas.

3. cinthia - January 4, 2013

thanks for such a great post and the review, i am totally impressed!

4. christian - January 7, 2013

thank you for your tips! useful for me

5. Ryan - December 4, 2013

i Have an error on this line of code

“$tableshema = $shema->fetch_row() ;”

and this was the error mesage:
Fatal error: Call to a member function fetch_row() on a non-object

hope for you response thanks, and God Bless

tournasdimitrios1 - December 4, 2013

@Ryan
The script was checked on Windows (wamp) and Linux (CentOs 6.4) ,it worked perfectly .
Try to debug the code with “var_dump” .First disable part of the script by appending die(); at the end of line 125 .
Let me know if you need more instructions .

6. chidi - November 14, 2014

would like to download into my computer instead of the server. how can that be achieved.

thanks for a great job and keep it up

tournasdimitrios1 - November 15, 2014

@chide
Hi, thanks for taking the time to visit my Blog 🙂
Using a FTP client is most likely the easier solution.
Of course PHP code can be written, it all depends on what exactly is our intention to achieve.
Does these articles help you ?
1) Compress –Zip– Multiple Files And Download with PHP
2)
Let me know .


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s