Export or Backup MySQL Database in separate file using Shell Script

Live projects database are always critical for Clients and Companies and if the development phase is still in progress than you need to take backup of database on daily basis for safety concerns. i came across with the same situation and for that i've created a simple script which will take backup of your database.

it will export table wise separate sql file rather than one big SQL file. you can also make zip file for all the tables with gzipped option (dbname.gz).

This script is created for MySQL database only and it;s been tested on Mac OS X and Ubuntu OS.
You need to set path for MySQLDump and MySQL in script file.

# Backup each mysql databases into a different file, rather than one big file
# Optionally files can be gzipped (dbname.gz)
#
# Usage: dump_all_databases [ -u username -o output_dir -z ]
#   
# -u username to connect mysql server
# -o [output_dir] optional the output directory where to put the files
# -z gzip enabled


PROG_NAME=$(basename $0)
USER=""
PASSWORD=""
OUTPUTDIR=${PWD}
GZIP_ENABLED=0
GZIP=""

MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

while getopts u:o:z OPTION
do
    case ${OPTION} in
        u) USER=${OPTARG};;
        o) OUTPUTDIR=${OPTARG};;
        z) GZIP_ENABLED=1;;
        ?) echo "Usage: ${PROG_NAME} [ -u username -o output_dir -z ]"
           exit 2;;
    esac
done

if [ "$USER" != '' ]; then

echo "Enter password for" $USER":"
oldmodes=`stty -g`
stty -echo
read PASSWORD
stty $oldmodes

fi

if [ ! -d "$OUTPUTDIR" ]; then
    mkdir -p $OUTPUTDIR
fi

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

# dump each database in turn
for db in $databases; do
    echo $db
 if [ $GZIP_ENABLED == 1 ]; then
  $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD --databases $db | gzip > "$OUTPUTDIR/$db.gz"
 else
     $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD --databases $db > "$OUTPUTDIR/$db.sql"
    fi    
done

Run this script in Terminal :
sh ./dump_all_databases.sh [ -u username -o output_dir -z ]

Note : The script will prompt for a password, you cannot specify it as command line argument for security reasons.


References :
snowforg.net
MySQL