使用php备份mysql表

发布时间：2020-12-13 22:04:44 所属栏目：PHP教程来源：网络整理

导读：如果表前缀与子字符串匹配,我想从db备份表(使用 PHP).我在尝试什么,不工作 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 =

如果表前缀与子字符串匹配,我想从db备份表(使用 PHP).我在尝试什么,不工作

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 listedn";
    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或只有一些表创建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,$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”);

对于表pr?fix

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!