Backup a remote database to your local filesystem

ssh user@host 'mysqldump dbname | gzip' > /path/to/backups/db-backup-`date +%Y-%m-%d`.sql.gz
I have this on a daily cronjob to backup the commandlinefu.com database from NearlyFreeSpeech.net (awesome hosts by the way) to my local drive. Note that (on my Ubuntu system at least) you need to escape the % signs on the crontab.

17
By: root
2009-02-18 21:12:53

These Might Interest You

  • This command will dump a database on a remote stream to stdout, compress it, stream it to your local machine, decompress it and put it into a file called database.sql.You could even pipe it into mysql on your local machine to restore it immediately. I had to use this recently because the server I needed a backup from didn't have enough disk space.


    8
    ssh user@host "mysqldump -h localhost -u mysqluser -pP@$$W3rD databasename | gzip -cf" | gunzip -c > database.sql
    daws · 2009-10-05 00:57:51 3
  • This command will nicely dump a filesystem to STDOUT, compress it, encrypt it with the gpg key of your choice, throttle the the data stream to 60kb/s and finally use ssh to copy the contents to an image on a remote machine. Show Sample Output


    2
    nice -n19 dump -0af - /<filesystem> -z9|gpg -e -r <gpg key id>|cstream -v 1 -t 60k|ssh <user@host> "cat > backup.img"
    din7 · 2009-10-29 18:27:25 0
  • backup big mysql db to remote machine over ssh. "--skip-opt" option is needed when you can?t allocate full database in ram.


    9
    mysqldump -q --skip-opt --force --log-error=dbname_error.log -uroot -pmysqlpassword dbname | ssh -C user@sshserver 'cd /path/to/backup/dir; cat > dbname.sql'
    esplinter · 2010-05-29 23:06:04 0

  • 5
    mysqldump -uUSERNAME -pPASSWORD database | gzip > /path/to/db/files/db-backup-`date +%Y-%m-%d`.sql.gz ;find /path/to/db/files/* -mtime +5 -exec rm {} \;
    nadavkav · 2009-10-28 19:49:39 1

What Others Think

You may want to use / add. -c arcfour
flojo · 404 weeks ago
I've been meaning to figure out how to do a mysqldump over ssh for awhile. This is great. I like to use a slightly different pattern for the date, however. I like to add the hours, minutes, seconds and remove some hyphens. ssh user@host 'mysqldump dbname | gzip' > /path/to/backups/db-backup-`date +%Y%m%d-%H%M%S`.sql.gz
wonderwoob · 61 weeks and 6 days ago

What do you think?

Any thoughts on this command? Does it work on your machine? Can you do the same thing with only 14 characters?

You must be signed in to comment.

What's this?

commandlinefu.com is the place to record those command-line gems that you return to again and again. That way others can gain from your CLI wisdom and you from theirs too. All commands can be commented on, discussed and voted up or down.

Share Your Commands



Stay in the loop…

Follow the Tweets.

Every new command is wrapped in a tweet and posted to Twitter. Following the stream is a great way of staying abreast of the latest commands. For the more discerning, there are Twitter accounts for commands that get a minimum of 3 and 10 votes - that way only the great commands get tweeted.

» http://twitter.com/commandlinefu
» http://twitter.com/commandlinefu3
» http://twitter.com/commandlinefu10

Subscribe to the feeds.

Use your favourite RSS aggregator to stay in touch with the latest commands. There are feeds mirroring the 3 Twitter streams as well as for virtually every other subset (users, tags, functions,…):

Subscribe to the feed for: