phpMyAdmin: Can't import huge database file, a

问题:

I recently got a new computer, so I thought I'd move all my web projects over. Moving the files was easy, however, moving the database seems to be a bit harder. I exported ALL databases using phpMyAdmin, and saved it to localhost.sql. Then I tried to import it on my new computer also using phpMyAdmin, and I get the error:

No data to import. Either no filename was sent or the filesize exceeded the maximum allowed size. See FAQ 1.16. (This was translated from Swedish)

I took a look at the FAQ, as advised. And they mentioned a tool called BigDump. So I downloaded it, and after looking at the settings you need to run it I realised that I CAN'T USE THAT EITHER. Why? Because it requires a connection to one specific database. I'm trying to import MANY databases at once. So, no success there.

I also tried setting PHP's upload_max_filesize (and the other one's mentioned in the FAQ) to something like 999. That doesn't seem to work either for some reason. I did restart all services before I ran it again.

I should mention that I used XAMPP on my old computer, and I switched to WAMP on my new one. That shouldn't matter though, right? As both "packages" uses phpMyAdmin and apache.

Any suggestions on how to bypass this nightmare?

回答1:

HowTo

Make sure you change both *"post_max_size"* and *"upload_max_filesize"* in your "php.ini" (which is located where your "php.exe" is).

The following example allows you to upload and import 128MB sql files:

post_max_size=128M
upload_max_filesize=128M

Restart Apache and you're all set.

Alternatives

An alternative way to work around the problem is to use the command line. But it's a workaround, and as ugly as workarounds get:

C:\xampp\mysql\mysql.exe -u root -p db_name < C:\some_path\your_sql_file.sql

As you're not using the server but the command line, upload and POST sizes don't matter. That's why I call this a "workaround", since it doesn't actually solve your problem.

回答2:

Others are always trying to reconfigure the php.ini file but for me I think the best practice is use your MYSQL console because it really saves your time.

Here are the step by step guide to the MYSQL Console:

Open your MYSQL Console then run it.

Type:

mysql> use database_name;

mysql> source location_of_your_file;

location_of_your_file should look like C:\mydb.sql

so the command is mysql>source C:\mydb.sql;

This kind of importing sql dump is very helpful for BIG SQL FILE.

I copied my file mydb.sql to directory C: .It should be capital C: in order to run because it will give you a mysql error or syntax error

回答3:

in wamp server:

1. Open config.inc.php file in the path "C:\wamp\apps\phpmyadmin" and set/add this line. $cfg['UploadDir'] = ‘upload’;
   
2. Restart all services
   
3. Now create upload folder inside your phpmyadmin(C:\wamp\apps\phpmyadmin4.0.4\upload) folder and put your .sql file in upload folder.
   
4. Now open phpmyadmin in browser, go to import. You can see the file in dropdown just below browse button with that upload directory folder and files.
   
5. Select it and Go…

回答4:

Make changes in xampp\php\php.ini

Find:

post_max_size       = 8M
upload_max_filesize = 2M
max_execution_time  = 30
max_input_time      = 60
memory_limit        = 8M

Change to:

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

回答5:

You can import from the command line - something like mysql < databasedump.sql (depending on the OS).

回答6:

Tried this? http://daipratt.co.uk/importing-large-files-into-mysql-with-phpmyadmin/

回答7:

Follow this step to dump huge database:

1) Download php script BigDump.php from http://www.ozerov.de/bigdump/

2) Move your bigdump.php and your databasedump.sql in projects folder (htdocs or www).

3) open bigdump.php file and edit bellow section:

$db_server   = 'localhost';
$db_name     = '';
$db_username = '';
$db_password = ''; 

$filename           = '.sql';  

4) open bigdump.php in browser and click on import link.

回答8:

This will import your large sql file within few seconds:

e:\xampp\mysql\bin>mysql -u root -h localhost < verybigsqlfile.sql

Don't use phpmyadmin to import large sql files. If you are using mysql online, then take the mysql console access.

回答9:

compress sql file (only gzip, bzip2, zip) and import it. compressing test files like db csv file will reduce file size dramatically (~ 11 mb to 2 mb) and phpmyadmin import engine cand etect compressed data

回答10:

Modify the my.ini file

in the [mysqld] section add the following lines

wait_timeout=28800
interactive_timeout = 28800
max_allowed_packet=32M

Restart the mysql

open the dump.sql file add the following lines

use mydbname

Go to the mysql folder and run this command

mysql -u root -h localhost < dump.sql

回答11:

phpmyadmin has the ability to read from the harddrive as well - upload the dump and use this feature. However, I would strongly advise you to the from the console if possible. It's way faster as php doesn't have to process the file first and it gets loaded directly into the server.

回答12:

If you are having a very large database uploading though the web interface will be limited to HTTP maximum HTTP request size

Edit the PHP.ini to increase the upload limit

find this line

;;;;;;;;;;;;;;;;
; File Uploads ;
;;;;;;;;;;;;;;;;

; Whether to allow HTTP file uploads.
file_uploads = On

; Temporary directory for HTTP uploaded files (will use system default if not
; specified).
;upload_tmp_dir =

; Maximum allowed size for uploaded files.
upload_max_filesize = ??M **<--- change here ....**

Then restart the server

/sbin/service httpd restart

or use directadmin to restart the http service

回答13:

Use SQLYog for db operations it is the best choice for professional. By using SQLYog you can import nGB {n->finite_size} of sql db.

回答14:

I tried to change the allowed size, but that didn't fixed the problem.

However, there's a folder called "uploaddir", in my particular case, located in:

EasyPHP\modules\phpmyadmin\uploaddir

I put the SQL file there, and then went back to the "Import" tab of PHP My Admin. There's an option that allowed me to upload files that were already located in "uploaddir" folder; and they have no limit.

I'm using Windows and EasyPHP 12.1

回答15:

Simple solution is that just download & install "MySql Workbench" software, its gui based and easy to use.I have recently import 300mb size mysql database.

回答16:

I know this is old but.. I came up with this problem long ago. Either I used the console to execute dump sql file. Or I use adminer as an alternative to PHPmyAdmin.

回答17:

If you'r on linux, you can go to terminal and after connect to mysql and select database, simply type:

\. /path/to/database/database.sql