Function to backup database as zip file using php
by Sasikumar[ Edit ] 2014-04-03 17:59:58
Function to backup database in php:
function backupDB()
{
define('BACKUP_DIR', './dbbackups' ); // path of the backup directory
define('HOST', 'HOST_NAME' ); // hostname
define('USER', 'USER_NAME' ); // username
define('PASSWORD', 'PASSWORD' ); // password
define('DB_NAME', 'DATABASE_NAME' ); //database name
$fileName = 'mysqlbackup--' . date('d-m-Y') . '@'.date('h.i.s').'.sql' ; //backup sql file name
// 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 .= "--
";
$return .= "-- A Mysql Backup System
";
$return .= "--
";
$return .= '-- Export created: ' . date("Y/m/d") . ' on ' . date("h:i") . "
";
$return = "--
";
$return .= "-- Database : " . DB_NAME . "
";
$return .= "--
";
$return .= "-- --------------------------------------------------
";
$return .= "-- ---------------------------------------------------
";
$return .= 'SET AUTOCOMMIT = 0 ;' ."
" ;
$return .= 'SET FOREIGN_KEY_CHECKS=0 ;' ."
" ;
$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 .= "--
" ;
$return .= '-- Tabel structure for table `' . $table . '`' . "
" ;
$return .= "--
" ;
$return.= 'DROP TABLE IF EXISTS `'.$table.'`;' . "
" ;
// 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].";" . "
" ;
// 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 .= ");" ."
" ;
}
$return .= "
" ;
}
// Close the connection
$mysqli->close() ;
$return .= 'SET FOREIGN_KEY_CHECKS = 1 ; ' . "
" ;
$return .= 'COMMIT ; ' . "
" ;
$return .= 'SET AUTOCOMMIT = 1 ; ' . "
" ;
//$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
<h2>BACKUP completed ,</h2> File Name is : $fileName File Size is : $fileSize .
msg;
echo $message ;
}
