使用 .php 文件生成 MySQL 转储
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6750531/
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
Using a .php file to generate a MySQL dump
提问by Thomas Ward
Here's the information I have:
这是我掌握的信息：
I am working with a Linux based system using MySQL and PHP5. I need to be able to generate a mysqldumpfrom within a .php file, and then have that dump be stored in a file on the server in a location I would specify.
我正在使用基于 Linux 的系统使用 MySQL 和 PHP5。我需要能够mysqldump从 .php 文件中生成一个，然后将该转储存储在服务器上我指定位置的文件中。
As I'm a PHP nooblet, I'd like someone to give me some assistance, guidance, or code, that would do what I require. This would have to be run remotely from the Internet.
由于我是一个 PHP nooblet，我希望有人给我一些帮助、指导或代码，这将满足我的要求。这必须从 Internet 远程运行。
回答by Pascal MARTIN
You can use the exec()function to execute an external command.
您可以使用该exec()函数来执行外部命令。
Note: between shell_exec()and exec(), I would choose the second one, which doesn't return the output to the PHP script -- no need for the PHP script to get the whole SQL dump as a string : you only need it written to a file, and this can be done by the command itself.
注意：在shell_exec()和之间exec()，我会选择第二个，它不会将输出返回到 PHP 脚本——PHP 脚本不需要将整个 SQL 转储作为字符串获取：您只需要将其写入文件，这可以通过命令本身来完成。
That external command will :
该外部命令将：
- be a call to
mysqldump, with the right parameters, - and redirect the output to a file.
- 是一个电话
mysqldump，用正确的参数， - 并将输出重定向到一个文件。
For example :
例如 ：
mysqldump --user=... --password=... --host=... DB_NAME > /path/to/output/file.sql
Which means your PHP code would look like this :
这意味着您的 PHP 代码将如下所示：
exec('mysqldump --user=... --password=... --host=... DB_NAME > /path/to/output/file.sql');
Of course, up to you to use the right connection information, replacing the ...with those.
当然，取决于您使用正确的连接信息，替换为...那些。
回答by MajorLeo
If you want to create a backup to download it via the browser, you also can do this without using a file.
如果您想创建备份以通过浏览器下载它，您也可以在不使用文件的情况下执行此操作。
The php function passthru()will directly redirect the output of mysqldump to the browser. In this example it also will be zipped.
php 函数passthru()将直接将 mysqldump 的输出重定向到浏览器。在这个例子中，它也将被压缩。
Pro: You don't have to deal with temp files.
优点：您不必处理临时文件。
Con: Won't work on Windows. May have limits with huge datasets.
缺点：不适用于 Windows。可能对庞大的数据集有限制。
<?php
$DBUSER="user";
$DBPASSWD="password";
$DATABASE="user_db";
$filename = "backup-" . date("d-m-Y") . ".sql.gz";
$mime = "application/x-gzip";
header( "Content-Type: " . $mime );
header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
$cmd = "mysqldump -u $DBUSER --password=$DBPASSWD $DATABASE | gzip --best";
passthru( $cmd );
exit(0);
?>
回答by diego
Take a look here: https://github.com/ifsnop/mysqldump-php! It is a native solution written in php.
看看这里：https: //github.com/ifsnop/mysqldump-php！它是用 php 编写的本机解决方案。
You can install it using composer, and it is as easy as doing:
您可以使用composer安装它，就像这样做一样简单：
<?php
use Ifsnop\Mysqldump as IMysqldump;
try {
$dump = new IMysqldump\Mysqldump('database', 'username', 'password');
$dump->start('storage/work/dump.sql');
} catch (\Exception $e) {
echo 'mysqldump-php error: ' . $e->getMessage();
}
?>
It supports advanced users, with lots of options copied from the original mysqldump.
它支持高级用户，从原始 mysqldump 复制了许多选项。
All the options are explained at the github page, but more or less are auto-explicative:
所有选项都在 github 页面上进行了解释，但或多或少是自动解释的：
$dumpSettingsDefault = array(
'include-tables' => array(),
'exclude-tables' => array(),
'compress' => 'None',
'no-data' => false,
'add-drop-database' => false,
'add-drop-table' => false,
'single-transaction' => true,
'lock-tables' => false,
'add-locks' => true,
'extended-insert' => true,
'disable-foreign-keys-check' => false,
'where' => '',
'no-create-info' => false
);
回答by André Puel
Please reffer to the following link which contains a scriptlet that will help you: http://davidwalsh.name/backup-mysql-database-php
请参阅以下链接，其中包含可以帮助您的脚本：http: //davidwalsh.name/backup-mysql-database-php
Note:This script may contain bugs with NULL data types
注意：此脚本可能包含 NULL 数据类型的错误
回答by Constantin Galbenu
For security reasons, it's recommended to specify the password in a configuration file and not in the command (a user can execute a ps aux | grep mysqldumpand see the password).
出于安全原因，建议在配置文件中而不是在命令中指定密码（用户可以执行 aps aux | grep mysqldump并查看密码）。
//create a temporary file
$file = tempnam(sys_get_temp_dir(), 'mysqldump');
//store the configuration options
file_put_contents($file, "[mysqldump]
user={$user}
password=\"{$password}\"");
//execute the command and output the result
passthru("mysqldump --defaults-file=$file {$dbname}");
//delete the temporary file
unlink($file);
回答by ANTARA
Here you can find a comprehensive solution to dump mysql structure and data like in PMA (and without using exec, passthru etc.):
在这里你可以找到一个全面的解决方案来像 PMA 一样转储 mysql 结构和数据（并且不使用 exec、passthru 等）：
https://github.com/antarasi/MySQL-Dump-with-Foreign-keys
https://github.com/antarasi/MySQL-Dump-with-Foreign-keys
It is fork of dszymczuk project with my enhancements.
它是 dszymczuk 项目的分支，具有我的增强功能。
The usage is simple
用法很简单
<?php
//MySQL connection parameters
$dbhost = 'localhost';
$dbuser = 'dbuser';
$dbpsw = 'pass';
$dbname = 'dbname';
//Connects to mysql server
$connessione = @mysql_connect($dbhost,$dbuser,$dbpsw);
//Set encoding
mysql_query("SET CHARSET utf8");
mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");
//Includes class
require_once('FKMySQLDump.php');
//Creates a new instance of FKMySQLDump: it exports without compress and base-16 file
$dumper = new FKMySQLDump($dbname,'fk_dump.sql',false,false);
$params = array(
//'skip_structure' => TRUE,
//'skip_data' => TRUE,
);
//Make dump
$dumper->doFKDump($params);
?>
works like a charm :-)
奇迹般有效 ：-）
回答by charliefortune
As long as you are allowed to use exec(), you can execute shell commands through your PHP code.
只要允许您使用exec()，您就可以通过 PHP 代码执行 shell 命令。
So assuming you know how to write the mysqldump in the command line, i.e.
所以假设你知道如何在命令行中编写mysqldump，即
mysqldump -u [username] -p [database] > [database].sql
then you can use this as the parameter to exec() function.
那么您可以将其用作 exec() 函数的参数。
exec("mysqldump -u mysqluser -p my_database > my_database_dump.sql");
回答by Matías Cánepa
MajorLeo's answer point me in the right direction but it didn't worked for me. I've found this sitethat follows the same approach and did work.
MajorLeo 的回答为我指明了正确的方向，但对我没有用。我发现这个网站遵循相同的方法并且确实有效。
$dir = "path/to/file/";
$filename = "backup" . date("YmdHis") . ".sql.gz";
$db_host = "host";
$db_username = "username";
$db_password = "password";
$db_database = "database";
$cmd = "mysqldump -h {$db_host} -u {$db_username} --password={$db_password} {$db_database} | gzip > {$dir}{$filename}";
exec($cmd);
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"$filename\"");
passthru("cat {$dir}{$filename}");
I hope it helps someone else!
我希望它可以帮助别人！
回答by Fuad Hasan
global $wpdb;
$export_posts = $wpdb->prefix . 'export_posts';
$backupFile = $_GET['targetDir'].'export-gallery.sql';
$dbhost=DB_HOST;
$dbuser=DB_USER;
$dbpass=DB_PASSWORD;
$db=DB_NAME;
$path_to_mysqldump = "D:\xampp_5.6\mysql\bin";
$query= "D:\xampp_5.6\mysql\bin\mysqldump.exe -u$dbuser -p$dbpass $db $export_posts> $backupFile";
exec($query);
echo $query;回答by Emil M
Here's another native PHP based option: https://github.com/2createStudio/shuttle-export
这是另一个基于 PHP 的原生选项：https: //github.com/2createStudio/shuttle-export
