Import large SQL file into MySql

Well, I'd include the database name (and drop the auth stuff - I assume people know part - could be an incorrect assumption!)

    mysql dbname < mybigfile.sql

The issue here is most likely the server's "max_allowed_packet" setting.  If it's set to 32megs and your inserts are "extended" (meaning an entire table in one line), then I don't think even the above will work.  However, you can try this right away (setting 200M to something bigger than your SQL file):

    mysql --max_allowed_packet=200M dbname < mybigfile.sql

The server has its own value for this, which you can find with:

    mysql -e 'show variables like "max_allowed_packet"'

The value here is in bytes.  If this is smaller than your file size, you're gonna have problems.  The easiest solution would be to restart the server after adding this to the "[mysqld]" portion of your /etc/my.conf (or my.ini file for Windows, in the MySQL server directory):

[mysqld]
max_allowed_packet=200M

Again, I'm assuming you received an "extended-insert" dump.  If you performed the dump from another server, redo your dump with:

    mysqldump --extended-insert=0 dbname > bigfile.sql

The file will actually be somewhat bigger, but the INSERTs will be broken up on many lines instead of one.  If someone just sent this to you, you're only option is to reset max_allowed_packet on the server as I described above.


If you insist on phpMyAdmin....

I don't use phpMyAdmin much, but I see in my 'config.inc.php' several lines that contain '32M' - you might try changing these to bigger values to see if that gets it to work.

Have you tryed setting a higher value in the php.ini -> upload_max_filesize.

Ok using command prompt u can do this easily in lesstime compared to phpmyadmin.

Navigate to path where u put the sql file

type
mysql

type

use database;
source filename.sql;

thanks for the suggestions, but since i'm a completely new to mysql i need some more help to know how to try these suggestions.  i forgot to mention that i am working on a mac.

i presume that using Terminal is the same as using an msdos command prompt, but not sure.

how do i access mysql commands from Terminal?

I use a Mac as well.

/Applications/Utilities/Terminal

is correct - that gets you to a command prompt.

Thanks for the comment Jack, but as I mentioned in my earlier email I am working on a MAC so I can't use the command prompt.  How would I follow your suggestion using Terminal on a MAC?

I'm assuming your big files are on your Desktop

1) Open Terminal

2) type in:  "cd Desktop"

3) Type in (replacing "dbname" and "bigfile.sql" appropriately):
  /local/mysql/bin/mysql dbname < bigfile.sql

4) If this gives you some sort of "I couldn't connect error", then run:
  /local/mysql/bin/mysql -u root -p dbname < bigfile.sql
and then enter in root's MySQL password (may be blank if you haven't set it yet).

Jack's long description is essentially the same thing, just explained step-by-step.

If you want a specific answer instead of general help, you'll have to provide more specific details (where are the files exactly located, what is your database name, how *big* are the files?)

I dont work on mac.

Sorry i cant help u.

I' sorry but I still don't have this figured out.  I'm working on a MAC, the SQL dump file is on the desktop, and I am having difficulty navigating around in Terminal.  I installed MAMP stack, but can't import my large (1.5GB) file.  Where do I begin???

Please give me some time i will try to figure it out.

Or sure some experts may shower some light on it...

Check this out..

http://ss64.com/osx/
http://kb.iu.edu/data/amog.html
http://www.oreillynet.com/pub/ct/51

Thanks,
Jack

Thanks Jack.  I managed to get my large table imported by following your suggestions.  What I was not understanding was that once opening Terminal on the MAC, I then needed to specify the full path to mysql and then provide the username and password and database name as follows:

/Applications/MAMP/Library/bin/mysql -u root -proot mydbname

Also, it took me a while to figure out that there should be no space between the -p and the password.  Once I got the above line figured out I was then able to start the MySQL monitor and from there I was able to import the large table.

Thanks for all your help.