Willkommen in der Webstatt Zum Webstatt Blog und Stories
Franky Franky am 01.05.07 01:58

MySQL Datenbank exportieren wie bei PHPmyAdmin in PHP v.0.1
Oft möchte man eine MySQL-Datenbank exportieren - beispielsweise als Backup, so dass man diese später wieder (z.B. per mysql_query()) importieren kann.
Diese Funktion exportiert komplette Tabellen mit Struktur und Inhalt!

<?
// (c) 2005 by Franky
// http://FrankyOnline.de/
// Dieser Vermerk darf nicht entfernt werden

function parsefield($field, $typ){
if($typ == 'integer') return $field;
else{
$field = addslashes($field);
$field = str_replace("\t", "\\t", $field);
$field = str_replace("\r", "\\r", $field);
$field = str_replace("\n", "\\n", $field);
return '\''.$field.'\'';
}
}

function mysql_export_table($table, $connection=''){
// Struktur
$dump = "CREATE TABLE `".$table."` (\n";

$types = array();
$result = mysql_query('DESCRIBE '.$table, $connection);
$count = mysql_num_rows($result);

$i = 0;
while($row = mysql_fetch_array($result)){
$name = $row['Field'];
$typ = ' '.$row['Type'];

$fieldtypes[$name] = (!stristr($row['Type'], "ENUM") && ((stristr($row['Type'], "INT") || stristr($row['Type'], "FLOAT") || stristr($row['Type'], "DOUBLE") || stristr($row['Type'], "REAL") || stristr($row['Type'], "DECIMAL") || stristr($row['Type'], "NUMERIC") || stristr($row['Type'], "TIMESTAMP") || stristr($row['Type'], "YEAR"))) ? ("integer") : ("string"));

if($row['Null'] == '') $null = ' NOT NULL';
else $null = ' NULL';

if($row['Default'] == '') $default = '';
else $default = " DEFAULT '".$row['Default']."'";

if($row['Extra'] == '') $extra = '';
else $extra = ' '.$row['Extra'];

$dump .= " ".$name.$typ.$null.$default.$extra;

$i++;
if($i < $count) $dump .= ", \n";
}

$index = array();
$result = mysql_query('SHOW KEYS FROM '.$table, $connection);
while($row = mysql_fetch_array($result)){
$keyname = $row['Key_name'];
$comment = (isset($row['Comment'])) ? $row['Comment'] : '';
$sub_part = (isset($row['Sub_part'])) ? $row['Sub_part'] : '';

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

if($comment == 'FULLTEXT') $keyname = 'FULLTEXT|'.$keyname;

if(!isset($index[$keyname])) $index[$keyname] = array();

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

foreach($index as $keyname => $columns){
$dump .= ", \n";
if($keyname == "PRIMARY") $dump .= " PRIMARY KEY (";
elseif(substr($keyname, 0, 6) == "UNIQUE") $dump .= " UNIQUE ".substr($keyname, 7)." (";
elseif(substr($keyname, 0, 8) == "FULLTEXT") $dump .= " FULLTEXT ".substr($keyname, 9)." (";
else $dump .= " KEY ".$keyname." (";

$dump .= implode($columns, ", ").')';
}

$dump .= "\n);\n\n";



// Content
$insert_tag = 0;
$rowcount = 0;
$result = mysql_query('SELECT * FROM '.$table, $connection);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$values = '';
foreach($row as $key => $field)
if($values != '') $values .= ','.parsefield($field, $fieldtypes[$key]);
else $values = parsefield($field, $fieldtypes[$key]);

if($insert_tag == 0){
$dump .= 'INSERT INTO '.$table.' VALUES ('.$values.')';
$insert_tag = 1;
}else $dump .= ',('.$values.')';

if($insert_tag == 1 && $rowcount == 500){
$dump .= ";\n";
$insert_tag = 0;
$rowcount = 0;
}

$rowcount++;
}
mysql_free_result($result);

if($insert_tag == 1) $dump .= ";\n";

return $dump;
}
?>



Beispiel
<?
$mysqlconnect = mysql_connect('localhost', 'root', '');
$result = mysql_list_tables('datenbank', $mysqlconnect);
$num_tables = mysql_num_rows($result);

for($i=0;$i<$num_tables;$i++){
$table = mysql_tablename($result, $i);
echo mysql_export_table($table, $mysqlconnect);
}

mysql_free_result($result);
mysql_close($mysqlconnect);
?>


Ausgabe
CREATE TABLE `counter` (
`datum` date NOT NULL default '0000-00-00',
`visitors` int(5) NOT NULL default '0',
PRIMARY KEY (`datum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `counter` VALUES ('2005-09-03', 25);
INSERT INTO `counter` VALUES ('2005-09-04', 48);
INSERT INTO `counter` VALUES ('2005-09-05', 31);
# ...

netcup.de Warum gibt es hier Werbung?
Creative Commons Lizenzvertrag
Alle Inhalte des Webstatt-Archivs stehen unter einer Creative Commons Namensnennung - Weitergabe unter gleichen Bedingungen 3.0 Unported Lizenz.

Impressum & Kontakt