MySQL Snapshot from a full disk. Tar over ssh

Have you ever tried to take a snapshot from a mysql server only to find an hour later that the box ran out of disk space when trying to create the tar ball? An easy solution is to use ssh to pipe the tar data directly to another server without it even touching the disk. For those unfamiliar with unix shells or pipes a pipe allows you to tie the output of one program to the input of another. This is most commonly used to manipulate that stream of data. For example if you want to find a specific file in a directory. In these examples cartman is server A (where the commands are ran from). Stan is server B.

cartman> ls | grep mysql-5
mysql-5.0

The | (pipe) tells the shell to direct the output of ls to the input of grep which looks for the pattern ‘mysql-5′. This same functionality can be used with the tar command. This next command would compress a file and uncompress it on itself. Of course this is pointless. Until we add in ssh

cartman> tar -cf – mysql-5 | tar -xf -

With ssh we can pipe data to another server without it being written to the local disk. If the same users and (uids) exist on both systems tar will even preserve the owner and permissions of the file which is exactly what we want for a mysql snapshot.

cartman> tar -cvf – mysql-5 | ssh stan ‘ tar -xf -’

You can of course add in calls to cd and sudo to preserve user permissions and make sure the snapshot ends up in the correct dir.

cartman> cd /usr/local/mysql; tar -cvf – data | ssh stan ‘cd /usr/local/mysql; sudo tar -xf -’

Just to confuse you this can also be ran the other way. From stan to cartman. Think of this as reaching over to the other server and pulling a file through.

stan> ssh cartman ‘cd store/mysql/; tar -cf – mysql-4.1.15.tar.gz’ | tar -xf -

This can also be used to speed up scp command that involve lots of small files. Tar will produce a stream of output which can be sent across the network in much larger blocks than the native scp protocol which will send a small packet for each file if the file is small enough. The linux command line contains hundreds of different tools that can be combined in millions (billions? trillions?) of different ways to easily accomplish tasks. I have been using linux for years and am still learning new shell tricks almost daily. If you’re an old school shell guy please add a comment with your favorite shell tricks.

8 Comments

  1. Luke Hollins says:

    Only drawback with tar is it drops OS-specific flags. Have you tried rsync for this? it can cut down on the traffic I use it to make remote backups to machines on slow links.

  2. Eric Bergen says:

    Which os specific flags are you talking about?

    I have tried rync in the past. It has proved to be more difficult than tar over ssh due to version mis matches and crashing :(

  3. Sheeri says:

    My trick is df -h and du -sh. Always figure out the size of what you’re tar’ing up!!! Blindly tar’ing and coming back to find the disk is full is not responsible sysadmining. If you have a script to backup like that, a few simple tests to make sure you won’t run out of space (using df and du) are in order.

    As well, you can tar | gzip -c and that will zip the input as it comes in, as opposed to tar’ing and then gzip’ing. Unfortunately, bzip2 doesn’t have a feature like that, as it compresses better.

  4. Luke Hollins says:

    I have had some issues with rsync between differing systems but it’s generally OK. In situations where you’re paying by the bit it’s very useful. For stuff tar misses I am thinking for example on FreeBSD of file flags from chflags and ACLs from get/setfacl.

  5. Chris Moore says:

    I’m always finding this one-line bash script useful:

    echo some:set:of:data:in:one:line | while read LINE; do FIRST=`echo $LINE | cut -d: -f1`; SECOND=`echo $LINE | cut -d: -f2`; echo $SECOND $FIRST; done

    This script will spit out “set some”.

    The general usefulness is that you can parse input to this script, line for line. It’s pretty easy to parse out pieces in various ways and then use them for some command you need the values for. Combine this with some output and error handling/redirection and you can whip together some quick-n-dirty solutions :)

  6. Eric Bergen says:

    GNU Tar has the -z and -j flag for optional gzipping/bzipping.

  7. Toby says:

    Don’t forget (GNU) tar has the -C (change directory) option for this purpose, so in your example you could write

    cartman> tar -C /usr/local/mysql -cvf – data | ssh stan ‘sudo tar -C /usr/local/mysql -xf -’

  8. Merlin Raser says:

    Thx for information.

Leave a Reply