How do I import a large mysql databse file?

If you are importing a file over a few MB more often than not the phpadmin interface will crash.

Depending on the size of the file it is possible to simply break it into chunks and paste each section into the sql window but this very quickly becomes tiresome and also could lead to missing out data.

A much more effective way of doing this is via your command prompt and only takes a few moments (where I write something in [ ] brackets the contents of the brackets needs to be changed to your local values and the brackets omitted):

  1. Open your command prompt and navigate to your mysql/bin directory (entering cd .. takes you up a level, entering cd [name of directory] will change you directory to the name entered).
  2. Login to mysql – type: mysql -u[username] -p then press Enter. Enter your password when prompted. The command prompt should now look like : mysql>
  3. Assuming you have already created the database, type : use ‘[database name]’
  4. Type source [path of file to import].sql (so for example this could be source c:\Users\Someuser\Downloads\myfile.sql or similar)
  5. The command window should then show files being imported row by row.

As I type I am in the process of importing a 586 mb sql file, so far so good..

Leave a Reply

Your email address will not be published. Required fields are marked *

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

© dougandrews.co.uk
CyberChimps