In phpmyadmin either by wamp , lamp , mamp or cpanel is always a time consuming and tedious, in this post i will share 4 ways to import large file in phpmyadmin or in mysql
you may get the following errors while uploading large file in phpmyadmin
Fatal error: Maximum execution time of 30 seconds exceeded
Fatal error: Allowed memory size of —– bytes exhausted
let us resolve this issue and duscuss the ways to import large files in mysql with cpanel, wamp and lamp.
4 ways to import large file in phpmyadmin with
- By editing php.ini
- Using php script BigDump
- Use of Command Line SSH to upload sql
- Using split and compress Method
1. Use of php.ini to large database import
This method will work on wamp, xamp , manp and lamp
- find your php.ini file
- goto the line upload_max_filesize
- set its value to 500M (for 500 mb)
- Now got the line post_max_size
- Set its value to 500M
- Now goto the line
max_execution_time - Set its value to 300 or 500
to import large sql in cpanel , if your server allow to edit the php.ini you can do the above method
2. Using BigDump php script
In this method you have to upload the dump sql file in server via ftp (like filezila).
- Create a directory db_upload in your webserver
- Upload you large dump via filezilla
- Upload bigdump.php via filezilla to the db_upload directory
- Run the bigdump.php from your browser via URL like http://www.mydomain.com/db_upload/bigdump.php
- Wait for the script execution, do not close the browser .
- If Timeout errors occur, adjust the
$linepersessionsetting in this file.
More info and Download Available at here
3. Using Command Line to import database in cpanel
you can use putty for remote ssh access, after login type the following command to import the files directly into the mysql
|
1 |
mysql –u {DB–USER–NAME} –h {MySQL–SERVER–HOST–NAME} –p {DB–NAME} < {db.file.sql path} |
remember in this case your file should be inside the server, you can upload it via filezilla
4. Using SQL Dump Splitter
If your max upload size is larger then your sql dump then how to Import large file in phpmyadmin ?
The answer is easy “spilt the dump file”
But in this large file how can you split and from where to split.
Well solution of problem is simple Dump Splitter , this tool that split your sql dump files into sub files.
All you need to do is to import the file to this application, process it and then follow the instruction created by this application .
This application divides your files into no. Of chunks you want.
Also this application generate an instruction file that will tell you which file you need to import first.
i.e. the file with table structure must be imported first.
Suppose the size of your sql dump file is 80MB and your server allow you to upload only 20MB at a time then.
Steps to Use SQL Dump Splitter
- Run sql dump splitter .
- Browse and select your file .
- Set the max upload size in this case select 19MB.
- Select your destination directory and execute.
After that file generate the 6 files in your destination directory 5 with max upload size i.e. 19MB.
And one help file this Readme file,
Read me file tell you out of these 5 files which one must be uploaded first.
(which one is table structure file.)
Extra tip : You can set size of chunk 40 mb and then compress the output files with zip archive
So this is simple trick to Import large file in phpmyadmin if your max upload size less then file.
Download