I want top tips on moving a lot of data to some mysql database located remotely. (godaddy)
I've received a 700k row CSV file and imported it into MSSQL on my small dev machine and authored a couple of quick utilities.
The very first ended up being to create more compact 1 meg (max permitted by host) csv files that may be directly imported via myPhpAdmin.
I had been only getting roughly 1600 rows for every meg which may mean about 437 individual manual uploads. Not achievable and so i abandoned that technique.
Next I attempted while using mysql .Internet connector and transfer directly through ODBC from MS Access and MS Stand out. That unsuccessful.
Finally I authored b .Internet winform application that will get the information from the local SQL Server database then updates the remote MySQL database via standard place queries while using .Internet MySQL data devices.
I've the winform application spawn 4 threads, each placing a brand new unique record to increase my inputs at four new records for every loop but nonetheless only getting about 100k records every day.
(I've attempted growing to 10 threads and running multiple cases of the application however these transfers are memory hogs and 4 is all about my machine may take without problems)
I've in your area installed the mysql admin tools (MySQL Work bench 5.2CE) wishing to have the ability to in some way transfer the MSSQL data with the mysql admin tool. Unable to do this.
All the Machine ODBC attempts are failing using standard tools for example MSAcess and MSExcel. (Might be some godaddy config issues or the truth that I am managing a 64 bit OS and also the Mysql odbc tools are 32?)
I actually do be capable of connect the godaddy located mysql server remotely so my only other idea would be to place the 700k records within an Access database around the server and make 10 or 20 WCF services to complete the job of placing towards the mysql database.
Possibly getting rid of the latency of needing to transfer each row within the wire would drastically boost the place speeds?
There has to a technique that's more effective that things i have finally, a piece of equipment running 24/7 for several days simply to place records.
Any help or ideas could be greatly appreciated.
Thanks millions of, Dork
How about something similar to this:
Make use of a php script to retrieve a compressed version of SQL file (with, say, file_get_contents()) and save it within the server close to the MySQL. You need to have the ability to download more data than you'd be able by utilizing HTML upload forms. I suppose that you simply can't get it done via FTP, incidentally :)
Use Bigdump, a staggered import tool, to import the information into MySQL slowly. By doing this you'll avoid timeout / various other issues.
Best of luck!