我想做一个简单的表单,显示服务器上的数据库列表,允许用户选择一个DB点击ok,它开始下载一个.sql文件,可以用来恢复DB
<?php
ob_start();
// print_r($_POST);
$username = "dbselect";
$password = "PASSWORD";
$hostname = "SERVER";
$dbname = $_POST['db_select'];
if (isset($_POST['db_select'])) {
//echo "file created on " . date('mdy');
// $command = "mysqldump --add-drop-table --result-file=/var/www/db-dump/${dbname}.sql --host=" . $hostname . " --user=" . $username . " --password=". $password . " " . $dbname;
$command = "mysqldump --opt --add-drop-table --host=" . $hostname . " --user=" . $username . " --password=" . $password . " " . $dbname;
system($command);
file_put_contents("/var/www/db-dump/return");
$dump = ob_get_contents();
ob_end_clean();
// send dump file to the output
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($dbname . "_" . date("Y-m-d_H-i-s").".sql"));
flush();
echo $dump;
exit();
//echo date('mdy');
} else {
$dbh = new PDO("mysql:host=hostname", username, password);
$dbs = $dbh->query('SHOW DATABASES');
?>
</head>
<body>
<header>
<h1> Databases</h1>
<p>on <b>d.uk</b></p>
</header>
<section class="databases">
<p>Select and download any production database from any app or site.</p>
<form method="POST" action="<?php $_SERVER['PHP_SELF']; ?>">
<select name="db_select" id="">
<?php
foreach ($dbs as $db) {
echo "<option value='" . $db['Database'] . "'>";
echo $db['Database'];
echo "</option>";
}
?>
</select>
<button type="submit">Download database</button>
</form>
</section>
</body>
</html>
<?php } ?>
我遇到的问题是,只有一个空白的。sql文件被创建。如果我更改它,使我只使用mysqldump命令,为转储指定一个文件位置,我将在文件中获得更多信息,但只是其中的一部分:
——MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686)
—Host: localhost Database:
—服务器版本5.5.35-0ubuntu0.12.04.2
/* !40101 SET @OLD_CHARACTER_SET_CLIENT=@ character_set_client /;/ !40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;/ !40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;/ !40101 SET NAMES utf8 /;/ !40103 set @old_time_zone = @@time_zone/;/ !40103 SET TIME_ZONE='+00:00' /;/ !40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;/ !40014集@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;/ !40101 set @old_sql_mode = @@sql_modeSQL_MODE = ' NO_AUTO_VALUE_ON_ZERO ' /;/ !40111集@OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
数据库非常大(大约30MB),所以该命令确实需要一些时间(大约10-15分钟),所以我认为这是一个超时问题。我唯一能找到的关于提高php发送命令的时间是set_the_limit,但这没有帮助。
我只是使用以下脚本创建MySQL转储(我的有57MB),也许你可以使用它(它不是完全好,像全局):
$dbname = $db_database."-".date("Y-m-d G").'.sql';
$backupfile = $root."files/".$dbname;
function backup_tables($host, $user, $pass, $name, $tables = '*') {
global $dbname;
global $backupfile;
$link = mysql_connect($host, $user, $pass);
mysql_select_db($name, $link);
//get all of the tables
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);
}
//cycle through
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";
}
//save file
$handle = fopen($backupfile, 'w+');
fwrite($handle, $return);
fclose($handle);
}
backup_tables('localhost', $db_login, $db_pass, $db_database);
不确定,但我认为你可以得到你正在寻找的使用:
<?php
$password = "PASSWORD";
$hostname = "SERVER";
$dbname = $_POST['db_select'];
if (isset($_POST['db_select'])) {
//echo "file created on " . date('mdy');
// $command = "mysqldump --add-drop-table --result-file=/var/www/db-dump/${dbname}.sql --host=" . $hostname . " --user=" . $username . " --password=". $password . " " . $dbname;
$command = "mysqldump --opt --add-drop-table --host=" . $hostname . " --user=" . $username . " --password=" . $password . " " . $dbname;
if($result=system($command)){
$fp=fopen($dbname.'.sql','ab');
$fwrite($fp,$result);
$fclose($fp);
}
}
?>
我使用GitHub上的mysqldump-php。
"这是一个php版本的Linux mysqldump在终端'$ mysqldump -u username -p…"',没有依赖项,输出压缩和相同的默认值。
MySQLDump-PHP支持备份表结构、数据本身、视图和触发器。"