A A

Quick BASH Script to Dump & Compress a MySQL Database

Sat, Dec 27, 2008

Automation, Code, Linux, Projects, SQL, Scripts

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

BackThatSqlUp.sh
Add me. I'm lonely Why not subscribe to the feed?. If you’re on a mobile device I suggest Viigo

Tags: , , , ,

1 Comments For This Post

  1. Gudata Says:

    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