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.