MySql Restore Dump File

[iNod]

Hello,

Does anybody know any code to store a dump file? (SQL Backup?)

iNod

 

[abcde]

Use this code

<?php


// please edit this
$server = "localhost";
$username = "";
$password = "";
$database = "";
$prefix = ""; // leave empty will back up all tables
$exclude = array('search_results', 'sessions'); // which don't you want back up
$target = ".."; // where to put files, either absolute or relative path
$gzipped = true;


// edit if you really want to do
define('MAXLENGTH', 20000);
define('REMARK', '#');
define('TIMEOUT', 120);
define('MAXGZIP', 52428800);
define('PARSESQL', false);
define('BIGDATA', true);

// no edit below here
function do_dump($table, $fp=0)
{
if (in_array(substr($table, strlen($GLOBALS['prefix'])), $GLOBALS['exclude'])) return;
$tabledump = "\n";
$tabledump.= "DROP TABLE IF EXISTS $table;\n";
$rows = mysql_query("SHOW CREATE TABLE $table");
$data = mysql_fetch_array($rows);
$tabledump .= preg_replace('/\r|\n|\t/', '', $data[1]).";\n";
if ($fp)
 fwrite($fp, $tabledump);
else
 echo $tabledump;

$rows = mysql_query("SELECT * FROM $table");
$numfields = mysql_num_fields($rows);
$dump = array();
$length = 0;
while ($row = mysql_fetch_array($rows))
{
 $data = '(';
 for ($i=0; $i<$numfields; $i++)
 {
  if ($i!=0) $data .= ',';
  $data .= isset($row[$i]) ? "'".mysql_escape_string($row[$i])."'" : 'NULL';
 }
 $dump[] = $data.')';
 $length += strlen($data)+1;

 if ($length>MAXLENGTH)
 {
  $tabledump = "INSERT INTO $table VALUES " . implode(', ', $dump).";\n";
  $dump = array();
  $length = 0;
  if ($fp)
   fwrite($fp, $tabledump);
  else
   echo $tabledump;
 }
}
mysql_free_result($rows);

if ($length>0)
{
 $tabledump = "INSERT INTO $table VALUES " . implode(', ', $dump).";\n";
 if ($fp)
  fwrite($fp, $tabledump);
 else
  echo $tabledump;
}
}

function do_restore()
{
if (empty($_GET['restore']))
{
 echo 'Current backups';
 $dp = opendir($GLOBALS['path']);
 while($entryname = readdir($dp))
 {
  if(substr($entryname,0,4)=='vnt_')
   echo '<li><a href="'.$_SERVER['PHP_SELF'].'?restore='.substr($entryname,4).'">'.preg_replace('/vnt_(.*)_(..)(..)(..)_(..)(..)\..*/','[\1] \4/\3/20\2 \5:\6', $entryname).'</a>';
 }
 closedir($dp);
}
else
{
 $filename = $GLOBALS['path'].'/vnt_'.$_GET['restore'];
 if (!file_exists($filename)) die("$filename File not found!");
 if (BIGDATA)
 {
  $fp = @gzopen($filename, 'rb');
 }
 elseif (substr($filename, -4)=='.sql')
 {
  $sql_query = @fread(@fopen($filename, 'rb'), @filesize($filename));
 }
 else
 {
  $sql_query = @gzread(@gzopen($filename, 'rb'), MAXGZIP);
 }
 $database = preg_replace('/^(.*?)_\d\d.*$/', '\1', $_GET['restore']);
 mysql_select_db($database);
 echo "Selected database: $database<br>";

 if (BIGDATA)
 {
  echo "BIGDATA mode. It will be slower, but safer.<br>";
  echo "Running (each dot is a block of 100 queries): ";
  while (!gzeof($fp))
  {
   $tmp = trim(gzgets($fp));
   $i++;
   if (substr($tmp,0,1)!='#' && !empty($tmp))
    if (!mysql_query($tmp))
    {
     echo "<div style='font:10px Tahoma;color:red'>".htmlentities($tmp)."</div>";
     echo mysql_error();
     break;
    }
   if ($i%100==0) {echo '. ';flush();}
  }
 }
 else
 {
  if (PARSESQL)
  {
   $sql_query = remove_remarks($sql_query);
   $sql_query = split_sql_file($sql_query, ';');
  }
  else
   $sql_query = explode("\n", $sql_query);
 
  $n = count($sql_query);$j=0;
  echo "Running (".ceil($n/100)." blocks of queries): ";
  for ($i=0; $i<$n; $i++)
  {
   $tmp = trim($sql_query[$i]);
   if (substr($tmp,0,1)!='#' && !empty($tmp))
    if (!mysql_query($tmp))
    {
     echo "<div style='font:10px Tahoma;color:red'>".htmlentities($tmp)."</div>";
     echo mysql_error();
     break;
    }
   if ($i%100==0) {echo ++$j%10;flush();}
  }
 }
}
}

