up vote 19 down vote favorite
16
share [g+] share [fb]

Usually after dumping a MySQL database with mysqldump command I immediately tar/gzip the resultant file. I'm looking for a way to do this in one command:

So from this:

mysqldump dbname -u root -p > dbname.sql
tar czvf dbname.sql.tgz dbname.sql
rm dbname.sql

To something like this:

mysqldump dbname -u root -p > some wizardry > dbname.sql.tgz

Or even better (since I'm usually scp'ing the dump file to another server):

mysqldump dbname -u root -p > send dbname.sql.tgz to user@host

I'm running bash on debian.

link|improve this question
100% accept rate
feedback

5 Answers

up vote 31 down vote accepted 
mysqldump --opt <database> | gzip -c | ssh user@wherever 'cat > /tmp/yourfile.sql.gz'

You can't use tar in a pipe like this, and you don't need it anyway, as you're only outputting a single file. tar is only useful if you have multiple files.

link|improve this answer
4  
You're right about not needing tar, but you could use it in the pipeline if you did, with mysqldump | tar cf - | gzip -c | ssh ... 'cat > file.tgz' – Darren Chamberlain Jan 27 '10 at 0:15
Does that actually work? I'm pretty sure tar needs a list of filenames to work on. – James Jan 27 '10 at 10:25
I updated this to work locally (not on a remote ssh server) oh, and I use a dynamic name based on date, thanks to original poster & answerer! mysqldump --opt <database> | gzip -c | cat > $(date +%Y-%m-%d-%H.%M.%S).sql.gz – electblake Apr 11 '11 at 13:41
2  
@electblake: you don't need to be using 'cat' if it's local. Just gzip -c > $(date +%Y-%m-%d-%H.%M.%S).sql.gz – James Jan 3 at 18:58
feedback
up vote 7 down vote

Use a named pipe.

mkfifo mysql_pipe
gzip -9 -c < mysql_pipe > name_of_dump.gz &
mysqldump database > mysql_pipe 
rm mysql_pipe

I use it all the time, it'a awesome.

http://en.wikipedia.org/wiki/Named_pipe

link|improve this answer
3  
James does the same thing in 1 line. – Jonathan Haddad Feb 3 '10 at 17:28
5  
..but learning about named pipes is worth it :-) – Tomasz Zieliński Feb 15 '11 at 15:34
feedback
up vote 2 down vote

I wrote a quick script to suck down a remote mysql database. It uses mysql compression, gzip and ssh compression. Sucked down a multi GB database at an incredible rate.

    ssh -C user@host "mysqldump --opt --compress database <table> | gzip -9 -c" > outputfile.sql.gz

A side benefit is that it requires no free space on the source database server, so you can use it to backup a database on a server with zero free disk space before going in an pruning your data.

Hope it helps somebody.

link|improve this answer
I've created a simple shell script: #!/bin/bash if [ -z "$1" ]; then echo "Usage: ${0} [host] [user] [database] [outputFile]" exit else HOST=$1 fi if [ -z "$2" ]; then echo "Usage: ${0} ${1} [user] [database] [outputFile]" exit else USER=$2 fi if [ -z "$3" ]; then echo "Usage: ${0} ${1} ${2} [database] [outputFile]" exit else DB=$3 fi if [ -z "$4" ]; then OUTFILE="${DB}.sql.gz" else OUTFILE=$4 fi COMMAND="ssh -C ${USER}@${HOST} \"mysqldump --opt ${DB} | gzip -9 -c\" > ${OUTFILE}" ssh -C ${USER}@${HOST} "mysqldump --opt ${DB} | gzip -9 -c" > ${OUTFILE} – Tony Dillon Sep 12 '11 at 18:52
feedback
up vote 1 down vote

You can do like:

mysqldump --add-drop-table -h dbhost -u dbuser -p dbname (tablename tablename ... ) | gzip -c > wp.sql.gz

e.g. mysqldump --add-drop-table -h localhost -u root -p wordpress | gzip -c > wp.sql.gz

link|improve this answer
feedback
up vote 1 down vote

If you are running this locally just use the following command to backup your database & zip it using gzip:

mysqldump -u userName -p (passwordPrompt) yourDatabaseName | gzip -v > output.gz
link|improve this answer
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.