PHP：导入 .SQL 数据文件时的最长执行时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22817670/
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
PHP: maximum execution time when importing .SQL data file
提问by EssexPN
I am trying to import a large .sql data file using phpMyAdmin in XAMPP. However this is taking a lot of time and I keep getting:
我正在尝试在 XAMPP 中使用 phpMyAdmin 导入一个大型 .sql 数据文件。然而，这需要很多时间，我不断得到：
Fatal error: Maximum execution time of 300 seconds exceeded in C:\xampp\phpMyAdmin\libraries\dbi\DBIMysqli.class.php on line 285
致命错误：第 285 行的 C:\xampp\phpMyAdmin\libraries\dbi\DBIMysqli.class.php 中超出了 300 秒的最大执行时间
And the file is about 1.2 million lines long.
该文件大约有 120 万行。
The file is about 30MB big, so it is not that big. I don't really understand why it is taking so long.
该文件大约有 30MB 大，所以它不是那么大。我真的不明白为什么要花这么长时间。
;;;;;;;;;;;;;;;;;;;
; Resource Limits ;
;;;;;;;;;;;;;;;;;;;
; Maximum execution time of each script, in seconds
; http://php.net/max-execution-time
; Note: This directive is hardcoded to 0 for the CLI SAPI
max_execution_time=30000
; Maximum amount of time each script may spend parsing request data. It's a good
; idea to limit this time on productions servers in order to eliminate unexpectedly
; long running scripts.
; Note: This directive is hardcoded to -1 for the CLI SAPI
; Default Value: -1 (Unlimited)
; Development Value: 60 (60 seconds)
; Production Value: 60 (60 seconds)
; http://php.net/max-input-time
max_input_time=60
; Maximum input variable nesting level
; http://php.net/max-input-nesting-level
;max_input_nesting_level = 64
; How many GET/POST/COOKIE input variables may be accepted
; max_input_vars = 1000
; Maximum amount of memory a script may consume (128MB)
; http://php.net/memory-limit
memory_limit=200M
The is the config file for php.ini in xampp, for some reason i still get
这是 xampp 中 php.ini 的配置文件，出于某种原因，我仍然得到
Fatal error: Maximum execution time of 300 seconds exceeded in C:\xampp\phpMyAdmin\libraries\dbi\DBIMysqli.class.php on line 285.
致命错误：在 C:\xampp\phpMyAdmin\libraries\dbi\DBIMysqli.class.php 中，第 285 行超出了 300 秒的最大执行时间。
回答by Randell
There's a configuration variable within the phpMyAdmin directory that you can find in libraries\config.default.phpcalled $cfg['ExecTimeLimit']that you can set to whatever maximum execution time you need.
在 phpMyAdmin 目录中有一个配置变量，您可以在libraries\config.default.php调用$cfg['ExecTimeLimit']中找到它，您可以将其设置为您需要的任何最大执行时间。
回答by Umesh Patil
Well, to get rid of this you need to set phpMyadmin variable to either 0 that is unlimited or whichever value in seconds you find suitable for your needs. Or you could always use CLI(command line interface) to not even get such errors(For which you would like to take a look at this link.
好吧，要摆脱这种情况，您需要将 phpMyadmin 变量设置为 0（无限制）或您认为适合您需要的任何值（以秒为单位）。或者您可以始终使用 CLI（命令行界面）甚至不会出现此类错误（您想查看此链接。
Now about the error here, first on the safe side make sure you have set PHP parameters properly so that you can upload large files and can use maximum execution time from that end. If not, go ahead and set below three parameters from php.ini file,
现在关于这里的错误，首先为了安全起见，请确保您已正确设置 PHP 参数，以便您可以上传大文件并可以使用最长的执行时间。如果没有，请继续从 php.ini 文件中设置以下三个参数，
- max_execution_time=3000000 (Set this as per your req)
- post_max_size=4096M
- upload_max_filesize=4096M
- max_execution_time=3000000（根据您的要求设置）
- post_max_size=4096M
- upload_max_filesize=4096M
Once that's done get back to finding phpMyadmin config file named something like "config.default.php". On XAMPP you will find it under "C:\xampp\phpMyAdmin\libraries" folder. Open the file called config.default.phpand set :
完成后，返回查找名为“config.default.php”的 phpMyadmin 配置文件。在 XAMPP 上，您会在“C:\xampp\phpMyAdmin\libraries”文件夹下找到它。打开调用的文件config.default.php并设置：
$cfg['ExecTimeLimit'] = 0;
Once set, restart your MySQL and Apache and go import your database.
设置后，重新启动 MySQL 和 Apache 并导入数据库。
Enjoy... :)
享受... ：）
回答by GordonM
You're trying to import a huge dataset via a web interface.
您正在尝试通过 Web 界面导入庞大的数据集。
By default PHP scripts run in the context of a web server have a maximum execution time limit because you don't want a single errant PHP script tying up the entire server and causing a denial of service.
默认情况下，在 Web 服务器上下文中运行的 PHP 脚本具有最长执行时间限制，因为您不希望单个错误的 PHP 脚本占用整个服务器并导致拒绝服务。
For that reason your import is failing. PHPMyAdmin is a web application and is hitting the limit imposed by PHP.
因此，您的导入失败。PHPMyAdmin 是一个 Web 应用程序，正在达到 PHP 施加的限制。
You could try raising the limit but that limit exists for a good reason so that's not advisable. Running a script that is going to take a very long time to execute in a web server is a very bad idea.
您可以尝试提高限制，但该限制存在是有充分理由的，因此不可取。在 Web 服务器中运行需要很长时间才能执行的脚本是一个非常糟糕的主意。
PHPMyAdmin isn't really intended for heavy duty jobs like this, it's meant for day to day housekeeping tasks and troubleshooting.
PHPMyAdmin 并不是真正适用于像这样的繁重工作，它适用于日常管理任务和故障排除。
Your best option is to use the proper tools for the job, such as the mysql commandline tools. Assuming your file is an SQL dump then you can try running the following from the commandline:
最好的选择是使用合适的工具来完成这项工作，例如 mysql 命令行工具。假设您的文件是 SQL 转储，那么您可以尝试从命令行运行以下命令：
mysql -u(your user name here) -p(your password here) -h(your sql server name here) (db name here) < /path/to/your/sql/dump.sql
Or if you aren't comfortable with commandline tools then something like SQLYog (for Windows), Sequel Pro (for Mac), etc may be more suitable for running an import job
或者，如果您对命令行工具不满意，那么 SQLYog（适用于 Windows）、Sequel Pro（适用于 Mac）等可能更适合运行导入作业
回答by William
This worked for me.
If you got Maximum execution time 300 exceeded in DBIMysqli.class.phpfile. Open the following file in text editor
C:\xampp\phpMyAdmin\libraries\config.default.phpthen
search the following line of code:
这对我有用。如果您在DBIMysqli.class.php文件中超过了最大执行时间 300 。在文本编辑器中打开以下文件，
C:\xampp\phpMyAdmin\libraries\config.default.php然后搜索以下代码行：
$cfg[‘ExecTimeLimit'] = 300;
and change value 300to 900.
并将值更改300为900.
回答by RAAJA KING
Set Only 3 Parameters from php.inifile of your server
从php.ini您的服务器文件中只设置 3 个参数
A. max_execution_time = 3000000 (Set as per your requirment)
B. post_max_size = 4096M
C. upload_max_filesize = 4096M
A. max_execution_time = 3000000（根据您的要求设置）
B. post_max_size = 4096M
C. upload_max_filesize = 4096M
Edit C:\xampp\phpMyAdmin\libraries\config.default.phpPage
编辑C:\xampp\phpMyAdmin\libraries\config.default.php页面
$cfg['ExecTimeLimit'] = 0;
After all set, restart your server and import again your database.
设置完成后，重新启动服务器并再次导入数据库。
Done
完毕
回答by Tejas Damre
Simply set $cfg['ExecTimeLimit'] = 0; In xampp/phpMyAdmin/libraries/config.default.php.
只需设置 $cfg['ExecTimeLimit'] = 0; 在 xampp/phpMyAdmin/libraries/config.default.php 中。
Maximum execution time in seconds (0 for no limit).
以秒为单位的最大执行时间（0 表示没有限制）。
And make this below changes in php.inifile as per file size.
并php.ini根据文件大小在文件中进行以下更改。
post_max_size = 600M
upload_max_filesize = 500M
max_execution_time = 5000
max_input_time = 5000
memory_limit = 600M
But make sure 'post_max_size' and 'memory_limit' should be more than upload_max_filesize.
但确保'post_max_size' 和'memory_limit' 应该大于upload_max_filesize。
**Note - Don't forget to restart your server.
**注意 - 不要忘记重新启动服务器。
回答by Isaac Bennetch
Is it a .sql file or is it compressed (.zip, .gz, etc)? Compressed formats sometimes require more PHP resources so you could try uncompressing it before uploading.
它是 .sql 文件还是压缩文件（.zip、.gz 等）？压缩格式有时需要更多 PHP 资源，因此您可以在上传前尝试解压缩。
However, there are other methods you can try also. If you have command-line access, just upload the file and import with the command line client mysql(once at the mysql>prompt, use databasename;then source file.sql).
但是，您也可以尝试其他方法。如果您有命令行访问权限，只需上传文件并使用命令行客户端导入mysql（一旦出现mysql>提示，use databasename;然后source file.sql）。
Otherwise you can use the phpMyAdmin "UploadDir" feature to put the file on the server and have it appear within phpMyAdmin without having to also upload it from your local machine.
否则，您可以使用 phpMyAdmin 的“UploadDir”功能将文件放在服务器上，并将其显示在 phpMyAdmin 中，而无需从本地计算机上传。
This linkhas information on using UploadDir and this onehas some more tips and methods.
回答by mp.hamid
you must change php_admin_value max_execution_timein your Alias config (\XAMPP\alias\phpmyadmin.conf)
您必须更改php_admin_value max_execution_time别名配置(\XAMPP\alias\phpmyadmin.conf)
answer is here: WAMPServer phpMyadmin Maximum execution time of 360 seconds exceeded
回答by António Almeida
After trying many things with no success, I've managed to get SSH access to the server, and import my 80Mb database with a command line, instead of phpMyAdmin. Here is the command:
在尝试了很多没有成功的事情之后，我设法获得了对服务器的 SSH 访问，并使用命令行而不是 phpMyAdmin 导入了我的 80Mb 数据库。这是命令：
mysql -u root -p -D mydatabase -o < mydatabase.sql
It's much easier to import big databases, if you are running xammp on windows, the path for mysql.exeis C:\xampp\mysql\bin\mysql.exe
导入大数据库要容易得多，如果你在windows上运行xammp，mysql.exe的路径是C:\xampp\mysql\bin\mysql.exe
回答by guido _nhcol.com.br_
- Never change original config.default.php file.
- Changing general executing time in php.ini has no effect on phpmyadmin scripts.
- Use a new config.inc.php or the config.sample.inc.php provided in the /phpMyAdmin folder instead.
- You can set
$cfg[‘ExecTimeLimit'] = 0;means endless execution in the config.inc.php as recommended above. Be aware this is not a "normal" ini file. Its a php script, so you need a open<?phpat the beginning of that file. - But most important: Do not use this procedure at all!phpmyadmin is okay for small database but not for huge databases with several MB or GB.
- 永远不要更改原始的 config.default.php 文件。
- 更改 php.ini 中的一般执行时间对 phpmyadmin 脚本没有影响。
- 使用新的 config.inc.php 或 /phpMyAdmin 文件夹中提供的 config.sample.inc.php。
- 您可以
$cfg[‘ExecTimeLimit'] = 0;按照上面的建议在 config.inc.php 中设置意味着无限执行。请注意，这不是“正常”的 ini 文件。它是一个 php 脚本，因此您需要<?php在该文件的开头打开。 - 但最重要的是：根本不要使用此程序！phpmyadmin 适用于小型数据库，但不适用于具有几 MB 或 GB 的大型数据库。
You have other tools on a server to handle the import.
您在服务器上有其他工具来处理导入。
a) If you have a server admin system like Plesk, use there database import tool.
a) 如果您有像 Plesk 这样的服务器管理系统，请使用那里的数据库导入工具。
b) use ssh commands to make database dump or to write databases directly in mysql via ssh. Commands below.
b) 使用 ssh 命令进行数据库转储或通过 ssh 直接在 mysql 中写入数据库。下面的命令。
Create a database dump:
创建数据库转储：
mysqldump DBname --add-drop-table -h DBhostname -u DBusername -pPASSWORD > databasefile.sql
Write a database to mysql:
写一个数据库到mysql：
mysql -h rdbms -u DBusername -pPASSWORD DBname < databasefile.sql
