MySQL 如何将 1GB .sql 文件导入 WAMP/phpmyadmin

How to Import 1GB .sql file to WAMP/phpmyadmin

提问by Miuranga

I want to import over 1GB size sqlfile to MySQLdatabase in localhost WAMP/phpmyadmin. But phpmyadminUI doesn't allow to import such big file.

我想将超过 1GB 大小的sql文件导入MySQLlocalhost 中的数据库WAMP/phpmyadmin。但是phpmyadminUI 不允许导入这么大的文件。

What are the possible ways to do that such as any SQLquery to import .sql file ?

有什么可能的方法来做到这一点，例如任何SQL查询来导入 .sql 文件？

Thanks

谢谢

回答by Ram Sharma

I suspect you will be able to import 1 GB file through phpmyadmin But you can try by increasing the following value in php.ini and restart the wamp.

我怀疑您将能够通过 phpmyadmin 导入 1 GB 文件但是您可以尝试通过增加 php.ini 中的以下值并重新启动 wamp。

post_max_size=1280M
upload_max_filesize=1280M
max_execution_time = 300 //increase time as per your server requirement. 

You can also try below command from command prompt, your path may be different as per your MySQL installation.

您也可以从命令提示符尝试以下命令，您的路径可能因您的 MySQL 安装而异。

C:\wamp\bin\mysql\mysql5.5.24\bin\mysql.exe -u root -p db_name < C:\some_path\your_sql_file.sql

if you have much bigger db than increase the max_allowed_packetof mysql in my.inito avoid MySQL server gone awayerror, something like this

如果你有比增加max_allowed_packetmysql更大的数据库my.ini以避免MySQL server gone away错误，像这样

max_allowed_packet = 100M

回答by Ajay Bhayani

Step 1:Find the config.inc.phpfile located in the phpmyadmin directory. In my case it is located here:

步骤 1：找到config.inc.php位于 phpmyadmin 目录中的文件。就我而言，它位于此处：

C:\wamp\apps\phpmyadmin3.4.5\config.inc.php 

Note: phymyadmin3.4.5 folder name is different in different version of wamp

注意：phymyadmin3.4.5 文件夹名称在不同的wamp版本中是不同的

Step 2:Find the line with $cfg['UploadDir']on it and update it to:

第 2 步：找到其上的行并将$cfg['UploadDir']其更新为：

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

Step 3:Create a directory called ‘upload' within the phpmyadmin directory.

第 3 步：在 phpmyadmin 目录中创建一个名为“upload”的目录。

C:\wamp\apps\phpmyadmin3.2.0.1\upload\

Step 4:Copy and paste the large sql file into upload directory which you want importing to phymyadmin

第 4 步：将大 sql 文件复制并粘贴到您要导入到 phymyadmin 的上传目录中

Step 5:Select sql file from drop down list from phymyadmin to import.

第 5 步：从 phymyadmin 的下拉列表中选择要导入的 sql 文件。

回答by Bob

The values indicated by Ram Sharmamight need to be changed in Wamp alias configuration files instead.

Ram Sharma指示的值可能需要在 Wamp 别名配置文件中更改。

In <wamp_dir>/alias/phpmyadmin.conf, in the <Directory>section:

在<wamp_dir>/alias/phpmyadmin.conf，在<Directory>部分：

  php_admin_value upload_max_filesize 1280M
  php_admin_value post_max_size 1280M
  php_admin_value max_execution_time 1800

回答by Milan

Make sure to check the phpMyAdmin config file as well!On newer WAMP applications it is set to 128Mb by default. Even if you update php.ini to desired values you still need to update the phpmyadmin.conf!

请务必检查 phpMyAdmin 配置文件！在较新的 WAMP 应用程序中，默认设置为 128Mb。即使您将 php.ini 更新为所需的值，您仍然需要更新phpmyadmin.conf！

Sample path: C:\wamp64\alias\phpmyadmin.conf

示例路径：C:\wamp64\alias\phpmyadmin.conf

Or edit through your WAMP icon by: ->Apache -> Alias directories -> phpMyAdmin

或者通过以下方式编辑 WAMP 图标：->Apache -> Alias 目录 -> phpMyAdmin

回答by Yogesh patel

I also face the same problem and strangely changing the values in php.ini did not worked for me. So I found out one more solution which worked for me.

我也面临同样的问题，奇怪的是更改 php.ini 中的值对我不起作用。所以我找到了另一种对我有用的解决方案。

• Click your Wamp server icon -> MySql -> MySql console

• Once mysql console is open. Enter your mysql password. and give these commands.

  1. use user_database_name
  2. source c:/your/sql/path/filename.sql

