How to import large sql file in phpmyadmin

Question

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 ?

use some mysql tools such as `mysql workbench` or `mysql Yog` — xkeshav, May 30 '13 at 11:50
you can also see it here http://stackoverflow.com/questions/9593128/cant-import-database-through-phpmyadmin-file-size-too-large Dunn. — Dung, Jul 27 '15 at 21:52
If you cannot use the mysql console, there is an interesting solution which comes from phpmyadmin documentation: http://www.ozerov.de/bigdump — Paolo Gibellini, Jun 02 '17 at 11:37

score 128 · Answer 1 · edited Aug 30 '13 at 10:45

128

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}

edited Aug 30 '13 at 10:45

metakermit

21,267
15
86
95

answered Jul 03 '12 at 06:48

manurajhada

5,284
3
24
43

1

Ok, thanks for revert guys. I make changes in php.ini and zip the file. It works for me :) – Jasmeet Kaur Chauhan Jul 03 '12 at 06:56
3

fastest way to do this! – Aris Nov 14 '13 at 18:17
if you are working locally you might consider my answer further down – Toskan Jul 10 '14 at 00:49
1

This is a great answer. For anyone who might not be super savvy, you can just upload your DB file to your file manager on your host, then use SSH on your host to operate locally. This is much easier than installing `mysql` on your server for just one migration. Thanks for the help guys! (Also, I didn't need brackets) – DomainsFeatured Oct 11 '16 at 04:52
3

For those who are wondering, you need to browse to your MySQL/bin folder to run the command. Worked great. Thanks. – Etienne Dupuis Jan 10 '17 at 15:36
ERROR: Unknown command '\U'. error(mysql -u {root} -p {test} < {C:\Users\sandeep\Desktop\pincode.sql}) – Sandeep Garapati Sep 02 '17 at 09:15
@SandeepGarapati remove curly brackets. – manurajhada Feb 05 '20 at 11:09
1

What about that they do not have a password? using the database with default user name root? Using this command it is asking for the password. – Hkachhia Apr 01 '20 at 06:15
@Hkachhia just hit enter when prompted. – Sujit May 28 '22 at 11:03

Toskan · Answer 2 · 2018-09-24T16:57:39.037

92

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

edited Sep 24 '18 at 16:57

answered Jul 10 '14 at 00:49

Toskan

13,911
14
95
185

3

CORRECT - answer. Those 3 lines were must – Mar 09 '15 at 07:26
This must be added to your apache directory, not the cli directory (at least as tested on my Ubuntu box) – Bad_Neighbor Feb 26 '17 at 06:39
changing this for `/etc/php/7.1/apache2/php.ini` didn't work – Imnotapotato Feb 25 '18 at 12:44
@RickSanchez you sure you run this php version on apache? please run `phpinfo()` and verify that the values have been set correctly and the server was restarted – Toskan Feb 26 '18 at 20:39
2

Dont forget to raise the max_execution_time value in php ini and restart apache – Hamboy75 Apr 12 '18 at 06:35

score 69 · Answer 3 · edited Jul 02 '15 at 10:36

69

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/

edited Jul 02 '15 at 10:36

AnNaMaLaI

4,064
11
53
93

answered Nov 12 '13 at 07:12

Siraj Khan

2,328
17
18

6

With more recent versions of phpMyAdmin, the $cfg['UploadDir'] var isn't there at all; however, this solution still works by just adding the var on a new line in the file. – dan Jan 01 '16 at 12:01
The var is found in `config.default.php`, of the same dir, am using phpmyadmin version 4.6.4 – KAD May 07 '17 at 18:23
1

Could not edit the previous comment anymore, the dir is `libraries/config.default.php` – KAD May 07 '17 at 18:28
wonderful solution after spending two days trying the MySQL command line with failure results. – youhana Jun 04 '17 at 09:32

Guille Acosta · Answer 4 · 2016-03-30T18:20:19.607

27

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.

edited Mar 30 '16 at 18:20

answered Jul 07 '15 at 16:05

Guille Acosta

2,051
23
35

With this method phpmyadmin is giving me a `File could not be read!` message when I click the submit button on the upload form. In the middle of troubleshooting this now. – Jan 17 '17 at 21:48
@JoeRocc did you find a solution for your issue? Perhaps it's a permission or ownership problem. Try with `sudo chmod 777 your-filename.sql` – Guille Acosta Feb 10 '17 at 19:31
2

775. Not 777. One should NOT advise to set 777 to get things working on a live server. It has security issues. – Koushik Das Mar 29 '17 at 16:13

score 20 · Answer 5 · edited Mar 10 '14 at 07:37

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 User
p stands for Password
D 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.

score 9 · Answer 6 · answered Jan 15 '17 at 05:43

9

Create a zip or tar file and upload in phpmyadmin thats it..!

answered Jan 15 '17 at 05:43

Sachin from Pune

656
8
19

score 8 · Answer 7 · answered Aug 07 '14 at 09:44

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>

score 5 · Answer 8 · answered Nov 04 '16 at 12:18

5

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

answered Nov 04 '16 at 12:18

Daniel Karatha

51
1
3

Great alternative, but not the easiest. Easiest is simply uploading the huge import file into a specified upload directory, and then selecting file for import via phpMyAdmin, as explained here: https://stackoverflow.com/a/19923060/888177 – Stefan Feb 08 '23 at 09:36

score 5 · Answer 9 · answered Mar 27 '18 at 18:24

Ok you use PHPMyAdmin but sometimes the best way is through terminal:

Connect to database: mysql -h localhost -u root -p (switch root and localhost for user and database location)
Start import from dump: \. /path/to/your/file.sql
Go take a coffe and brag about yourself because you use terminal.

And that's it. Just remember if you are in a remote server, you must upload the .sql file to some folder.

score 5 · Answer 10 · answered Sep 19 '20 at 10:39

5

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.

answered Sep 19 '20 at 10:39

Marlon

1,719
3
20
42

score 4 · Answer 11 · answered Jul 03 '12 at 06:40

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.

score 4 · Answer 12 · edited Oct 24 '18 at 10:04

4

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.

edited Oct 24 '18 at 10:04

Lewis Browne

904
7
23

answered Oct 24 '18 at 09:46

Ejalee

51
4

score 3 · Answer 13 · answered Mar 08 '16 at 11:47

3

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.

answered Mar 08 '16 at 11:47

Nikunj K.

8,779
4
43
53

Thanks you, that was perfect. – Morteza Sepehri Niya Dec 20 '16 at 13:12

score 3 · Answer 14 · answered Apr 23 '19 at 21:33

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

score 2 · Answer 15 · edited Jun 19 '15 at 17:52

I stumbled on an article and this worked best for me

Open up the config.inc.php file within the phpmyadmin dir with your favorite code editor. In your local MAMP environment, it should be located here:

Hard Drive » Applications » MAMP » bin » config.inc.php

Do a search for the phrase $cfg[‘UploadDir’] – it’s going to look like this:

$cfg['UploadDir'] = '';

Change it to look like this:

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

c0d3x1337 · Answer 16 · 2018-04-28T21:24:52.350

2

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

edited Apr 28 '18 at 21:24

answered Jan 03 '18 at 02:30

c0d3x1337

47
11

This is actually good solution, but You got downvote because of URL which can be invalid one day. Please add some informations about this script, escpecially the name of it. – instead Jan 27 '18 at 08:17
its called big dump it stops the SQL server from stopping or being interrupted when importing a large database file to the server. btw there are other answers with urls that could down at any moment and none of them are down voted – c0d3x1337 Apr 28 '18 at 21:19

score 2 · Answer 17 · answered Nov 21 '21 at 12:45

First, copy your mysql database to local disk C:\ for easy file location and now open your command prompt.

Meawhile, Navigate to mysql bin folder eg if you are using xampp, run or type the code below.

cd/ This take you to local disk pointer
cd xampp This take you to xampp folder
cd mysql This take you to mysql folder
cd bin This take you to bin folder

and run the code below

mysql -u dbusername -p -D dbname < c:\yourdbtoupload.sql

This will promt enter password, enter your password or click enter button if you are not using password

score 1 · Answer 18 · answered Jun 23 '15 at 06:11

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

score 1 · Answer 19 · answered Jul 04 '20 at 12:32

1

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.

answered Jul 04 '20 at 12:32

desbest

4,746
11
51
84

score 0 · Answer 20 · answered Jul 03 '12 at 06:38

0

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.

answered Jul 03 '12 at 06:38

Tschallacka

27,901
14
88
133

1

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/11490510) – Sebastian Brosch Mar 04 '16 at 07:35
1

