How to migrate Flat Text File into MySQL?

[zeroxsys]

I am running a VB.NET loop that imports a flat text file of 122677 towns/cities of Japan into an online MySQL table. MySQL Workbench crashes when you upload table this big, so export/import won't work. It took less than 2 hours when I first run it for local migration, now it's only at 55600 after 3 hours.

Is there a better way to do this?

[smoove]

Do it on the server.

[zeroxsys]

Thank you so much @smoove, I totally forgot to do this on the server for my own sake! Well the reason is, I am really looking at a long-term solution, not just for this Japan address job.

This desktop app technique I am using now is more geared towards my clients who cannot do that command/code (you are recommending) themselves. I am looking at how to do this kind of migration and synchronization of local and online (MySQL) databases automatically (or at least with a single click by my clients). My clients may not even know how to do FTP; even if I provide them with manuals to the technical steps, they might find it difficult. That’s why I am building these VB.NET migration/synchronization utilities that do flat-file migration and local-to-online (Intranet-to-Internet) table synchronization so that they can accomplish the task on their own.

I can setup MySQL Replication but they will have problem with primary IDs. Aside from that, I don’t know other tools/technique out there that can do these migration and synchronization.

[zeroxsys]

I don't have a shell access to my client's shared server, so I can't do it using perl/bash. Yes, I'll try this with PHP instead.

[rick888]

you could upload the file to your server and do a mysql_import (I assume it's tab/comma delimited).

PHP can call system commands, so you could do system("Mysql import ...(file)...").

more info about the command here: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

[zeroxsys]

Yes, I am familiar with that, thank you @rick888.

[there]

are you doing bulk inserts or one-per-transaction?

[zeroxsys]

Not using transaction, auto-commit.