Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've a database of more than 20Gb of size on my hard disk. What is the best way to upload it with the least (money) load possible on the server?
- I'm on Windows 7.
- I have FTP and SSH access on the server.

I avoid using FTP because my connection cuts off a lot, I can't imagine I re-upload again the file after failing on 99%.

I found some tools that split the large .sql file to small .sql files, but they didn't mention how to gather these files again into one file.

Another way is to archive the big .sql file to .rar with -v option, upload them through FTP then unpack them. But unpacking will also cost, right?

I know it will cost in any cases, but any best practice will be strongly appreciated.

share|improve this question
    
What exactly is costing you here? Are you paying a high price for bandwidth or CPU? If bandwidth is your concern, then I would look at RSYNC over SSH with compression. –  Zoredache Nov 23 '12 at 10:18
    
My concerns are Bandwidth and upload time. My internet connection cuts a lot. Zipping a .sql file into small .zip files won't effect the data of the database? –  Nadjib Mami Nov 23 '12 at 10:38
add comment

2 Answers 2

up vote 0 down vote accepted

Uploading through SFTP (WinSCP is a free graphical utility) would allow you to resume and does have the option to enable compression built in.

If you want to save bandwidth/temporary storage costs you could gzip the archive before uploading it and then use a command similar to gunzip yourdatabase.sql.gz | mysql -u -p . I believe both izarc and 7zip are capable of creating a .gz file.

share|improve this answer
    
Thanks Jared, your answer is very helpful, I'll figure out the WinSCP and go ahead. –  Nadjib Mami Nov 23 '12 at 10:49
    
I doubt it, but if this is a shared server you may wish to speak to the administrator of it. Depending on the exact database structure, importing a 20GB .sql file may take a few hours, maxing out the cpu and/or hard drive the entire time. If I remember correctly, pulling in a 2GB database took me about 30 minutes on a quad core machine with SAN storage. –  Jared Bartimus Nov 23 '12 at 10:55
    
Structure is not complex. I'll give up the time factor, I can wait. –  Nadjib Mami Nov 23 '12 at 11:03
    
I just settle for resuming after losing connection and having the least pressure on the server. –  Nadjib Mami Nov 23 '12 at 11:06
    
I suggest using a screen session either way (if it is available on your server). That way if you get disconnected there is not a risk of the command getting terminated. –  Jared Bartimus Nov 23 '12 at 11:09
show 1 more comment

Share it on a secure http server, and download it on the other side using a Download manager that has bandwidth limitation and Resume support. i would recommend to use Internet Download manager or Free download manager for this purpose.

I use the same method to share large file over WAN.

(Make sure you share it on secure http server, where the access is password protected and based on IP restriction)

You can use this small HTTP server for sharing this file over http: http://www.aprelium.com/abyssws/

share|improve this answer
    
As I mentioned, my connection cuts a lot. That will probably prevent the sharing process, right? –  Nadjib Mami Nov 23 '12 at 10:34
    
even if it does, still it can be reduced, thats the beauty of HTTP protocol. its Resume support –  Farhan Nov 23 '12 at 10:40
    
What do you think Frank about zipping the big .sql file into small chunks? Is that safe for my data when I extract them on the server? –  Nadjib Mami Nov 23 '12 at 10:42
    
zipping would be time consuming, and unzip process would be very resource intensive on the server. the method i gave you will put least level of load on server. as Zip/Unzip require a lot of computing resources. and for 20GB,it would be tough for server (as you are saying you dont want to put much load on it) –  Farhan Nov 23 '12 at 10:44
    
Thank you very much Frank :). –  Nadjib Mami Nov 23 '12 at 10:45
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.