actually at the time this answer was posted, this was the most common error(3 years ago) that file uploads were default not big enough. He's using phpmyadmin == webinterface. import == upload from disc. upload == server settings. server settings == defaults which is at 5 or 8 mb 3 years ago. So I do not see how this does NOT provide an answer. Yes, I didn't lead him by the hand how to set up exactly becuase he hasn't provided which server he's using, apache, ngix, iis, other. – Tschallacka Mar 04 '16 at 08:07

score 0 · Answer 21 · answered Nov 29 '17 at 08:43

0

Open your sql file in a text editor (like Notepad)
Select All -> Copy
Go to phpMyAdmin, select your database and go to SQL tab
Paste the content you have copied in clipboard
It might popup a javascript error, ignore it
Execute

answered Nov 29 '17 at 08:43

Teo Mihaila

134
1
2
18

Not a good practice! Large texts will cause big problems when you do this. Best way is configure php.ini to allow large import or import through mysql. – Marcelo Agimóvel Mar 27 '18 at 18:21

score 0 · Answer 22 · edited Oct 18 '21 at 04:59

/Applications/XAMPP/xamppfiles/etc/php.ini

First find this location -> open php.ini file in notepad or sublime text Then find this "post_max_size, upload_max_filesize, memory_limit" in php.ini text and change size like below

post_max_size=450M

upload_max_filesize=450M

memory_limit=700M

Notes : Before do this stop phpmyadmin in xampp or wamp and do above methods and then startall (xampp or manager-osx) it will work perfectly. Then you can able to upload large files in phpmyadmin. Thanks

score -1 · Answer 23 · answered May 25 '22 at 06:53

-1

For windows, first of all open xampp and right click Config and open php.ini file. After in php.ini file update this code

post_max_size = 800M
upload_max_filesize = 800M
max_execution_time = 6000
max_input_time = 6000
memory_limit = 1000M

answered May 25 '22 at 06:53

Kazi Masud

41
1

How to import large sql file in phpmyadmin

23 Answers23

Linked

Related