mysqlbak.sh

#!/bin/bash

if [ -z "$1" ];  then
    echo "Usage:"
    echo "mysqlbak backup username hostname dbname|ALL mybackup.sql"
    echo "mysqlbak restore  username hostname  dbname|ALL mybackup.sql"
    exit 0
fi

action=$1
myuser=$2
myserver=$3
dbname=$4
if [ -z "$5" ]; then
    if [ "$action" == "restore" ]; then
        echo "Input filename missing."
        exit 1
    else
    myfile=backup_$(date +%F).sql
    fi
else
    myfile=$5
fi

if [ "$action" == "backup" ]; then
    if [ "$dbname" == "ALL" ]; then
        mysqldump --all-databases --single-transaction --quick --lock-tables=false -u $myuser -h $myserver -p > $myfile
    else
        mysqldump -u $myuser -h $myserver -p $dbname > $dbname-$myfile
    fi
fi

if [ "$action" == "restore" ]; then
    if [ "$dbname" == "ALL" ]; then
        mysql -u $myuser -h $myserver -p < $myfile
    else
        read -r -p "Are you sure to DROP DATABASE $dbname? [y/N] " response
        if [[ "$response" =~ ^([yY][eE][sS]|[yY])$ ]]; then
            mysql -u $myuser -h $myserver -p -e "DROP DATABASE $dbname"
        fi
        mysql -u $myuser -h $myserver -p -e "create database $dbname"
        mysql --database=$dbname -u $myuser -h $myserver -p < $myfile
    fi
fi

#mysql -u root -h localhost -p
#use dbname
#source bak_database.sql