Backup database dengan Php

😀 hellloooo world
hahaha langsung aja yuk ahh, males mau basa-basi segala.
Hmm apa saya curhat dulu aja yaa?? 😀
Banyak nih yang mau diutarakan isi hati saya padahal 😦 . Gimana?? masih mau baca kan kalo saya curhat?
ENGGAAAAAA…
haaa?? en’ga?
😀 hehehe yaudah kita langsung aja.

Nyambi kerja iseng-iseng brosing nemu tutorial keren, jadi diblog orang itu dijelasin cara untuk ng’backup database mysql dan postgresql pake skrip php. Ada dua cara, salah satunya murni generate dump data pake skrip php dan satunya lagi memanfaatkan fitur command dari masing-masing DBMS itu.

Saya akan share dua-duanya, dan kalo skripnya jalan kasih tau yaa 😀 hehehe soalnya belom nyoba

1. Backup MySql
a). cara 1 skrip php murni

<?php

backup_tables('localhost','root','','sekolah','*');

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables)
{
	
	$link = mysql_connect($host,$user,$pass);
	$db = mysql_select_db($name,$link);
	
	//get all of the tables
	if($tables == '*')
	{
		$tables = array();
		$result = mysql_query('SHOW TABLES');
		while($row = mysql_fetch_row($result))
		{
			$tables[] = $row[0];
		}
	}
	else
	{
		$tables = is_array($tables) ? $tables : explode(',',$tables);
	}
	
// 	var_dump($tables);
	
	//cycle through
	foreach($tables as $table)
	{
		$result = mysql_query('SELECT * FROM '.$table);
		$num_fields = mysql_num_fields($result);
		
		$return.= 'DROP TABLE '.$table.';';
		$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
		$return.= "\n\n".$row2[1].";\n\n";
		
		for ($i = 0; $i < $num_fields; $i++) 
		{
			while($row = mysql_fetch_row($result))
			{
				$return.= 'INSERT INTO '.$table.' VALUES(';
				for($j=0; $j<$num_fields; $j++) 
				{
					$row[$j] = addslashes($row[$j]);
					$row[$j] = ereg_replace("\n","\\n",$row[$j]);
					if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
					if ($j<($num_fields-1)) { $return.= ','; }
				}
				$return.= ");\n";
			}
		}
		$return.="\n\n\n";
	}
	
	$files = 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql';
	
	//save file
	$handle = fopen($files,'w+');
	fwrite($handle,$return);
// 	fclose($handle);
	
	///Download file
	header('Content-Description: File Transfer');
	header('Content-Type: application/octet-stream');
	header('Content-Disposition: attachment; filename='.basename($files));
	header('Content-Transfer-Encoding: binary');
	header('Expires: 0');
	header('Cache-Control: must-revalidate');
	header('Pragma: public');
	header('Content-Length: ' . filesize($files));
	// ///

	// ///
	if(readfile($files)){
		unlink($files); //Hapus file temp
	}
}

b). cara 2 memanfaatkan fungsi system php

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'sekolah';

$backup_file = $dbname."-". date("Y-m-d-H-i-s") . '.sql.zip';
$command = "mysqldump --user=$dbuser --password=$dbpass ".$dbname." | zip > ".$backup_file;

system($command);

///Download file
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($backup_file));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($backup_file));
// ///

// ///
if(readfile($backup_file)){
	unlink($backup_file); //Hapus file temp
}

2. Backup PostgreSql
a). cara 1 php murni

<?php
// scrypt for backup and restore postgres database


function dl_file($file){
   if (!is_file($file)) { die("<b>404 File not found!</b>"); }
   $len = filesize($file);
   $filename = basename($file);
   $file_extension = strtolower(substr(strrchr($filename,"."),1));
   $ctype="application/force-download";
   header("Pragma: public");
   header("Expires: 0");
   header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
   header("Cache-Control: public");
   header("Content-Description: File Transfer");
   header("Content-Type: $ctype");
   $header="Content-Disposition: attachment; filename=".$filename.";";
   header($header );
   header("Content-Transfer-Encoding: binary");
   header("Content-Length: ".$len);
   @readfile($file);
   exit;
}



