用php备份mysql表 [英] backup mysql tables with php
本文介绍了用php备份mysql表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果表前缀与子字符串匹配,我想从数据库备份表(with PHP)
.我正在尝试但无法正常工作
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 "<pre>Table: {$row[0]}\n</pre>";
system( 'mysqldump $dbname $row[0] > verlag_$row[0].sql');
}
mysql_free_result($result);
推荐答案
此处是用于从db或仅对某些表进行bakups的功能
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<$num_rows; $i++){
$items = mysql_fetch_row($result);
$vals[$z]="(";
for($j=0; $j<count($items); $j++){
if (isset($items[$j])) { $vals[$z].= "'".mysql_real_escape_string( $items[$j], $link )."'"; } else { $vals[$z].= "NULL"; }
if ($j<(count($items)-1)){ $vals[$z].= ","; }
}
$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");
$result = mysql_query("SHOW TABLES");
用于表präfix
这篇关于用php备份mysql表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文