Quick BASH Script to Dump & Compress a MySQL Database

A quick script I whipped up to dump my MySQL database.
Usage: sh backthatsqlup.sh

(be warned that it dumps ALL databases. This can get huge uncompressed)


#!/bin/sh
# Isam (Biodegradablegeek.com) public domain 12/28/2008
# Basic BASH script to dump and compress a MySQL dump

out=sequel_`date +'%m%d%Y_%M%S'`.sql
dest=/bx/

function e {
  echo -e "n** $1"
}

e "Dumping SQL file ($out). May take awhile..."
#echo "oh snap" > $out
sudo mysqldump -u root -p --all-databases > $out
if [ $? -ne 0 ]; then
  e "MySQL dump failed. Check that server is up and your username/pass"
  exit 7
fi

e "Uncompressed SQL file size"
du -hs $out

e "Compressing SQL file"
gz=$out.tar.gz
tar -zvvcf $gz $out
rt=$?

if [ $rt -ne 0 ]; then
  e "tar failed (error=$rt). Will NOT remove uncompressed SQL file"
else
  e "Removing uncompressed SQL file"
  rm -f $out
  out=$gz

  e "Compressed SQL file size"
  du -hs $out
fi

e "Moving shit to '$dest'"
sudo mv $out $dest

download BackThatSqlUp.sh

2 thoughts on “Quick BASH Script to Dump & Compress a MySQL Database”

  1. I have to say that for the past couple of hours i have been hooked by the impressive articles on this blog. Keep up the great work.

  2. On big tables it is better to do the table compression while exporting.
    Here is an example from a backup I use.
    Note that in my code the mysql will NOT lock the tables which might be not good for small tables.

    function mysqlBackup {
    DB=$1
    tfile=$Backup_Dest_Dir_DB/$DB.sql.gz;
    mysqldump –routines –triggers –single-transaction –quick -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS -r $tfile –add-drop-table $DB | gzip -9 > $tfile
    # tar cjf $tfile.tar.bz $tfile
    # rm $tfile
    }

    Gudata’s last blog post..Zip Codes and Distances

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>