Importing large datasets to MySQL using command-line
Command-line MySQL client is way faster and more stable for importing large datasets into MySQL database. For all those Linux geeks, this is of course obvious. But, for use, lame Windowsers, using phpMyAdmin was nearly always a better choice, and this such articles like this still should be written! :>
In my case, it was 152 tables, nearly 120k records and 30 MB of uncompressed .sql file.
Even though I have all the limits reduced and all the timeouts maxed, phpMyAdmin was breaking on import. Even though, it claims, it has feature of resuming broken imports, it was not doing so in my case. I had to go on and use an alternative in form of command-line MySQL client.
Step, that I undertook were:
- Put uncompressed .sql file to c:\ root disk (cause, I like that)
- Run command line and navigate to C:\XAMPP\mysql\bin (adjust path accordingly).
- Execute
mysql -u username -p password < c:\dump.sql - Enter password if requested to do so
And there I was. Not only my entire .sql file was imported without any problems, but also entire import process took a snap of time.