I want to import a sql file of approx 12 mb. But its causing problem while loading. Is there any way to upload it without splitting the sql file ?
This question is related to
mysql
phpmyadmin
3 things you have to do:
in php.ini
of your php installation (note: depending if you want it for CLI, apache, or nginx, find the right php.ini to manipulate)
post_max_size=500M
upload_max_filesize=500M
memory_limit=900M
or set other values.
Restart/reload apache if you have apache installed or php-fpm for nginx if you use nginx.
Remote server?
increase max_execution_time
as well, as it will take time to upload the file.
NGINX installation?
you will have to add: client_max_body_size 912M;
in /etc/nginx/nginx.conf
to the http{...}
block
In MAMP, You could load huge files by :
creating a new folder in this directory /MAMP/bin/phpMyAdmin/"folderName"
and then edit "/MAMP/bin/phpMyAdmin/config.inc.php" line 531 :
$cfg['UploadDir']= 'folderName';
Copy your .sql or .csv Files into this folder.
Now you will have another option in "PhpMyAdmin" : Select from the web server upload directory newFolder/: You could select your file and import it.
You could load any file now !!
I was able to import a large .sql
file by having the following configuration in httpd.conf
file:
Alias /phpmyadmin "C:/xampp/phpMyAdmin/"
<Directory "C:/xampp/phpMyAdmin">
AllowOverride AuthConfig
Require all granted
php_admin_value upload_max_filesize 128M
php_admin_value post_max_size 128M
php_admin_value max_execution_time 360
php_admin_value max_input_time 360
</Directory>
the answer for those with shared hosting. Best to use this little script which I just used to import a 300mb DB file to my server. The script is called Big Dump.
provides a script to import large DB's on resource-limited servers
Create a zip or tar file and upload in phpmyadmin thats it..!
You will have to edit the php.ini file. change the following upload_max_filesize
post_max_size
to accommodate your file size.
Trying running phpinfo()
to see their current value. If you are not at the liberty to change the php.ini file directly try ini_set()
If that is also not an option, you might like to give bigdump a try.
Just one line and you are done (make sure mysql command is available as global or just go to mysql installation folder and enter into bin folder)
mysql -u database_user_name -p -D database_name < complete_file_path_with_file_name_and_extension
Here
u
stands for Userp
stands for PasswordD
stands for Database---DON'T FORGET TO ADD <
SIGN AFTER DATABASE NAME---
Complete file path with name and extension can be like
c:\folder_name\"folder name"\sql_file.sql
---IF YOUR FOLDER AND FILE NAME CONTAINS SPACE THAN BIND THEM USING DOUBLE QUOTE---
Tip and Note: You can write your password after -p
but this is not recommended because it will show to others who are watching your screen at that time, if you don't write there it will ask you when you will execute command by pressing enter.
Solution for LINUX USERS (run with sudo)
Create 'upload' and 'save' directories:
mkdir /etc/phpmyadmin/upload
mkdir /etc/phpmyadmin/save
chmod a+w /etc/phpmyadmin/upload
chmod a+w /etc/phpmyadmin/save
Then edit phpmyadmin's config file:
gedit /etc/phpmyadmin/config.inc.php
Finally add absolute path for both 'upload' and 'save' directories:
$cfg['UploadDir'] = '/etc/phpmyadmin/upload';
$cfg['SaveDir'] = '/etc/phpmyadmin/save';
Now, just drop files on /etc/phpmyadmin/upload
folder and then you'll be able to select them from phpmyadmin.
Hope this help.
For that you will have to edit php.ini
file, If you are using the ubuntu server this is link Upload large file in phpMyAdmin might help you.
PHPmyadmin also accepts compressed files in gzip format, so you can gzip the file (Use 7Zip if you don't have any) and upload the zipped file. Since its a text file, it will have a good compress ratio.
I have made a PHP script which is designed to import large database dumps which have been generated by phpmyadmin. It's called PETMI and you can download it here [project page] [gitlab page]. It has been tested with a 1GB database.
Ok you use PHPMyAdmin but sometimes the best way is through terminal:
mysql -h localhost -u root -p
(switch root and localhost for user and database location)\. /path/to/your/file.sql
And that's it. Just remember if you are in a remote server, you must upload the .sql file to some folder.
I stumbled on an article and this worked best for me
Hard Drive » Applications » MAMP » bin » config.inc.php
$cfg[‘UploadDir’]
– it’s going to look like this:$cfg['UploadDir'] = '';
$cfg['UploadDir'] = 'upload';
Then, within that phpmyadmin dir, create a new folder & name it upload.
Take that large .sql file that you’re trying to import, and put it in that new upload folder.
Now, the next time you go to import a database into phpMyAdmin, you’ll see a new dropdown field right below the standard browse area in your “File to Import” section.
Edit the config.inc.php file located in the phpmyadmin directory. In my case it is located at C:\wamp\apps\phpmyadmin3.2.0.1\config.inc.php
.
Find the line with $cfg['UploadDir']
on it and update it to $cfg['UploadDir'] = 'upload';
Then, create a directory called ‘upload’ within the phpmyadmin directory (for me, at C:\wamp\apps\phpmyadmin3.2.0.1\upload\
).
Then place the large SQL file that you are trying to import into the new upload
directory. Now when you go onto the db import page within phpmyadmin console you will notice a drop down present that wasn’t there before – it contains all of the sql files in the upload directory that you have just created. You can now select this and begin the import.
If you’re not using WAMP on Windows, then I’m sure you’ll be able to adapt this to your environment without too much trouble.
Reference : http://daipratt.co.uk/importing-large-files-into-mysql-with-phpmyadmin/comment-page-4/
Best way to upload a large file not use phpmyadmin . cause phpmyadin at first upload the file using php upload class then execute sql that cause most of the time its time out happened.
best way is : enter wamp folder>bin>mysql>bin dirrectory then write this line
mysql -u root -p listnames < latestdb.sql here listnames is the database name at first please create the empty database and the latestdb.sql is your sql file name where your data present .
but one important thing is if your database file has unicode data . you must need to open your latestdb.sql file and one line before any line . the line is :
SET NAMES utf8; then your command mode run this script code
Change your server settings to allow file uploads larger than 12 mb and you should be fine. Usually the server settings are set to 5 to 8 mb for file uploads.
One solution is to use the command line;
mysql -h yourhostname -u username -p databasename < yoursqlfile.sql
Just ensure the path to the SQL file to import is stated explicitly.
In my case, I used this;
mysql -h localhost -u root -p databasename < /home/ejalee/dumps/mysqlfile.sql
Voila! you are good to go.
Try to import it from mysql console as per the taste of your OS.
mysql -u {DB-USER-NAME} -p {DB-NAME} < {db.file.sql path}
or if it's on a remote server use the -h flag to specify the host.
mysql -u {DB-USER-NAME} -h {MySQL-SERVER-HOST-NAME} -p {DB-NAME} < {db.file.sql path}
I dont understand why nobody mention the easiest way....just split the large file with http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/ and after just execute vie mySQL admin the seperated generated files starting from the one with Structure
Source: Stackoverflow.com