up vote 1 down vote favorite
share [g+] share [fb]

I have about a large MYSQL DB(3 TB) that I need to move to another host, which is not yet up. what's the best approach?
I plan to either zip the mysqldump or the binary, what is the choice of the zip software? I want to be able to create many minizips so that I can incrementally download them to my client box. current host os is redhat.
how should I download the files? is ftps built in from ssh server the best way?
lastly, most importantly, the host itself already runs out of diskspace, has a couple hundred GB to buffer. how do I go about zipping up the mysqldumps?

link|improve this question
72% accept rate
feedback

3 Answers

up vote 2 down vote accepted

You can pipe the mysqldump across ssh (enabling compression might make it faster depending on the data and server capabilities) and save it to your local machine (or directly to the destination server). You can also pipe the mysqldump command through gzip/bzip2/xz so the file it produces on the local end of the pipe is considerably smaller.

Using the binary database is not the recommended way to backup/restore (from what I know).

link|improve this answer
the database is huge, how do I handle connection issues in the process of piping mysqldump and be able to resume the process? – user12145 Apr 27 '11 at 14:37
@user, you can dump one table or database at a time. Does your network have problems that prevent you from keeping a connection open indefinitely? – Chris S♦ Apr 27 '11 at 14:56
yes I was thinking about downloading to my local pc where connection is not stable. the whole db has only a couple of tables. these are large tables. what compression should I use if most of the data is english text? – user12145 Apr 27 '11 at 15:20
If you've got a relatively fast connection and/or slow server, use gzip. If you've got a slow connection/fast server, xz (or bzip2 if you don't have xz). – Chris S♦ Apr 27 '11 at 15:36
feedback
up vote 0 down vote

the best solution for you to copy your data to another Database hosted on another server dump your data as insert table by table

link|improve this answer
feedback
up vote 0 down vote

If your local filesystem only has a few hundred GB available, and your DB is 3TB... I don't see how you can do a dump.

You can try doing it over ssh to another host as Chris S suggested.

You can also mount the disk of a remote machine via NFS or Samba and dump directly to there.

If you want to split the dump file into multiple pieces, you can use the "split" command (read the man page for options... default option is to split in pretty small pieces, and you could end up with several thousand small chunks...).

There isn't really a "best" way to do the transfer, but if it's over a local network, pretty much anything should be fine. If you're doing it over a long distance (I mean like many thousand kilometers) or a high latency link, I don't recommend generic ssh for large file transfers.

link|improve this answer
should I use tar or gz prior to split? – user12145 Apr 27 '11 at 15:17
Well, tar will let you bundle a group of files together into a single "tar file", so it's kind of the opposite of "split". Not sure why you would want that after you have already split it up. Just split it into the sizes you want (of course, this is after you've done the dump, so you still have your original problem), and then gzip those with max compression (-9 option). – Some Guy Apr 27 '11 at 15:24
so I split the dump .sql file, and then gzip them, how do I join them together later? – user12145 Apr 27 '11 at 15:28
You can simply "cat" the files into one big file again after you gunzip them ("cat xaa xab > newfile" for example, will join those two files into one). Note that this is all a lot of extra work and hassle. It certainly would be more logical to find a way to dump your mysqldump directly to a remote filesystem, if it's at all possible. – Some Guy Apr 27 '11 at 15:38
dump mysqldump to a remote filesystem is possible, however, I also want a local(by local I mean home pc local) copy, downloading a large file over several days can be risky given the stability of my internet connection and pc. – user12145 Apr 27 '11 at 18:28
feedback

Your Answer

 
or
required, but never shown
discard

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