php备份mysql表_使用php备份mysql表

这是一个从db或只有一些表创建bakup的函数

function &backup_tables($host, $user, $pass, $name, $tables = '*'){

$data = "\n/*---------------------------------------------------------------".

"\n SQL DB BACKUP ".date("d.m.Y H:i")." ".

"\n HOST: {$host}".

"\n DATABASE: {$name}".

"\n TABLES: {$tables}".

"\n ---------------------------------------------------------------*/\n";

$link = mysql_connect($host,$user,$pass);

mysql_select_db($name,$link);

mysql_query( "SET NAMES `utf8` COLLATE `utf8_general_ci`" , $link ); // Unicode

if($tables == '*'){ //get all of the 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);

}

foreach($tables as $table){

$data.= "\n/*---------------------------------------------------------------".

"\n TABLE: `{$table}`".

"\n ---------------------------------------------------------------*/\n";

$data.= "DROP TABLE IF EXISTS `{$table}`;\n";

$res = mysql_query("SHOW CREATE TABLE `{$table}`", $link);

$row = mysql_fetch_row($res);

$data.= $row[1].";\n";

$result = mysql_query("SELECT * FROM `{$table}`", $link);

$num_rows = mysql_num_rows($result);

if($num_rows>0){

$vals = Array(); $z=0;

for($i=0; $i

$items = mysql_fetch_row($result);

$vals[$z]="(";

for($j=0; $j

if (isset($items[$j])) { $vals[$z].= "'".mysql_real_escape_string( $items[$j], $link )."'"; } else { $vals[$z].= "NULL"; }

if ($j

}

$vals[$z].= ")"; $z++;

}

$data.= "INSERT INTO `{$table}` VALUES ";

$data .= " ".implode(";\nINSERT INTO `{$table}` VALUES ", $vals).";\n";

}

}

mysql_close( $link );

return $data;

}

如何使用：

// create backup

//

$backup_file = 'db-backup-'.time().'.sql';

// get backup

$mybackup = backup_tables("myhost","mydbuser","mydbpasswd","mydatabase","*");

// save to file

$handle = fopen($backup_file,'w+');

fwrite($handle,$mybackup);

fclose($handle);

您可以修改该行：

$result = mysql_query(“SHOW TABLES”);

对于表präfix