Can't import database through phpmyadmin file size too large
MysqlDatabaseApachePhpmyadminWampserverMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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.
Solution 3 - Mysql
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!
Solution 4 - Mysql
Use command line :
mysql.exe -u USERNAME -p PASSWORD DATABASENAME < MYDATABASE.sql
where MYDATABASE.sql is your sql file.
Solution 5 - Mysql
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.
Solution 6 - Mysql
- 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 calledupload
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)
[![upload image][1]][1]
- That's all baby ! :)
***It takes lot of time.In my db(266mb) takes 50min to upload. So be patient ! *** [1]: https://i.stack.imgur.com/IdEY0.png
Solution 7 - Mysql
for ubuntu 14.04 use this:
Solution 8 - Mysql
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 = 60
enter 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
Solution 9 - Mysql
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.
Solution 10 - Mysql
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.
Solution 11 - Mysql
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!
Solution 12 - Mysql
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.
Solution 13 - Mysql
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.