CODEWITHSUNDEEP
uploadphpmyadminsize
qwerty
qwerty

Reputation: 221

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

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?

Upvotes: 22

Views: 98652

Answers (17)

kajol netinnovatus
kajol netinnovatus

Reputation: 1

sql resubmit link

1

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

Upvotes: -1

Robert Anthony S. Tribiana
Robert Anthony S. Tribiana

Reputation: 1032

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

Upvotes: 21

Marcelo Agimóvel
Marcelo Agimóvel

Reputation: 1719

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

Upvotes: 0

Midhun
Midhun

Reputation: 3998

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

Upvotes: 1

reignsly
reignsly

Reputation: 502

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.

Upvotes: 1

Muhammad Zeshan Ghafoor
Muhammad Zeshan Ghafoor

Reputation: 307

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.

Upvotes: 0

user3509697
user3509697

Reputation: 91

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…

Upvotes: 9

Prasad.CH
Prasad.CH

Reputation: 492

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

Upvotes: 1

Ramya
Ramya

Reputation: 151

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

Upvotes: 8

K.K.Bindal
K.K.Bindal

Reputation: 11

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.

Upvotes: 1

codemania
codemania

Reputation: 1094

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.

Upvotes: 1

ecairol
ecairol

Reputation: 6583

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

Upvotes: 0

PHP Ferrari
PHP Ferrari

Reputation: 15666

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.

Upvotes: 0

user1093284
user1093284

Reputation:

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.

Upvotes: 53

Sudantha
Sudantha

Reputation: 16224

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

Upvotes: 0

Femaref
Femaref

Reputation: 61497

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.

Upvotes: 0

awm
awm

Reputation: 6580

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

Upvotes: 3

Related Questions