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.
RSS feed for comments on this post. TrackBack URL
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.
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.
Gibbs says…
I had forgotten how to do this and a few gig to import. Cheers Andy, much appreciated.
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.
😀
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!
Yugo says…
Thanks, it’s works! 🙂
Test McGhee says…
Exactly what I needed. Thanks for the info.