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'.
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 '.
Then just identify the source SQL file using 'SOURCE ' So, for me that was 'SOURCE C:\dbDump.sql'

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

About TechnoTaste:

I'm Judd Antin, and this is my blog. I spend most of my time riding the fine edge between social psychology, HCI, and computer-mediated communication. I'm currently a post-doctoral Research Scientist in the Internet Experiences research group at Yahoo! Research. I finished my PhD at UC Berkeley's School of Information (iSchool) in 2010. My research explores how social psychological incentives operate to facilitate or hinder online cooperation. My research topics let me work and think in social psychology, sociology, economics, psychology, and communication, and to do fundamentally multi-methods work. It's my favorite thing. Oh, and I'm also a former chef, so that explains the food & wine stuff.

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.