Import large files (table or database) to mysql
How can you solve this issue? We will start with reproducing steps previous to this error to make sure we are talking about the same issue.
Step By Step on How to import file using phpMyAdmin
- Open phpMyAdmin and select database to which you will try to import data from file.
- Select the imoprt TAB
- Choose larg file size
- Click Go to start the import process
If all goes well you will see that data imported correctly. But we are interested in cases where you get error saying something went wrong
"No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration. See FAQ 1.16."
First thing to do in order to solve issue of exiding max file size changing default setting in php.ini.
Go to the PHP folder locate php.ini file and open it in edit mode.
If you are not sure where php.ini file is you can add info.php file with the following lines to show php info :
<?php echo phpinfo(); ?>Go to your favorit browser and put the following url
On php info serch for the file php.ini location.
Open php.ini file and edit the following parameters to increase sizes, in our example we will increase each parament in ten times but you will need to increase sizes to support your heavy files:
- upload_max_filesize increase from 2M to 20M
- post_max_size increase from 8M to 80M
- max_input_time increase from 60 to 600 (set -1 for unlimited time)
- memory_limit increase from 8M to 80M (maximum amount of memory could be 128MB)
- max_execution_time increase from 30 to 300
* I recommend you to add comment above each change in configuration files to allow you fast roll back.
Save changes to php.ini files and restart apache and try to import the heavy file again.
If you still get same error when trying to import heavy file using phpMyAdmin then let's try to import this heavy file using command line.
Import file using MySQL command line
Open terminal, put the following command to import data base file, replace username with your own database username, replace database mame and file name. mysql -u root -p myDB < fileToImport.sql
Click 'Enter'. system will prompt for Password, type your database password and click enter again.