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

I would like to backup tables (with PHP) from a db if the table prefix is matching with a sub string. What I was trying and is not working

error_reporting(1);

$dbname = 'wp_dev';

if (!mysql_connect('127.0.0.1', 'root', '')) {

echo 'Connection Error';

exit;

}

$sql = "SHOW TABLES FROM $dbname LIKE 'wp_%'";

$result = mysql_query($sql);

if (!$result) {

echo "DB tables could not be listed\n";

echo 'MySQL Fehler: ' . mysql_error();

exit;

}

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

echo "

Table: {$row[0]}\n

";

system( 'mysqldump $dbname $row[0] > verlag_$row[0].sql');

}

mysql_free_result($result);

解决方案

Here is a function for making bakups from db or only some tables

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;

}

How to use:

// 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);

You can modify the line:

$result = mysql_query("SHOW TABLES");

for the table präfix