phpMyAdmin: Can't import huge database file, any suggestions?

Question

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?

You made upload_max_size to just 999 or 999M?, just wanted to confirm. — Zimbabao, Feb 19 '11 at 14:24
Please use phpinfo (http://help.rackspacecloud.com/article.php?id=082) and verify that your changes in php.ini actually took place. May be you changed the wrong php.ini (and you can see the path of the php.ini in effect from phpinfo also). Secondly, if you make any syntax error in php.ini file, the configuration set there will be ignored and default configuration will be applied (which AFAIK sets upload limite to 2MB) — Sarwar Erfan, Feb 19 '11 at 14:36
@Sawar; why the h*ll are there several php.ini's? The issue was i changed the wrong file. I can import it now, however i'm facing another problem now.`Access denied for user 'root'@'localhost' to database 'information_schema'`. Half of the datbases were importet, but it got stuck there. — qwerty, Feb 19 '11 at 14:44
@qwerty: WAMP is not a single software. They took apache, mysql, php (and other tools like phpMyAdmins) and combined. They left the original php.ini that came with php intact, created another php.ini in the apache directory to use for WAMP. Now, you can create another php.ini for your IIS and use single installation of php in both IIS and apache (with different configs!) You should NOT had exported the information_schema DB from the old server. This DB contains metadata about server and other DB, tables etc.When you import the data,metadata is created in new server.So, reexport without this DB — Sarwar Erfan, Feb 19 '11 at 18:32

score 53 · Answer 1 · 2012-01-03T23:45:16.120

53

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.

edited Jan 03 '12 at 23:45

answered Jan 03 '12 at 23:40

2

Good answer (should be accepted by OP): the command line approach worked for me but make sure that the mysql max_allowed_packet setting (in my.cnf file or my.ini file in mysql settings folder on your system) is larger than the size of the .sql file that you want to import/upload. For example my .sql was about 250Mb, so I set max_allowed_packet to be 1024M (well above that) just to be sure! Presumably max_allowed_packet would also apply to using phpmyadmin because although you must set post_max_size and upload_max_filesize, you may still get an error if max_allowed_packet is not also set. – therobyouknow Jul 06 '12 at 09:45
1

If you see "mysql server has gone away" with error code 2006 then this can be caused by max_allowed_packet being smaller than the file you want to import/upload - so my above comment should remedy this for you. – therobyouknow Jul 06 '12 at 09:50

Robert Anthony S. Tribiana · Answer 2 · 2018-11-29T03:51:19.500

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

score 9 · Answer 3 · edited Apr 08 '14 at 06:59

9

in wamp server:

Open config.inc.php file in the path "C:\wamp\apps\phpmyadmin" and set/add this line. $cfg['UploadDir'] = ‘upload’;
Restart all services
Now create upload folder inside your phpmyadmin(C:\wamp\apps\phpmyadmin4.0.4\upload) folder and put your .sql file in upload folder.
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.
Select it and Go…

edited Apr 08 '14 at 06:59

Code Lღver

15,573
16
56
75

answered Apr 08 '14 at 06:41

user3509697

91
1
1

this works for me, I don't know why it is not working the other way. – Karim Samir May 01 '15 at 23:51
didn't know about this function, very useful when on a shared hosting without permission to change PHP settings or have shell access. – Inserve Jan 08 '16 at 21:43

score 8 · Answer 4 · edited Nov 21 '13 at 09:16

8

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

edited Nov 21 '13 at 09:16

DB5

13,553
7
66
71

answered Nov 21 '13 at 08:54

Ramya

151
1
4
6

score 3 · Answer 5 · answered Feb 19 '11 at 14:23

3

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

answered Feb 19 '11 at 14:23

awm

6,526
25
24

I thought of that aswell, however, i am no commandline-ninja. How do i specify in which folder the file is? Would it work if i just moved it to the same folder as the mysql.exe file? Thanks! – qwerty Feb 19 '11 at 14:28
You need have the file in your current working directory - or use the full pathname on the command line. (Sorry... I run linux; don't know the exact commands on windoze.) – awm Feb 19 '11 at 14:36
Alright, thanks! I got phpMyAdmin to work though, i edited the wrong php.ini file. (kind of embarassing) Still very good to know though, thanks! – qwerty Feb 19 '11 at 14:46

score 1 · Answer 6 · answered Sep 13 '13 at 07:05

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.

score 1 · Answer 7 · edited Sep 20 '13 at 12:13

1

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.

edited Sep 20 '13 at 12:13

jam

3,640
5
34
50

answered Sep 20 '13 at 11:48

K.K.Bindal

11
1

@CodeWithCoffee that's because you must select a database before run commands on it: use databasenamehere – Marcelo Agimóvel Dec 19 '17 at 15:12

score 1 · Answer 8 · answered Jan 24 '14 at 16:25

1

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

answered Jan 24 '14 at 16:25

Prasad.CH

492
1
5
25

score 1 · Answer 9 · answered Jul 13 '15 at 09:22

1

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.

answered Jul 13 '15 at 09:22

reignsly

502
4
10

score 1 · Answer 10 · answered Nov 02 '16 at 06:22

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

score 0 · Answer 11 · answered Nov 04 '12 at 14:04

0

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.

answered Nov 04 '12 at 14:04

PHP Ferrari

15,754
27
83
149

score 0 · Answer 12 · answered Mar 22 '13 at 15:18

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

score 0 · Answer 13 · answered May 13 '15 at 12:53

0

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.

answered May 13 '15 at 12:53

Muhammad Zeshan Ghafoor

303
5
14

score 0 · Answer 14 · answered Dec 19 '17 at 15:14

0

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

answered Dec 19 '17 at 15:14

Marcelo Agimóvel

1,668
2
20
25

score 0 · Answer 15 · answered Feb 19 '11 at 14:23

0

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.

answered Feb 19 '11 at 14:23

Femaref

60,705
7
138
176

That's what i did. I'm running WAMP locally now, so just tried to use phpMyAdmin's upload funtion to import it. The console sounds like a good idea, however, i'm not a ninja in that area. Read awm's answer. – qwerty Feb 19 '11 at 14:30

Sudantha · Answer 16 · 2011-02-19T14:46:48.557

0

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

edited Feb 19 '11 at 14:46

answered Feb 19 '11 at 14:24

Sudantha

15,684
43
105
161

Would that be the "post_max_size" setting? I changed that aswell. – qwerty Feb 19 '11 at 14:31
1

And thinking about it, it should accept this file anyway. I mean, it's not a huge file, it's just a huge database. The filsize is about 4 MB. – qwerty Feb 19 '11 at 14:32
(Max: 128 MiB) PHP myadmin uploader says check it – Sudantha Feb 19 '11 at 14:35
check this generate the SQL file and execute queries little by little but will not be really practical ! – Sudantha Feb 19 '11 at 14:36
Alright, mine actually says 2048 KiB. (that's 2 MB, right?) What setting do i have to change? – qwerty Feb 19 '11 at 14:38
I got it working, but i didn't need to change the upload_max_filesize. phpMyAdmin uses a function that splits the import into pieces when the filesize is near the limit. – qwerty Feb 19 '11 at 15:02

score -1 · Answer 17 · edited Aug 25 '22 at 07:57

-1

sql resubmit link

You can resubmit your SQL file again until database not fully uploaded

edited Aug 25 '22 at 07:57

vimuth

5,064
33
79
116

answered Aug 19 '22 at 08:00

kajol netinnovatus

1
1

Share more details on the process, this looks like trial and error method. I believe you using the CMD for loading the file would be a better approach. – MUNGAI NJOROGE Aug 30 '22 at 15:32
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 30 '22 at 15:33

phpMyAdmin: Can't import huge database file, any suggestions?

17 Answers17

Linked