$action  = $_POST["actionButton"];
$ficheiro=$_FILES["path"]["name"];
switch ($action) {
    case "Import":
      $dbname = "sekolah"; //database name
      $dbconn = pg_pconnect("host=localhost port=5432 dbname=$dbname 
user=postgres password=password"); //connectionstring
      if (!$dbconn) {
        echo "Can't connect.\n";
        exit;
      }
      $back = fopen($ficheiro,"r");
      $contents = fread($back, filesize($ficheiro));
      $res = pg_query(utf8_encode($contents));
      echo "Upload Ok";
      fclose($back);
  break;
  case "Export":
  $dbname = "sekolah"; //database name
  $dbconn = pg_pconnect("host=localhost port=5432 dbname=$dbname 
user=postgres password=password"); //connectionstring
  if (!$dbconn) {
    echo "Can't connect.\n";
  exit;
  }
  $back = fopen("$dbname.sql","w");
  $res = pg_query(" select relname as tablename
                    from pg_class where relkind in ('r')
                    and relname not like 'pg_%' and relname not like 
'sql_%' order by tablename");
  $str="";
  while($row = pg_fetch_row($res))
  {
    $table = $row[0];
    $str .= "\n--\n";
    $str .= "-- Estrutura da tabela '$table'";
    $str .= "\n--\n";
    $str .= "\nDROP TABLE $table CASCADE;";
    $str .= "\nCREATE TABLE $table (";
    $res2 = pg_query("
    SELECT  attnum,attname , typname , atttypmod-4 , attnotnull 
,atthasdef ,adsrc AS def
    FROM pg_attribute, pg_class, pg_type, pg_attrdef WHERE 
pg_class.oid=attrelid
    AND pg_type.oid=atttypid AND attnum>0 AND pg_class.oid=adrelid AND 
adnum=attnum
    AND atthasdef='t' AND lower(relname)='$table' UNION
    SELECT attnum,attname , typname , atttypmod-4 , attnotnull , 
atthasdef ,'' AS def
    FROM pg_attribute, pg_class, pg_type WHERE pg_class.oid=attrelid
    AND pg_type.oid=atttypid AND attnum>0 AND atthasdef='f' AND 
lower(relname)='$table' ");                                             
    while($r = pg_fetch_row($res2))
    {
    $str .= "\n" . $r[1]. " " . $r[2];
     if ($r[2]=="varchar")
    {
    $str .= "(".$r[3] .")";
    }
    if ($r[4]=="t")
    {
    $str .= " NOT NULL";
    }
    if ($r[5]=="t")
    {
    $str .= " DEFAULT ".$r[6];
    }
    $str .= ",";
    }
    $str=rtrim($str, ",");  
    $str .= "\n);\n";
    $str .= "\n--\n";
    $str .= "-- Creating data for '$table'";
    $str .= "\n--\n\n";

    
    $res3 = pg_query("SELECT * FROM $table");
    while($r = pg_fetch_row($res3))
    {
      $sql = "INSERT INTO $table VALUES ('";
      $sql .= utf8_decode(implode("','",$r));
      $sql .= "');";
      $str = str_replace("''","NULL",$str);
      $str .= $sql;  
      $str .= "\n";
    }
    
     $res1 = pg_query("SELECT pg_index.indisprimary,
            pg_catalog.pg_get_indexdef(pg_index.indexrelid)
        FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
            pg_catalog.pg_index AS pg_index
        WHERE c.relname = '$table'
            AND c.oid = pg_index.indrelid
            AND pg_index.indexrelid = c2.oid
            AND pg_index.indisprimary");
    while($r = pg_fetch_row($res1))
    {
    $str .= "\n\n--\n";
    $str .= "-- Creating index for '$table'";
    $str .= "\n--\n\n";
    $t = str_replace("CREATE UNIQUE INDEX", "", $r[1]);
    $t = str_replace("USING btree", "|", $t);
    // Next Line Can be improved!!!
    $t = str_replace("ON", "|", $t);
    $Temparray = explode("|", $t);
    $str .= "ALTER TABLE ONLY ". $Temparray[1] . " ADD CONSTRAINT " . 
$Temparray[0] . " PRIMARY KEY " . $Temparray[2] .";\n";
    }   
  }
  $res = pg_query(" SELECT
  cl.relname AS tabela,ct.conname,
   pg_get_constraintdef(ct.oid)
   FROM pg_catalog.pg_attribute a
   JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind = 'r')
   JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)
   JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND
   ct.confrelid != 0 AND ct.conkey[1] = a.attnum)
   JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND 
clf.relkind = 'r')
   JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)
   JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND
   af.attnum = ct.confkey[1]) order by cl.relname ");
  while($row = pg_fetch_row($res))
  {
    $str .= "\n\n--\n";
    $str .= "-- Creating relacionships for '".$row[0]."'";
    $str .= "\n--\n\n";
    $str .= "ALTER TABLE ONLY ".$row[0] . " ADD CONSTRAINT " . $row[1] . 
" " . $row[2] . ";";
  }       
  fwrite($back,$str);
  fclose($back);
  dl_file("$dbname.sql");
  break;
}

?>
 
<html>
<head>
</head>
<body>
<form id="dataForm" name="dataForm" method="post" 
enctype="multipart/form-data" action="">
    <input type="file" name="path" id="path" style="width:300px"/>
    <input type="submit" value="Import" name="actionButton" 
id="actionButton" >
    <input type="submit" value="Export" name="actionButton" 
id="actionButton" >
</form>
</body>
</html>

b). cara 2 memanfaatkan fungsi system php

<?php
$dbhost = 'localhost';
$dbuser = 'postgres';
$dbname = 'sekolah';

$backup_file = $dbname."-". date("Y-m-d-H-i-s") . '.sql';
$command = "PGPASSWORD=password pg_dump $dbname -U $dbuser -h $dbhost -F p > $backup_file";

system($command);

///Download file
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($backup_file));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($backup_file));

if(readfile($backup_file)){
	unlink($backup_file); //Hapus file temp
}

beuhh gimana??
kerenkan??
hahahaaa sok dicoba heula, nanti kalo bisa kasih tau dikomentar yaa 😀
sedikit kutipan lagu hari ini buat kamu mba :p

Bagaimana mestinya…
Membuatmu jatuh hati kepadaku
Tlah kutulis kan sejuta puisi
Meyakinkanmu membalas cintaku…

Haruskah ku mati karena mu
Terkubur dalam kesedihan sepanjang waktu..

Advertisements
This entry was posted in php and tagged , , , , . Bookmark the permalink.

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