Easy MySQL Import from the Command Line on Windows

I have to deal with fairly large database imports from time to time, which can be a bit of a challenge. phpmyadmin fails pretty miserably when dealing with large database dumps or database imports. Either the SQL files are too large, or the import process takes too long. There are lots of ways around this, but after trying most of them I've found what I think is the easiest way to handle large database imports into MySQL on Windows.

All credit due to Greg at dittio.net who posted a tutorial on this. My only contribution is to say that this works on Windows as well. Here's how I did it:

To make things easy, I moved the SQL file I was importing to a root directory – C:\ or similar.
Run mysql. I don't have it in my path, so I had to navigate to the proper directory to find the .exe. I use the excellent XAMPP, so for me that was C:\xampp\mysql\bin\. On the laptop that I use for development I have no password set for the root user (security alert! OMG!), so the command was just 'mysql -u root'. If you had a password set the command would be 'mysql -u root -p'.
This tutorial assumes there's already a database you want to use. If not, look here for a tutorial on how to create one. Otherwise, switch to that database using the command 'USE {databasename}'.
Then just identify the source SQL file using 'SOURCE {pathtofile};' So, for me that was 'SOURCE C:\dbDump.sql'. Don't forget that semi-colon!

That's it. The import of a 3mb SQL file took about a second. Swoosh.

George Shirey says:

1/20/2011 at 9:47 am

Thank you so much! I have spent way to many hours and days splitting tables into bite sized chunks for my phpMyAdmin to handle. No more – it all loaded so fast. Why is it that the php script takes over 300 seconds and the command line took all of 5 even though they are both running on my local xampp server?

Thanks again!

Judd says:

1/20/2011 at 1:30 pm

Glad to help, George. I have also been mystified about the time differences. I think it's just that phpmyadmin has to take the file upload, and then send the data through mysqli. It's just much less efficient and requires a lot of overhead. By using the native mysql command we skip all of that and get the most optimized process.

Louis says:

2/12/2011 at 12:44 pm

Thanks also Judd, it's incredible that this post is the only thing resembling plain English on such a fundamental issue….brilliant!

Randall says:

3/9/2011 at 4:35 pm

Also thanks. Brilliant. Can't believe it was that easy. Wanted to work on my database on my localhost, so I 'mysqldump'ed the database, downloaded from server to my machine… then couldn't figure out how to import it!

Gersh says:

1/5/2012 at 8:24 am

Thanks bro, you saved me a crap load of time.

Ryan Marshall says:

2/9/2012 at 4:22 am

Thanks for this the SOURCE command is a new one to me.

Just processed a near 500mb file in about 1 minute.

Richard Joseph says:

2/21/2012 at 5:49 am

Thanxxxx a lot dude!!

Anna says:

3/10/2012 at 1:00 pm

This just worked perfectly for an import of a 2.5GB database! Thank you for documenting for the rest of us.

Anna

Randika says:

3/14/2012 at 10:29 am

Thanks bro,I Can't believe it was that easy

Steve K says:

4/11/2012 at 7:55 am

Great! Thanks man

josh says:

4/17/2012 at 3:14 pm

I must be doing something wrong, followed the steps and nothing happens, all I see is a new line ( ">" ) for some reason, I can't reference the source file location (C:\myfile.sql)

Naz says:

4/23/2012 at 3:27 pm

Thanks this is really helpful, it was taking so long to import a 60mb file now its done in seconds!!

caesar says:

5/10/2012 at 9:01 pm

AWESOME !!! as simple as that…many thanks brother i used to use mysql on linux, then i'm using windows for import the database, 1st i was confused about the command :p then i met ur website