PHP选择性备份MySQL数据库完整函数

MySQL备份的方式很多，这里介绍如何用PHP有选择性的备份。

公共函数：传入连接数据库的参数和要备份的表即可，返回为表结构即数据sql字符串

function backup_tables($host, $user, $pass, $name, $tables = '*') {
	$link = mysql_connect($host, $user, $pass);
	if(!$link) die(" connection failed");
	mysql_select_db($name, $link);
	mysql_query('set names utf8');

	if($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);
	}
	$return = '';

	foreach ($tables as  $table) {
		$result = mysql_query('SELECT * FROM '.$table);
		$num_fields = mysql_num_fields($result);
		$return .= 'DROP TABLE '.$table.';';
		$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
		$return .= "\n\n".$row2[1].";\n\n";

		for($i=0; $i<$num_fields; $i++) {
			while($row = mysql_fetch_row($result)) {
				$return .= 'INSERT INTO '.$table.' VALUES(';
				for($j=0; $j<$num_fields; $j++) {
					$row[$j] = addslashes($row[$j]);
					//$row[$j] = ereg_replace("\n", "\\n", $row[$j]);
					if(isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
					if($j<($num_fields-1)) { $return .= ','; }
				}
				$return .= ");\n";
			}
		}
		$return .= "\n\n\n";
	}

	return $return;
}

调用示例：

echo  backup_tables($host, $user, $passwd, $dbname, $table);

在浏览器中导出为文件：

$filename = "bak".date("Ymj").".sql";
header("Content-disposition:filename=".$filename);
header("Content-type:application/octetstream");
header("Pragma:no-cache");
header("Expires:0");

直接在浏览器中输出：

header('content-type:text/plain;charset=utf-8');

记得在脚本最开始关闭错误信息提示：

error_reporting(0);

下面是一个完整的示例：（导出文件到浏览器）

<?php
error_reporting(0);
//header('content-type:text/plain;charset=utf-8'); //这个是以纯文本输出用的
$host = 'localhost';
$user = 'root';
$passwd = '';
$dbname = 'db';

$filename = $dbname.date("Ymj").".sql";
header("Content-disposition:filename=".$filename);
header("Content-type:application/octetstream");
header("Pragma:no-cache");
header("Expires:0");

echo  backup_tables($host, $user, $passwd, $dbname);

function backup_tables($host, $user, $pass, $name, $tables = '*') {
	$link = mysql_connect($host, $user, $pass);
	if(!$link) die("fucking connection failed");
	mysql_select_db($name, $link);
	mysql_query('set names utf8');

	if($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);
	}
	$return = '';

	foreach ($tables as  $table) {
		$result = mysql_query('SELECT * FROM '.$table);
		$num_fields = mysql_num_fields($result);
		$return .= 'DROP TABLE '.$table.';';
		$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
		$return .= "\n\n".$row2[1].";\n\n";

		for($i=0; $i<$num_fields; $i++) {
			while($row = mysql_fetch_row($result)) {
				$return .= 'INSERT INTO '.$table.' VALUES(';
				for($j=0; $j<$num_fields; $j++) {
					$row[$j] = addslashes($row[$j]);
					//$row[$j] = ereg_replace("\n", "\\n", $row[$j]);
					if(isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
					if($j<($num_fields-1)) { $return .= ','; }
				}
				$return .= ");\n";
			}
		}
		$return .= "\n\n\n";
	}

	return $return;
}