function do_backup()
{
$filename = $GLOBALS['path'].'/vnt_'.$GLOBALS['database'].date('_ymd_Hi').'.sql';
$fp = fopen($filename,"w");

// Header
$header = "#----------------------------------------\n";
$header.= "# DATABASE:  ".$GLOBALS['database']."\n";
$header.= "# Date/Time:  ".date ("l dS of F Y H:i:s")."\n";
$header.= "#----------------------------------------\n";
fwrite($fp, $header);

$tablesbackup = mysql_query("SHOW tables LIKE '".$GLOBALS['prefix']."%'");
$nums = mysql_num_rows($tablesbackup);
echo "Dumping ($nums tables): ";flush();$i=0;
while ($tablebackup = mysql_fetch_array($tablesbackup))
{
 do_dump($tablebackup[0], $fp);
 echo ++$i%10;flush();
}
fclose($fp);

if ($GLOBALS['gzipped'])
 echo "<br>Preparing to gzip...<script>setTimeout(\"location.href='$_SERVER[PHP_SELF]?gzip=$filename'\", 2000)</script>";
else
 echo '<br><a href="'.$_SERVER['PHP_SELF'].'?restore">Go to RESTORE section</a>';
}

function do_gzip()
{
$filename = $_GET['gzip'];
if (!file_exists($filename)) die('File not found');
$nums = ceil(@filesize($filename)/1048576);
echo "<br>Gzipping ($nums MB): ";flush();$i=0;
$fr = @fopen ($filename, 'rb') or die('Read file error: '.$filename);
$zw = @gzopen ($filename.'.gz', 'wb') or die('Write file error: '.$filename);
while (!feof($fr))
{
 gzwrite ($zw, fread ($fr, 1048576));
 echo ++$i%10;flush();
}
fclose ($fr);
fclose ($zw);
@unlink($filename);
echo '<br><a href="'.$_SERVER['PHP_SELF'].'?restore">Go to RESTORE section</a>';
}

echo '<div style="font:9pt Verdana">Please wait until you see <b>Completed!</b> below<hr><br><br>';
flush();
$starttime = getmicrotime();

