php 无需访问服务器或 phpMyADMIN 即可导出 SQL 表的简便方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/81934/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Easy way to export a SQL table without access to the server or phpMyADMIN
提问by Jrgns
I need a way to easily export and then import data in a MySQL table from a remote server to my home server. I don't have direct access to the server, and no utilities such as phpMyAdmin are installed. I do, however, have the ability to put PHP scripts on the server.
我需要一种方法来轻松地将 MySQL 表中的数据从远程服务器导出然后导入到我的家庭服务器。我没有直接访问服务器的权限，也没有安装诸如 phpMyAdmin 之类的实用程序。但是，我确实能够将 PHP 脚本放在服务器上。
How do I get at the data?
我如何获取数据？
I ask this question purely to record my way to do it
我问这个问题纯粹是为了记录我的做法
回答by lewis
You could use SQL for this:
您可以为此使用 SQL：
$file = 'backups/mytable.sql';
$result = mysql_query("SELECT * INTO OUTFILE '$file' FROM `##table##`");
Then just point a browser or FTP client at the directory/file (backups/mytable.sql). This is also a nice way to do incremental backups, given the filename a timestamp for example.
然后只需将浏览器或 FTP 客户端指向目录/文件 (backups/mytable.sql)。这也是进行增量备份的好方法，例如给定文件名一个时间戳。
To get it back in to your DataBase from that file you can use:
要从该文件将其恢复到您的数据库，您可以使用：
$file = 'backups/mytable.sql';
$result = mysql_query("LOAD DATA INFILE '$file' INTO TABLE `##table##`");
The other option is to use PHP to invoke a system command on the server and run 'mysqldump':
另一种选择是使用 PHP 在服务器上调用系统命令并运行“mysqldump”：
$file = 'backups/mytable.sql';
system("mysqldump --opt -h ##databaseserver## -u ##username## -p ##password## ##database | gzip > ".$file);
回答by Jrgns
I did it by exporting to CSV, and then importing with whatever utility is available. I quite like the use of the php://output stream.
我通过导出到 CSV，然后使用任何可用的实用程序导入来做到这一点。我非常喜欢使用 php://output 流。
$result = $db_con->query('SELECT * FROM `some_table`');
$fp = fopen('php://output', 'w');
if ($fp && $result) {
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="export.csv"');
while ($row = $result->fetch_array(MYSQLI_NUM)) {
fputcsv($fp, array_values($row));
}
die;
}
回答by Shinhan
You should also consider phpMinAdminwhich is only one file, so its easy to upload and setup.
您还应该考虑phpMinAdmin，它只有一个文件，因此易于上传和设置。
回答by T.Todua
WORKING SOLUTION(latest version at: Export.php+ Import.php)
工作解决方案（最新版本： Export.php+ Import.php）
EXPORT_TABLES("localhost","user","pass","db_name");
CODE:
代码：
//https://github.com/tazotodua/useful-php-scripts
function EXPORT_TABLES($host,$user,$pass,$name, $tables=false, $backup_name=false ){
$mysqli = new mysqli($host,$user,$pass,$name); $mysqli->select_db($name); $mysqli->query("SET NAMES 'utf8'");
$queryTables = $mysqli->query('SHOW TABLES'); while($row = $queryTables->fetch_row()) { $target_tables[] = $row[0]; } if($tables !== false) { $target_tables = array_intersect( $target_tables, $tables); }
foreach($target_tables as $table){
$result = $mysqli->query('SELECT * FROM '.$table); $fields_amount=$result->field_count; $rows_num=$mysqli->affected_rows; $res = $mysqli->query('SHOW CREATE TABLE '.$table); $TableMLine=$res->fetch_row();
$content = (!isset($content) ? '' : $content) . "\n\n".$TableMLine[1].";\n\n";
for ($i = 0, $st_counter = 0; $i < $fields_amount; $i++, $st_counter=0) {
while($row = $result->fetch_row()) { //when started (and every after 100 command cycle):
if ($st_counter%100 == 0 || $st_counter == 0 ) {$content .= "\nINSERT INTO ".$table." VALUES";}
$content .= "\n(";
for($j=0; $j<$fields_amount; $j++) { $row[$j] = str_replace("\n","\n", addslashes($row[$j]) ); if (isset($row[$j])){$content .= '"'.$row[$j].'"' ; }else {$content .= '""';} if ($j<($fields_amount-1)){$content.= ',';} }
$content .=")";
//every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) {$content .= ";";} else {$content .= ",";} $st_counter=$st_counter+1;
}
} $content .="\n\n\n";
}
$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";
header('Content-Type: application/octet-stream'); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"".$backup_name."\""); echo $content; exit;
}
回答by Lasar
If you have FTP/SFTP access you could just go ahead and upload phpMyAdmin yourself.
如果您有 FTP/SFTP 访问权限，您可以自行上传 phpMyAdmin。
I'm using this little package to make automated mysql backups from a server I only have FTP access to:
http://www.taw24.de/download/pafiledb.php?PHPSESSID=b48001ea004aacd86f5643a72feb2829&action=viewfile&fid=43&id=1
The site is in german but the download has some english documentation as well.
我正在使用这个小程序包从我只能通过 FTP 访问的服务器进行自动 mysql 备份：
http://www.taw24.de/download/pafiledb.php?PHPSESSID=b48001ea004aacd86f5643a72feb2829&action=viewfile&fid=43& id=1
该站点是用德语，但下载也有一些英文文档。
A quick google also turns up this, but I have not used it myself:
http://snipplr.com/view/173/mysql-dump/
一个快速的谷歌也发现了这一点，但我自己没有使用过：http:
//snipplr.com/view/173/mysql-dump/
回答by DreamWerx
You might consider looking at: http://www.webyog.comThis is a great GUI admin tool, and they have a really neat HTTP-Tunneling feature (I'm not sure if this is only in enterprise which costs a few bucks).
您可能会考虑查看：http: //www.webyog.com这是一个很棒的 GUI 管理工具，并且它们具有非常简洁的 HTTP 隧道功能（我不确定这是否仅在企业中花费几美元）。
Basically you upload a script they provide into your webspace (php script) and point sqlyog manager to it and you can access the database(s). It uses this script to tunnel/proxy the requests/queries between your home client and the server.
基本上，您将他们提供的脚本上传到您的网站空间（php 脚本）并将 sqlyog 管理器指向它，您就可以访问数据库了。它使用此脚本在您的家庭客户端和服务器之间建立隧道/代理请求/查询。
I know at least 1 person who uses this method with great results.
我知道至少有 1 个人使用这种方法取得了很好的效果。
回答by Vali Munteanu
Here is a PHPscript I made which will backup all tables in your database. It is based on this http://davidwalsh.name/backup-mysql-database-phpwith some improvements. First of all it will correctly set up foreign key restrictions.
这是PHP我制作的脚本，它将备份数据库中的所有表。它基于此http://davidwalsh.name/backup-mysql-database-php并进行了一些改进。首先它会正确设置foreign key restrictions。
In my set up the script will run on a certain day of the week, let's say Monday. In case it did not run on Monday, it will still run on Tuesday (for example), creating the .sqlfile with the date of previous Monday, when it was supposed to run. It will erase .sqlfile from 4 weeks ago, so it always keeps the last 4 backups. Here's the code:
在我的设置中，脚本将在一周中的某一天运行，比如说星期一。如果它没有在星期一运行，它仍然会在星期二运行（例如），创建.sql文件的日期是前一个星期一，当它应该运行时。它将删除.sql4 周前的文件，因此它始终保留最近的 4 个备份。这是代码：
<?php
backup_tables();
// backup all tables in db
function backup_tables()
{
$day_of_backup = 'Monday'; //possible values: `Monday` `Tuesday` `Wednesday` `Thursday` `Friday` `Saturday` `Sunday`
$backup_path = 'databases/'; //make sure it ends with "/"
$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$db_name = 'movies_database_1';
//set the correct date for filename
if (date('l') == $day_of_backup) {
$date = date("Y-m-d");
} else {
//set $date to the date when last backup had to occur
$datetime1 = date_create($day_of_backup);
$date = date("Y-m-d", strtotime($day_of_backup.' -7 days'));
}
if (!file_exists($backup_path.$date.'-backup'.'.sql')) {
//connect to db
$link = mysqli_connect($db_host,$db_user,$db_pass);
mysqli_set_charset($link,'utf8');
mysqli_select_db($link,$db_name);
//get all of the tables
$tables = array();
$result = mysqli_query($link, 'SHOW TABLES');
while($row = mysqli_fetch_row($result))
{
$tables[] = $row[0];
}
//disable foreign keys (to avoid errors)
$return = 'SET FOREIGN_KEY_CHECKS=0;' . "\r\n";
$return.= 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";' . "\r\n";
$return.= 'SET AUTOCOMMIT=0;' . "\r\n";
$return.= 'START TRANSACTION;' . "\r\n";
//cycle through
foreach($tables as $table)
{
$result = mysqli_query($link, 'SELECT * FROM '.$table);
$num_fields = mysqli_num_fields($result);
$num_rows = mysqli_num_rows($result);
$i_row = 0;
//$return.= 'DROP TABLE '.$table.';';
$row2 = mysqli_fetch_row(mysqli_query($link,'SHOW CREATE TABLE '.$table));
$return.= "\n\n".$row2[1].";\n\n";
if ($num_rows !== 0) {
$row3 = mysqli_fetch_fields($result);
$return.= 'INSERT INTO '.$table.'( ';
foreach ($row3 as $th)
{
$return.= '`'.$th->name.'`, ';
}
$return = substr($return, 0, -2);
$return.= ' ) VALUES';
for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysqli_fetch_row($result))
{
$return.="\n(";
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = preg_replace("#\n#","\n",$row[$j]);
if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
if ($j<($num_fields-1)) { $return.= ','; }
}
if (++$i_row == $num_rows) {
$return.= ");"; // last row
} else {
$return.= "),"; // not last row
}
}
}
}
$return.="\n\n\n";
}
// enable foreign keys
$return .= 'SET FOREIGN_KEY_CHECKS=1;' . "\r\n";
$return.= 'COMMIT;';
//set file path
if (!is_dir($backup_path)) {
mkdir($backup_path, 0755, true);
}
//delete old file
$old_date = date("Y-m-d", strtotime('-4 weeks', strtotime($date)));
$old_file = $backup_path.$old_date.'-backup'.'.sql';
if (file_exists($old_file)) unlink($old_file);
//save file
$handle = fopen($backup_path.$date.'-backup'.'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}
}
?>
回答by Teepeemm
I found that I didn't have enough permissions for SELECT * INTO OUTFILE.
But I was able to use enough php (iterating and imploding) to really cut down on the nested loops compared to other approaches.
我发现我没有足够的权限SELECT * INTO OUTFILE。但是与其他方法相比，我能够使用足够的 php（迭代和内爆）来真正减少嵌套循环。
$dbfile = tempnam(sys_get_temp_dir(),'sql');
// array_chunk, but for an iterable
function iter_chunk($iterable,$chunksize) {
foreach ( $iterable as $item ) {
$ret[] = $item;
if ( count($ret) >= $chunksize ) {
yield $ret;
$ret = array();
}
}
if ( count($ret) > 0 ) {
yield $ret;
}
}
function tupleFromArray($assocArr) {
return '('.implode(',',array_map(function($val) {
return '"'.addslashes($val).'"';
},array_values($assocArr))).')';
}
file_put_contents($dbfile,"\n-- Table $table --\n/*\n");
$description = $db->query("DESCRIBE `$table`");
$row = $description->fetch_assoc();
file_put_contents($dbfile,implode("\t",array_keys($row))."\n",FILE_APPEND);
foreach ( $description as $row ) {
file_put_contents($dbfile,implode("\t",array_values($row))."\n",FILE_APPEND);
}
file_put_contents($dbfile,"*/\n",FILE_APPEND);
file_put_contents($dbfile,"DROP TABLE IF EXISTS `$table`;\n",FILE_APPEND);
file_put_contents($dbfile,array_pop($db->query("SHOW CREATE TABLE `$table`")->fetch_row()),FILE_APPEND);
$ret = $db->query("SELECT * FROM `$table`");
$chunkedData = iter_chunk($ret,1023);
foreach ( $chunkedData as $chunk ) {
file_put_contents($dbfile, "\n\nINSERT INTO `$table` VALUES " . implode(',',array_map('tupleFromArray',$chunk)) . ";\n", FILE_APPEND );
}
readfile($dbfile);
unlink($dbfile);
If you have tables with foreign keys, this approach can still work if you drop
them in the correct order and then recreate them in the correct (reverse) order.
The CREATEstatement will create the foreign key dependency for you.
Go through SELECT * FROM information_schema.referential_constraintsto
determine that order.
如果您有带有外键的表，如果您以正确的顺序删除它们，然后以正确的（反向）顺序重新创建它们，这种方法仍然可以工作。该CREATE语句将为您创建外键依赖项。通过SELECT * FROM information_schema.referential_constraints以确定该顺序。
If your foreign keys have a circular dependency, then there is no possible order to drop or create. In that case, you might be able to follow the lead of phpMyAdmin, which creates all of the foreign keys at the end. But this also means that you have to adjust the CREATEstatements.
如果您的外键具有循环依赖关系，则无法删除或创建顺序。在这种情况下，您可能可以按照 phpMyAdmin 的指导，在最后创建所有外键。但这也意味着您必须调整CREATE报表。
回答by SeanDowney
I use mysqldump via the command line :
我通过命令行使用 mysqldump ：
exec("mysqldump sourceDatabase -uUsername -p'password' > outputFilename.sql");
Then you just download the resulting file and your done.
然后您只需下载生成的文件并完成。