• 单击您的 Wamp 服务器图标 -> MySql -> MySql 控制台

• 一旦 mysql 控制台打开。输入您的 mysql 密码。并给出这些命令。

  1. 使用 user_database_name
  2. 源 c:/your/sql/path/filename.sql

If you still have problems watch this video.

如果您仍有问题，请观看此视频。

回答by Tippa Raj

What are the possible ways to do that such as any SQL query to import .sql file ?

有什么可能的方法来做到这一点，例如任何 SQL 查询来导入 .sql 文件？

Try this

尝试这个

 mysql -u<user> -p<password> <database name> < /path/to/dump.sql

assuming dump.sqlis your 1 GB dump file

假设dump.sql是您的 1 GB 转储文件

回答by Marc Delisle

A phpMyAdmin feature called UploadDir permits to upload your file via another mechanism, then importing it from the server's file system. See http://docs.phpmyadmin.net/en/latest/faq.html#i-cannot-upload-big-dump-files-memory-http-or-timeout-problems.

一个名为 UploadDir 的 phpMyAdmin 功能允许通过另一种机制上传您的文件，然后从服务器的文件系统导入它。请参阅http://docs.phpmyadmin.net/en/latest/faq.html#i-cannot-upload-big-dump-files-memory-http-or-timeout-problems。

回答by Khubab Mazhar

If you will try to load such a large file through phpmyadmin then you would need to change upload_file_size in php.ini to your requirements and then after uploading you will have to revert it back. What will happen? If you would like to load a 3GB file. You will have to change those parameters in php.ini again.

如果您尝试通过 phpmyadmin 加载如此大的文件，那么您需要将 php.ini 中的 upload_file_size 更改为您的要求，然后在上传后您必须将其还原。会发生什么？如果你想加载一个 3GB 的文件。您将不得不再次更改 php.ini 中的这些参数。

The best solution to solve this issue to open command prompt in windows.

解决此问题的最佳解决方案是在 Windows 中打开命令提示符。

Find path of wamp mysql directory.

查找 wamp mysql 目录的路径。

Usually, it is C:/wamp64/bin/mysql/mysqlversion/bin/mysql.exe

通常，它是 C:/wamp64/bin/mysql/mysqlversion/bin/mysql.exe

Execute mysql -u root

执行 mysql -u root

You will be in mysql command prompt

您将在 mysql 命令提示符下

Switch database with use command.

使用 use 命令切换数据库。

mysql> use database_name
mysql> source [file_path]

In case of Windows, here is the example.

在 Windows 的情况下，这里是示例。

mysql> source C:/sqls/sql1GB.sql

That's it. If you will have a database over 10GB or 1000GB. This method will still work for you.

就是这样。如果您将拥有超过 10GB 或 1000GB 的数据库。这个方法对你仍然有效。

回答by user3556600

Before importing just make sure you have max_allowed_pack value set some thing large else you will get an error: Error 2006 MySQL server gone away.

在导入之前，请确保您已将 max_allowed_pa​​ck 值设置为较大的值，否则您将收到错误消息： Error 2006 MySQL server gone away.

Then try the command: mysql -u root -p database_name < file.sql

然后试试这个命令： mysql -u root -p database_name < file.sql

回答by Debasish Mishra

You can do it in following ways;

您可以通过以下方式进行；

1. You can go to control panel/cpanel and add host % It means now the database server can be accessed from your local machine. Now you can install and use MySQL Administrator or Navicat to import and export database with out using PHP-Myadmin, I used it several times to upload 200 MB to 500 MB of data with no issues

2. Use gzip, bzip2 compressions for exporting and importing. I am using PEA ZIP software (free) in Windows. Try to avoid Winrar and Winzip

3. Use MySQL Splitter that splits up the sql file into several parts. In my personal suggestion, Not recommended

4. Using PHP INI setting (dynamically change the max upload and max execution time) as already mentioned by other friends is fruitful but not always.

1. 您可以转到控制面板/cpanel 并添加主机 % 这意味着现在可以从本地计算机访问数据库服务器。现在您可以安装和使用 MySQL Administrator 或 Navicat 来导入和导出数据库，而无需使用 PHP-Myadmin，我多次使用它上传 200 MB 到 500 MB 的数据没有问题

2. 使用 gzip、bzip2 压缩进行导出和导入。我在 Windows 中使用 PEA ZIP 软件（免费）。尽量避免使用 Winrar 和 Winzip

3. 使用 MySQL Splitter 将 sql 文件分成几个部分。个人建议，不推荐

4. 使用其他朋友已经提到的 PHP INI 设置（动态更改最大上传和最大执行时间）很有成效，但并非总是如此。