@set_time_limit(TIMEOUT);
@error_reporting (E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
$path = (substr($target,0,1)=='/') ? $target : str_replace("\\", "/", dirname(__FILE__)) . '/'.$target;
if (!$connect = mysql_connect($server,$username,$password)) die("khong ket noi duoc voi mysql");
mysql_select_db($database);

if (isset($_GET['restore']))
do_restore();
elseif (isset($_GET['backup']))
do_backup();
elseif (isset($_GET['gzip']))
do_gzip();
else
echo '<br>Do you want to <a href="'.$_SERVER['PHP_SELF'].'?backup">backup</a> or <a href="'.$_SERVER['PHP_SELF'].'?restore">restore</a> ?';

$spendtime = getmicrotime()-$starttime;
printf("<br><br><br><hr><b>Completed!</b> %.4f sec     [<a href='$_SERVER[PHP_SELF]'>index</a>]<br>GZIP ".($gzipped?'on':'off')."     PARSESQL ".(PARSESQL?'on':'off')."     BIGDATA ".(BIGDATA?'on':'off')."</div>", $spendtime);

// ------------------------------------
// These functions are from phpBB 2.0
function remove_comments(&$output)
{
$lines = $output;
$output = "";

// try to keep mem. use down
$linecount = count($lines);

$in_comment = false;
for($i = 0; $i < $linecount; $i++)
{
 if( preg_match("/^\/\*/", preg_quote($lines[$i])) )
 {
  $in_comment = true;
 }

 if( !$in_comment )
 {
  $output .= $lines[$i] . "\n";
 }

 if( preg_match("/\*\/$/", preg_quote($lines[$i])) )
 {
  $in_comment = false;
 }
}

unset($lines);
return $output;
}

function remove_remarks($sql)
{
$lines = explode("\n", $sql);

// try to keep mem. use down
$sql = "";

$linecount = count($lines);
$output = "";

for ($i = 0; $i < $linecount; $i++)
{
 if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
 {
  if ($lines[$i][0] != REMARK)
  {
   $output .= $lines[$i] . "\n";
  }
  else
  {
   $output .= "\n";
  }
  // Trading a bit of speed for lower mem. use here.
  $lines[$i] = "";
 }
}

return $output;
}
function split_sql_file($sql, $delimiter)
{
// Split up our string into "possible" SQL statements.
$tokens = explode($delimiter, $sql);

// try to save mem.
$sql = "";
$output = array();

// we don't actually care about the matches preg gives us.
$matches = array();

// this is faster than calling count($oktens) every time thru the loop.
$token_count = count($tokens);
for ($i = 0; $i < $token_count; $i++)
{
 // Don't wanna add an empty string as the last thing in the array.
 if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
 {
  // This is the total number of single quotes in the token.
  $total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
  // Counts single quotes that are preceded by an odd number of backslashes, 
  // which means they're escaped quotes.
  $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);
  
  $unescaped_quotes = $total_quotes - $escaped_quotes;
  
  // If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
  if (($unescaped_quotes % 2) == 0)
  {
   // It's a complete sql statement.
   $output[] = $tokens[$i];
   // save memory.
   $tokens[$i] = "";
  }
  else
  {
   // incomplete sql statement. keep adding tokens until we have a complete one.
   // $temp will hold what we have so far.
   $temp = $tokens[$i] . $delimiter;
   // save memory..
   $tokens[$i] = "";
   
   // Do we have a complete statement yet? 
   $complete_stmt = false;
   
   for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++)
   {
    // This is the total number of single quotes in the token.
    $total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
    // Counts single quotes that are preceded by an odd number of backslashes, 
    // which means they're escaped quotes.
    $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);
  
    $unescaped_quotes = $total_quotes - $escaped_quotes;
    
    if (($unescaped_quotes % 2) == 1)
    {
     // odd number of unescaped quotes. In combination with the previous incomplete
     // statement(s), we now have a complete statement. (2 odds always make an even)
     $output[] = $temp . $tokens[$j];

     // save memory.
     $tokens[$j] = "";
     $temp = "";
     
     // exit the loop.
     $complete_stmt = true;
     // make sure the outer loop continues at the right point.
     $i = $j;
    }
    else
    {
     // even number of unescaped quotes. We still don't have a complete statement. 
     // (1 odd and 1 even always make an odd)
     $temp .= $tokens[$j] . $delimiter;
     // save memory.
     $tokens[$j] = "";
    }
    
   } // for..
  } // else
 }
}

return $output;
}
function getmicrotime()
{
  list($usec, $sec) = explode(" ",microtime()); 
  return ((double)$usec + (double)$sec); 
} 
?>

 

[iNod]

Thanks Deeply! May I ask for the url of the site you got it from? Or if you coded it. May I use it in my script?

iNod

 

[abcde]

oppz!

I saved this code in my hd for years, and forgot where i got it from.

 

[znender]

Great Script!
it's awesome and it works perfectly ^^
Thanks