Importing Large SQL Files into MySQL

Ran into some fun times working with 20-50MB SQL files, so thought I’d share:

First, login to MySQL via the command-line, and increase your max_allowed_packet size via the run-time flag:

>$ mysql --max_allowed_packet=128M -u root

Next, select your database, and use the SOURCE /path/to/file command instead of \. /path/to/file method:

>$ mysql SOURCE /www/vhosts/examplesite.com/db.sql

Be sure to use a full path to your file, and obviously replace the path from my example with the path on your file system. I found <a href=”>this post on MySQL imports using SOURCE informative and give John Andrews mad props, especially for have a sweet last name.

Happy MySQL Importing.

7 Comments

  1. Nelson says…

    Thanks a lot Andy, I were aware that i should increase –max_allowed_packet, but didn’t know that should use Source command instead normal mysql import.

  2. Gryffyn says…

    Great tip. Could have used this once or twice. Although I had larger task and believe I used Toad for MySQL ( http://toadformysql.com ) to import a couple gigs of data to a remote MySQL server.

    Can’t say it’ll work for everyone and every situation but did a bang-up job for me a few times.

  3. Gibbs says…

    I had forgotten how to do this and a few gig to import. Cheers Andy, much appreciated.

  4. Woody says…

    Hey, Know this is quite old but I found this epic’ly useful:
    http://www.ozerov.de/bigdump/

    It will process a huge sql file in nugget’ size chunks, haven’t hit a sql file big enough to fault it yet.

    😀

  5. dootzky says…

    hey man, thanks a lot, I tried 5 different sites and solutions, but your did the trick, and now I’ve imported a 6GB mysql dump into my computer without any problems 🙂

    THANKS MAN! 🙂

    cheers from Serbia, Europe!

  6. Yugo says…

    Thanks, it’s works! 🙂

  7. Test McGhee says…

    Exactly what I needed. Thanks for the info.

RSS feed for comments on this post. TrackBack URL

Leave a Comment

February 26, 2009

Filed in Development

There are 7 comments »


« Back to the Blog