Spiegelung kompletter Datenbank? [Archiv] - PHP-Scripte PHP-Tutorials PHP-Jobs und vieles mehr

BrainBug

26-10-2001, 09:08

Hallo, also wir verwenden folgendes Skript zum spiegeln von DBs:

<?
function my_handler($sql_insert)
{
global $crlf, $asfile;
// if(empty($asfile))
// echo htmlspecialchars("$sql_insert;$crlf");
// else
echo "$sql_insert;$crlf";
}

function get_table_content($db, $table, $handler){
$result = mysql_query("SELECT * FROM ".$db."." .$table);
if ($result!=false){
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;
}

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"); //\x08\\x09, not required
$replace=array("\\n","\\r","\Z");

@set_time_limit(1200); // 20 Minutes

while($row = mysql_fetch_row($result)){

for($j=0; $j < $field_count; $j++){
if (isset($row[$j])){
if ($field_num[$j]) $values[]=$row[$j]; // a number
else $values[]="'".str_replace($search,$replace,AddSlashes($row[$j]))."'"; // string
}
else if(!isset($row[$j])) $values[]="NULL";
else $values[]="''";
}

$insert_line = $schema_insert.implode (",",$values).")";
unset ($values);

$handler($insert_line);
}
}
}

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

$schema_create = "";
$schema_create .= "DROP TABLE IF EXISTS $table;$crlf";
$schema_create .= "CREATE TABLE $table ($crlf";

$result = mysql_query("SHOW FIELDS FROM " .$db."."
. $table);
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 " .$db."." .
$table);
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);
}
}

?>
<html>
<head>
<title>Datenbank Backup</title>
</head>
<body>
<?
$crlf="<br>";
$default_dbname="QuellDB"; // hier der Name der Quell-Datenbank
$db="ZielDB"; // hier der Name der Ziel-Datenbank

$result = mysql_query("SELECT VERSION() AS version") or mysql_die();
$row = mysql_fetch_array($result);

echo "# Beesite-DB-Dump$crlf";
echo "# Database: ".$default_dbname.$crlf;
echo "# Database Type: ".$database_type."$crlf";
echo "# Time: ".date("F j, Y, g:i a")."$crlf";
echo "# PHPVersion: ".phpversion()."$crlf";
echo "# MYSQL_MAJOR_VERSION: ".substr($row["version"], 0, 4).$crlf;
echo "# MYSQL_MINOR_VERSION: ".substr($row["version"], 5).$crlf;

if (phpversion()>="4.0.5"){
echo "# MySQL client info".mysql_get_client_info();
echo "# MySQL host info".mysql_get_host_info();
echo "# MySQL protocol info".mysql_get_proto_info();
echo "# MySQL server info".mysql_get_server_info();
}
echo $crlf;

$tables = mysql_list_tables($default_dbname);
$num_tables = @mysql_numrows($tables);

$i = 0;
if (isset($table_select)) {
$tmp_select=implode($table_select,"|");
$tmp_select="|".$tmp_select."|";
}
$schema_create ="CREATE DATABASE IF NOT EXISTS ".$default_dbname;

echo $schema_create.";$crlf";

while($i < $num_tables)
{

if (!isset($single)) $table = mysql_tablename($tables, $i);
if(isset($tmp_select) && is_int(strpos($tmp_select,"|".$table."|"))==false) $i++;
else
{

echo "# --------------------------------------------------------$crlf";
echo "$crlf#$crlf";
echo "# TableStructure '$table'$crlf";
echo "#$crlf$crlf";

echo get_table_def($default_dbname, $table, $crlf).";$crlf";
echo "$crlf#$crlf";
echo "# DumpingData '$table'$crlf";
echo "#$crlf$crlf";

get_table_content($db, $table, "my_handler");
$i++;
}
}
echo "$crlf"; // Don't remove, it makes easier to select & copy from browser - staybyte

?>
</body>
</html>

ich hoffe, es sind nicht zuviele Anpassungen an unsere Bedürfnisse drin. Das Skript stammt im Ursprung auch nicht von mir. Mußt also vielleicht bißchen testen. Es gibt ein paar Variablen, wichtig sind $default_dbname (das willst du kopieren) und $db (dorthin kopieren/spiegeln). Viel Glück damit :)