• Programmazione » PHP » Database e PHP

Questo script in PHP permette di fare il backup di un database anche agli utenti che non hanno accesso diretto alla macchina in cui si trova il database. E' sconsigliabile utilizzarlo per database di grosse dimensioni, in quanto il timeout di esecuzione degli script php è troppo basso per processare il backup di grosse moli di dati.

Creiamo un file di testo vuoto chiamato dump.php e inseriamo questo codice:

<?
$target="file";
if($target=="file")
{
header('Content-Type: application/octetstream');
header('Content-Disposition: filename="backup.sql"');
$asfile="download";
}


$crlf="
";

$link = mysql_connect("localhost" , "root" , "password");
$database = mysql_select_db("newobs"); 

$dbname = "newobs";

$dump_buffer="";

$tables = mysql_query("show tables from $dbname");
$num_tables = mysql_num_rows($tables);

if($num_tables == 0)
{
echo "# No Tables Found";
exit;
}

$dump_buffer.= "# DatabaseBackup $crlf";
$dump_buffer.= "# Backup made:$crlf";
$dump_buffer.= "# ".date("F j, Y, g:i a")."$crlf";
$dump_buffer.= "# Database: $dbname$crlf";
$dump_buffer.= "# Backed up tables : $dbname $crlf";

$i = 0;
while($i < $num_tables)
{
$table = mysql_tablename($tables, $i);
//echo $table . "<br>";
$dump_buffer.= "# --------------------------------------------------------$crlf";
$dump_buffer.= "$crlf#$crlf";
$dump_buffer.= "# Table structure for table '$table'$crlf";
$dump_buffer.= "#$crlf$crlf";
$db = $table;
$dump_buffer.= get_table_def($table, $crlf,$dbname).";$crlf";
$dump_buffer.= "$crlf#$crlf";
$dump_buffer.= "# Dumping data for table '$table'$crlf";
$dump_buffer.= "#$crlf$crlf";
$tmp_buffer="";
get_table_content($dbname, $table, 0, 0, 'my_handler', $dbname);
$dump_buffer.=$tmp_buffer;

$i++;
$dump_buffer.= "$crlf";
}
echo $dump_buffer;
exit;

function get_table_def($table, $crlf,$dbname)
{

$schema_create = "DROP TABLE IF EXISTS $table;$crlf";
$db = $table;

$schema_create .= "CREATE TABLE $table ($crlf";

$result = mysql_query("SHOW FIELDS FROM " .$dbname."."
. $table) or die();
while($row = mysql_fetch_array($result))
{
$schema_create .= " $row[Field] $row[Type]";

if(isset($row["Default"]) && (!empty($row["Default"]) || $row["Default"] == "0"))
$schema_create .= " DEFAULT '$row[Default]'";
if($row["Null"] != "YES")
$schema_create .= " NOT NULL";
if($row["Extra"] != "")
$schema_create .= " $row[Extra]";
$schema_create .= " ,$crlf";
}
$schema_create = ereg_replace(" ,".$crlf."$" , "" , $schema_create);
$result = mysql_query("SHOW KEYS FROM " .$dbname."." .
$table) or die();
while($row = mysql_fetch_array($result))
{
$kname=$row['Key_name'];
$comment=(isset($row['Comment'])) ? $row['Comment'] : '';
$sub_part=(isset($row['Sub_part'])) ? $row['Sub_part'] : '';

if(($kname != "PRIMARY") && ($row['Non_unique'] == 0))
$kname="UNIQUE|$kname";

if($comment=="FULLTEXT")
$kname="FULLTEXT|$kname";
if(!isset($index[$kname]))
$index[$kname] = array();

if ($sub_part>1)
$index[$kname][] = $row['Column_name'] . "(" . $sub_part . ")";
else
$index[$kname][] = $row['Column_name'];
}

while(list($x, $columns) = @each($index))
{
$schema_create .= " ,$crlf";
if($x == "PRIMARY")
$schema_create .= " PRIMARY KEY (";
elseif (substr($x,0,6) == "UNIQUE")
$schema_create .= " UNIQUE " .substr($x,7)." (";
elseif (substr($x,0,8) == "FULLTEXT")
$schema_create .= " FULLTEXT ".substr($x,9)." (";
else
$schema_create .= " KEY $x (";

$schema_create .= implode($columns," , ") . ")";
}

$schema_create .= "$crlf)";
if(get_magic_quotes_gpc()) {
return (stripslashes($schema_create));
} else {
return ($schema_create);
}
}
function get_table_content($db, $table, $limit_from = 0, $limit_to = 0,$handler)
{
// Defines the offsets to use
if ($limit_from > 0) {
$limit_from--;
} else {
$limit_from = 0;
}
if ($limit_to > 0 && $limit_from >= 0) {
$add_query = " LIMIT $limit_from, $limit_to";
} else {
$add_query = '';
}

get_table_content_fast($db, $table, $add_query,$handler);

}

function get_table_content_fast($db, $table, $add_query = '',$handler)
{
$result = mysql_query('SELECT * FROM ' . $db . '.' . $table . $add_query) or die();
if ($result != false) {

@set_time_limit(1200); // 20 Minutes

// Checks whether the field is an integer or not
for ($j = 0; $j < mysql_num_fields($result); $j++) {
$field_set[$j] = mysql_field_name($result, $j);
$type = mysql_field_type($result, $j);
if ($type == 'tinyint' || $type == 'smallint' || $type == 'mediumint' || $type == 'int' ||
$type == 'bigint' ||$type == 'timestamp') {
$field_num[$j] = true;
} else {
$field_num[$j] = false;
}
} // end for

// Get the scheme
if (isset($GLOBALS['showcolumns'])) {
$fields = implode(', ', $field_set);
$schema_insert = "INSERT INTO $table ($fields) VALUES (";
} else {
$schema_insert = "INSERT INTO $table VALUES (";
}

$field_count = mysql_num_fields($result);

$search = array("x0a" ,"x0d" ,"x1a"); //x08x09, not required
$replace = array("
" ,"" ,"