Can't import database through phpmyadmin file size too large

Question

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

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.

did you refer to the documentation? do you have shell access to your server? — ninesided, Mar 06 '12 at 22:47
Best [Ans](http://www.steptoinstall.com/import-big-database-sql-file-to-phpmyadmin-wamp-server.html) — Step To Install, Apr 12 '15 at 05:43

score 94 · Answer 1 · edited May 27 '18 at 17:17

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

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.
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.

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

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

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

+1 for mysql console. 600mb database imported in 30 seconds. Using xampp, method: `cmd` > `cd {xampp_dir}/mysql/bin` > `mysql -h localhost -u root {db_name}` > enter password then step 4 from above solution. — Onimusha, Nov 20 '20 at 16:00

score 79 · Accepted Answer · answered Mar 06 '12 at 23:00

79

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

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

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.

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.

answered Mar 06 '12 at 23:00

2

Thanks alot. It worked but I more option was needed, we have to add a variable ($cfg ['ExecTimeLimit'] = 0;)which we have to declare in c:\wamp\apps\phpmyadmin\config.inc.php. I am providing the links here (http://www.john-dugan.com/?p=638) – Ricky Sharma Mar 12 '12 at 05:12
Or you can edit /etc/php.ini in RedHat 6.6 using the same directives above , always restart apache such as: sudo service httpd restart in RedHat. – Dung Jul 27 '15 at 21:18
1

where this ini file should be created. Is in PHPmyAdmin folder? – Abel Jojo Oct 03 '16 at 07:11
3

@AbelJojo, This answer is bad, look here instead: https://stackoverflow.com/a/38607904/4684797 – Black May 27 '18 at 19:08

score 34 · Answer 3 · edited May 23 '17 at 11:54

34

Here is what I've done:

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

Done!

edited May 23 '17 at 11:54

Community

1
1

answered Jun 06 '13 at 11:00

Rishi Kulshreshtha

1,748
1
24
35

2

Had the same problem as the OP. This solution worked for me. – swl1020 Jun 11 '13 at 16:12
Thanks, saw a whole bunch of other more complicated answers and this worked great. – Jordan_Walters Oct 18 '17 at 11:34

score 22 · Answer 4 · edited Feb 22 '14 at 09:28

22

Use command line :

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

where MYDATABASE.sql is your sql file.

edited Feb 22 '14 at 09:28

Hasib Al Muhaimin

62
11

answered Mar 06 '12 at 22:48

how would you do this if you only had FTP access to the server? – ninesided Mar 06 '12 at 22:55
1

@Kyotoweb There's no space between the -p and PASSWORD on the command line (I can't edit your post because the change is only one character). – jcmeloni Mar 06 '12 at 22:58
@ninesided He's talking about WAMP and local. Just gave him the fastest solution to his problem taking in consideration is environment. – Mar 06 '12 at 23:02
aah I didn't spot the WAMP - the OP didn't mention local, but I guess most Windows server providers allow RDP, so that may be largely irrelevant – ninesided Mar 06 '12 at 23:12
When using this option just make sure that you run the script in the folder where the .sql file is stored e.g C: \downloads>mysql.exe -u USERNAME -pPASSWORD DATABASENAME < MYDATABASE.sql – Marinus Jan 27 '15 at 23:06

Anuja · Answer 5 · 2017-12-19T03:48:17.040

Set the below values in php.ini file (C:\xampp\php\)
- max_execution_time = 0
- max_input_time=259200
- memory_limit = 1000M
- upload_max_filesize = 750M
- post_max_size = 750M
Open config.default file(C:\xampp\phpMyAdmin\libraries\config.default) and set the value as below:
- $cfg['ExecTimeLimit'] = 0;
Then open the config.inc file(C:\xampp\phpMyAdmin\config.inc). and paste below line:
- $cfg['UploadDir'] = 'upload';
Go to phpMyAdmin(C:\xampp\phpMyAdmin) folder and create folder called upload and paste your database to newly created upload folder (don't need to zip)
Lastly, go to phpMyAdmin and upload your db (Please select your database in drop-down)

That's all baby ! :)

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

Riad Hossain · Answer 6 · 2019-02-02T12:16:03.980

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

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

post_max_size = 8M
upload_max_filesize = 2M
max_execution_time = 30
ut_time = 60enter code here memory_limit = 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

score 4 · Answer 7 · answered Jun 09 '16 at 10:04

for ubuntu 14.04 use this:

1- open php.ini using gedit or nano:

sudo gedit /etc/php5/apache2/php.ini

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

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

4- restart apache2:

sudo service apache2 restart

5- now open

localhost://phpmyadmin/

in browser and uplaod your database.sql

Thank you, it helped. I have Ubuntu 18.04 and for me path to `php.ini` was `/etc/php/7.2/apache2/php.ini` — Yamashiro Rion, May 29 '19 at 08:55

score 4 · Answer 8 · answered Mar 06 '12 at 22:54

Another option that nobody here has mentioned yet is to do a staggered load of the database using a tool like BigDump to 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.

score 1 · Answer 9 · edited Mar 09 '15 at 04:39

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

JUST FOLLOW THE STEPS.

Install MySQL WorkBench 6.0

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

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

Mitesh vaghela · Answer 10 · 2018-08-30T12:54:54.820

1

to import big database into phpmyadmin there are two ways 1 increase file execution size from php.ini 2 use command line to import big database.

edited Aug 30 '18 at 12:54

answered Oct 20 '16 at 10:26

Mitesh vaghela

470
4
6

score 1 · Answer 11 · answered Jan 28 '17 at 14:36

1

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!

answered Jan 28 '17 at 14:36

Cindy Cullen

11
1

score 0 · Answer 12 · answered Apr 12 '23 at 14:39

Just to put this here as I just updated my databases in xampp after several tries and reading the answers here. I used the Xampp 7.4 I tried to edit the ExecTimeLimit variable in config.default.php but still kept timing out and finally when I looked at the code I realized that if you put it to 0 it ill still default to 300. So you need to put it to actual high value to have a longer timeout.

 /**
 * @param array<int|string, mixed> $settings
 *
 * @psalm-return 0|positive-int
 */
private function setExecTimeLimit(array $settings): int
{
    if (! isset($settings['ExecTimeLimit'])) {
        return 300;
    }

    $execTimeLimit = (int) $settings['ExecTimeLimit'];

    return $execTimeLimit >= 0 ? $execTimeLimit : 300;
}

score 0 · Answer 13 · answered Mar 06 '12 at 22:50

You have three options:

Use another way to get the file on the server, and use a mysql client on the server
Use another client to connect to the server and load the data
Change your PHP settings to allow such huge files. Don't forget to increment the execution time as well.

score -1 · Answer 14 · answered Jan 14 '21 at 01:54

-1

If you do not want to change the settings or play with command line. There is option to compress the file and upload in phpMyAdmin. It should bring down the size considerably.

answered Jan 14 '21 at 01:54

Mohmed Ashfaq

1

Can't import database through phpmyadmin file size too large

14 Answers14

Linked

Related