MySQL 无法通过phpmyadmin导入数据库文件过大

Can't import database through phpmyadmin file size too large

提问by Ricky Sharma

I have been trying to import Database through phpMyAdmin. My database file is a.sqland it's size is 1.2 GB I am trying to import this on local and phpMyAdmin is saying:

我一直在尝试通过 phpMyAdmin 导入数据库。我的数据库文件是a.sql1.2 GB 我试图在本地导入它，phpMyAdmin 说：

You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit.

您可能尝试上传过大的文件。有关解决此限制的方法，请参阅文档。

Please help I really need this to work.

请帮助我真的需要这个才能工作。

回答by himansu

For Upload large size data in using phpmyadmin Do following steps.

对于使用 phpmyadmin 上传大尺寸数据执行以下步骤。

1. Open php.ini file from C:\wamp\bin\apache\Apache2.4.4\bin Update following lines

   max_execution_time = 259200
   max_input_time = 259200
   memory_limit = 1000M
   upload_max_filesize = 750M
   post_max_size = 750M

   than after restart wamp server or restart all services Now Upload data using import function in phymyadmin. Apply second step if till not upload data.
   
2. open config.default.php file in c:\wamp\apps\phpmyadmin4.0.4\libraries (Open this file accoring to phpmyadmin version)
   Find $cfg['ExecTimeLimit'] = 300;Replace to $cfg['ExecTimeLimit'] = 0;
   Now you can upload data.

1. 从 C:\wamp\bin\apache\Apache2.4.4\bin 打开 php.ini 文件更新以下几行

   max_execution_time = 259200
   max_input_time = 259200
   memory_limit = 1000M
   upload_max_filesize = 750M
   post_max_size = 750M

   比重新启动 wamp 服务器或重新启动所有服务之后现在使用 phymyadmin 中的导入功能上传数据。如果直到不上传数据，则应用第二步。
   
2. 打开 c:\wamp\apps\phpmyadmin4.0.4\libraries 中的 config.default.php 文件（根据 phpmyadmin 版本打开此文件）
   找到$cfg['ExecTimeLimit'] = 300;Replace to $cfg['ExecTimeLimit'] = 0;
   Now 您可以上传数据。

You can also upload large size database using MySQL Console as below.

您还可以使用 MySQL 控制台上传大型数据库，如下所示。

1. Click on WampServer Icon -> MySQL -> MySQL Consol
2. Enter your database password like rootin popup
3. Select database name for insert data by writing command USE DATABASENAME
4. Then load source sql file as SOURCE C:\FOLDER\database.sql
5. Press enter for insert data.

1. 单击 WampServer 图标 -> MySQL -> MySQL 控制台
2. root在弹出窗口中输入您的数据库密码
3. 通过写入命令选择插入数据的数据库名称 USE DATABASENAME
4. 然后将源sql文件加载为 SOURCE C:\FOLDER\database.sql
5. 按回车键插入数据。

Note: You can't load a compressed database file e.g. database.sql.zipor database.sql.gz, you have to extract it first. Otherwise the console will just crash.

注意：您不能加载压缩的数据库文件，例如database.sql.zip或database.sql.gz，您必须先将其解压缩。否则控制台只会崩溃。

回答by himansu

Its due to PHP that has a file size restriction for uploads.

这是由于 PHP 对上传有文件大小限制。

If you have terminal/shell access then the above answers @Kyotoweb will work.

如果您有终端/外壳访问权限，那么上述答案@Kyotoweb 将起作用。

one way to get it done is that you create an .htaccess/ini file file to change PHP settings to get the sql file uploaded through PHPmyAdmin.

完成它的一种方法是创建一个 .htaccess/ini 文件文件来更改 PHP 设置以获取通过 PHPmyAdmin 上传的 sql 文件。

php_value upload_max_filesize 120M //file size
php_value post_max_size 120M
php_value max_execution_time 200
php_value max_input_time 200

Note you should remove this file after upload.

请注意，您应该在上传后删除此文件。

回答by Rishi Kulshreshtha

Here is what I've done:

这是我所做的：

1. Gone to my XAMPP installed directoy C:\xampp\php
2. Open the "Configuration Settings" file named php.iniusing your text editor, preferably you can also you Notepad for Windows machine.
3. Somewhere nearby on line no 886, please update it to upload_max_filesize = 128M
4. Similarly on line no 735 post_max_size = 128M
5. On line no 442 max_execution_time = 300
6. On line no 452 max_input_time = 60
7. Restart your Apache from either XAMPP Control Panel or using CMD in Windows https://stackoverflow.com/a/6723485/969092

1. 转到我的 XAMPP 安装目录 C:\xampp\php
2. 打开php.ini使用文本编辑器命名的“配置设置”文件，最好也可以使用 Windows 机器的记事本。
3. 886 号线附近某处，请将其更新为 upload_max_filesize = 128M
4. 同样在第 735 行 post_max_size = 128M
5. 在线编号 442 max_execution_time = 300
6. 在线编号 452 max_input_time = 60
7. 从 XAMPP 控制面板或在 Windows 中使用 CMD 重新启动 Apache https://stackoverflow.com/a/6723485/969092

Done!

完毕！

回答by kytwb

Use command line :

使用命令行：

mysql.exe -u USERNAME -p PASSWORD DATABASENAME < MYDATABASE.sql

where MYDATABASE.sql is your sql file.

其中 MYDATABASE.sql 是您的 sql 文件。

