Home

Thursday, July 11, 2013

Database backup using PHP

<?php
define('BACKUP_DIR', './backups' ) ;
define('HOST', 'localhost' ) ;
define('USER', 'root' ) ;
define('PASSWORD', 'pass' ) ;
define('DB_NAME', 'dbname' ) ;

$fileName = 'mysqlbackup--' . date('d-m-Y') . '@'.date('h.i.s').'.sql' ;
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"  ;
//$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>

  the archive has the name of  : <b>  $fileName  </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 ;

// 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" ;
}
}
?>

Database Clone using PHP

<?php
    $source_db='source';
    $target_db='target';
    
    $server='localhost';
    $user='root';
    $password='';
  
    mysql_connect($server,$user,$password);
  
   if(mysql_select_db($target_db))
   {
       $sql = "DROP DATABASE ".$target_db;
       mysql_query($sql) or die(mysql_error());
   }
  
   if(!mysql_select_db($target_db))  
   {
       $sql = "CREATE DATABASE ".$target_db;
       mysql_query($sql) or die(mysql_error());
   }
   
  
    mysql_select_db($source_db) or die(mysql_error());

    // Get names of all tables in source database
    $result=mysql_query("show tables");
    while($row=mysql_fetch_array($result)){
        $name=$row[0];
        $this_result=mysql_query("show create table $name");
        $this_row=mysql_fetch_array($this_result);
        $tables[]=array('name'=>$name,'query'=>$this_row[1]);
    }

    // Connect target database to create and populate tables
    mysql_select_db($target_db) or die(mysql_error());

    $total=count($tables);
    for($i=0;$i < $total;$i++){
        $name=$tables[$i]['name'];
        $q=$tables[$i]['query'];
         echo $i.">>>".$total;
        echo "<br>";
        mysql_query($q);
        mysql_query("insert into $name select * from $source_db.$name")  or die(mysql_error());
    }
    
?>
rathoddhirendra.blogspot.com-Google pagerank and Worth