回答by ninesided

Another option that nobody here has mentioned yet is to do a staggered load of the database using a tool like BigDumpto work around the limit. It's a simple PHP script that loads a chunk of the database at a time before restarting itself and moving on the the next chunk.

这里没有人提到的另一个选项是使用BigDump 之类的工具对数据库进行交错加载以绕过限制。这是一个简单的 PHP 脚本，它一次加载一个数据库块，然后重新启动并继续下一个块。

回答by Anuja

1. Set the below values in php.inifile (C:\xampp\php\)

   • max_execution_time = 0
   • max_input_time=259200
   • memory_limit = 1000M
   • upload_max_filesize = 750M
   • post_max_size = 750M

2. Open config.default file(C:\xampp\phpMyAdmin\libraries\config.default) and set the value as below:

   • $cfg['ExecTimeLimit'] = 0;

3. Then open the config.inc file(C:\xampp\phpMyAdmin\config.inc). and paste below line:

   • $cfg['UploadDir'] = 'upload';

4. Go to phpMyAdmin(C:\xampp\phpMyAdmin) folder and create folder called uploadand paste your database to newly created upload folder (don't need to zip)

5. Lastly, go to phpMyAdmin and upload your db (Please select your database in drop-down)

1. 在php.ini文件 ( C:\xampp\php\) 中设置以下值

   • max_execution_time = 0
   • max_input_time=259200
   • memory_limit = 1000M
   • upload_max_filesize = 750M
   • post_max_size = 750M

2. 打开 config.default 文件（C:\xampp\phpMyAdmin\libraries\config.default）并设置如下值：

   • $cfg['ExecTimeLimit'] = 0;

3. 然后打开 config.inc 文件（C:\xampp\phpMyAdmin\config.inc）。并粘贴以下行：

   • $cfg['UploadDir'] = 'upload';

4. 转到 phpMyAdmin( C:\xampp\phpMyAdmin) 文件夹并创建名为的文件夹upload并将您的数据库粘贴到新创建的上传文件夹（不需要压缩）

5. 最后，转到 phpMyAdmin 并上传您的数据库（请在下拉列表中选择您的数据库）

1. That's all baby ! :)

6. 这就是所有宝贝！:)

*It takes lot of time.In my db(266mb) takes 50min to upload. So be patient ! *

*这需要很多时间。在我的 db(266mb) 中需要 50 分钟才能上传。所以要有耐心！*

回答by Hamid

for ubuntu 14.04 use this:

对于 ubuntu 14.04 使用这个：

1- open php.ini using gedit or nano:

1- 使用 gedit 或 nano 打开 php.ini：

须藤 gedit /etc/php5/apache2/php.ini

2- find 'upload_max_size' then replace this single line with these lines:

2- 找到 'upload_max_size' 然后用这些行替换这一行：

max_execution_time = 259200 max_input_time = 259200 memory_limit = 1000M upload_max_filesize = 750M post_max_size = 750M

3- save php.ini and close editor

3- 保存 php.ini 并关闭编辑器

4- restart apache2:

4-重启apache2：

须藤服务 apache2 重启

5- now open

5-现在开放

localhost://phpmyadmin/

本地主机://phpmyadmin/

在浏览器中上传你的database.sql

回答by Riad Hossain

If you are using MySQL in Xampp then do the steps below.

如果您在 Xampp 中使用 MySQL，请执行以下步骤。

Find the following in XAMPP control panel>Apach-Config> PHP (php.ini) file

在XAMPP控制面板>Apache-Config>PHP(php.ini)文件中找到以下内容

• post_max_size = 8M

• upload_max_filesize = 2M

• max_execution_time = 30
• ut_time = 60enter code herememory_limit = 8M

• post_max_size = 8M

• upload_max_filesize = 2M

• 最大执行时间 = 30
• ut_time = 60 内存限制enter code here= 8M

And change their sizes according to your need. I'm using these values

并根据您的需要更改它们的大小。我正在使用这些值

post_max_size = 30M
upload_max_filesize = 30M
max_execution_time = 4500
max_input_time = 4500
memory_limit = 850M

回答by Cindy Cullen

You can also try compressing (zipping) the file. It sometimes works for me if I can't get to php.ini for phpmyadmin. If zipping reduces the file size below the maximum file size, it just might work!

您也可以尝试压缩（压缩）文件。如果我无法访问 phpmyadmin 的 php.ini，它有时对我有用。如果压缩将文件大小减小到最大文件大小以下，它可能会起作用！

回答by Abdulla Nilam

You no need to edit php.inior any thing. I suggest best thing as Just use MySQL WorkBench.

你不需要编辑php.ini或任何东西。我建议最好的方法是使用MySQL WorkBench。

JUST FOLLOW THE STEPS.

只需按照步骤操作即可。

Install MySQL WorkBench 6.0

安装 MySQL WorkBench 6.0

And In "Navigation panel"(Left side) there is option call 'Data import' under "MANAGEMENT". Click that and [follow steps below]

在“导航面板”（左侧）中，“管理”下有选项调用“数据导入”。单击它并[按照以下步骤操作]

1. click Import Self-Contained File and choose your SQL file
2. Go to My Document and create folder call "dump"[simple].
3. now you ready to upload file. Click IMPORT Button on down.

1. 单击导入自包含文件并选择您的 SQL 文件
2. 转到我的文档并创建文件夹 call "dump"[simple]。
3. 现在您可以上传文件了。单击下方的导入按钮。