###############################################
########### VARIABLES CONFIGURATION ###########
local_db_user='USER_NAME'  # Optional
local_db_pass='PASSWORD'   # Optional

remote_db_user='USER_NAME' # Mandatory
remote_db_pass='PASSWORD'  # Mandatory

remote_ip='90.223.2.143'   # IP server address

# Dump files
local_sql_file='/tmp/mysql_remote.sql'
remote_sql_file="$local_sql_file"

# ssh user
ssh_auth_name='USER'

# command ssh & options
ssh_cmd='/usr/bin/ssh -p PORT -i /home/MOI/.ssh/id_rsa'
# command scp & options
scp_cmd='/usr/bin/scp -P PORT -i /home/MOI/.ssh/id_rsa'
########## END OF THE CONFIGURATION ###########
###############################################
  

# [ -z $DISPLAY ] && DIALOG=dialog || DIALOG=Xdialog
  

#!/bin/bash -E
# -E: ERR trap is inherited by shell functions.

# getdb.sh: shell script that duplicates remote MySQL databases locally.
# getdb.sh: duplique des bases de données MySQL distantes en local.

# Copyright (C) 2009  Philippe Ivaldi http://www.piprime.fr/
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

SELF=$(basename $0)

help()
{
    printf "Usage: %s [-fFILE]\n" $SELF
    echo -e "Download and install remote databases.
FILE is the configuration file, it must be executable
and contains the folowing informations.
###############################################
local_db_user='USER_NAME'  # Optional
local_db_pass='PASSWORD'   # Optional

remote_db_user='USER_NAME' # Mandatory
remote_db_pass='PASSWORD'  # Mandatory

remote_ip='90.223.2.143'   # IP server address

# Dump files
local_sql_file='/tmp/mysql_remote.sql'
remote_sql_file=\"$local_sql_file\"

# ssh user
ssh_auth_name='USER'

# command ssh & options
ssh_cmd='/usr/bin/ssh -p PORT -i ~/.ssh/id_rsa'
# command scp & options
scp_cmd='/usr/bin/scp -P PORT -i ~/.ssh/id_rsa'
###############################################

If file configuration is not given, getdb.sh try to
source '~/.getbd/default.sh'; if not available, a dialog box
allow to select one.
"
    exit 1
}

abort()
{
    local exit_status=$?
    echo "Process aborted with exit code $exit_status"
    exit $exit_status
}

while getopts f:h option
do
    case $option in
        f)
            conf_file="${OPTARG}"
            ;;
        *)
            help
            ;;
    esac
done

DIALOG=dialog ## I prefer dialog
# Uncomment if you want automatic switch between dialog and Xdialog
# [ -z $DISPLAY ] && DIALOG=dialog || DIALOG=Xdialog

[ -f "${HOME}/.getdb/default.sh" ] && conf_file="${HOME}/.getdb/default.sh"

if [ ! "$conf_file" ]
then
    [ -d "${HOME}/.getdb/" ] && sdir="${HOME}/.getdb/" || sdir="$HOME/"

    exec 3>&1 # open fd3
    conf_file=$($DIALOG --backtitle "$SELF" --aspect 9 \
        --title "Choice the file configuration" \
        --fselect "$sdir" 0 0 2>&1 1>&3)
    code=$?
    exec 3>&- # close fd3

    case $code in
        0) : ;;
        *)
            abort
            ;;
    esac
fi

[ -f "${conf_file}" ] && {
    . "${conf_file}" || abort
} || {
    echo "${conf_file} is not an valid file"
    exit 1
}

ssh_auth="${ssh_auth_name}@${remote_ip}"

choice_file=$(tempfile 2>/dev/null) || choice_file='/tmp/choice_'
back_db_file=$(tempfile 2>/dev/null) || \
    back_db_file='/tmp/back_db_file_'
pid_file="$(dirname $back_db_file)/${SELF}.pid"
log_file="$(dirname $back_db_file)/${SELF}.log"

[ -e "$pid_file" ] && {
    echo "${pid_file} exists"
    echo "An other $SEFL process is running"
    echo 'Process Aborted !'
    exit 1
}

# Init temporary files
:> "$back_db_file" || abort
:> "$choice_file" || abort
:> "$log_file" || abort
echo $$ > "$pid_file" || abort

# Do this when exit, interupt, abort etc
cleanup() { # clean up after work
    rm -f "${choice_file}" "${pid_file}" "${back_db_file}" 2> /dev/null
}
trap "cleanup;abort" HUP INT ABRT QUIT SEGV TERM
trap "cleanup" EXIT

# Rename db $1 to db $2
rename_local_db()
{
    mysql -u${local_db_user} -p${local_db_pass} \
        -e"CREATE DATABASE \`$2\`;" || abort
    mysqldump -u${local_db_user} -p${local_db_pass} "$1" | \
        mysql -u${local_db_user} -p${local_db_pass} -D "$2" || abort
    mysql -u${local_db_user} -p${local_db_pass} -e"DROP DATABASE \`$1\`;"
}

# Message to the dialog gauge.
gauge()
{
    echo $1
    echo 'XXX'
    echo "$2"
    echo 'XXX'
}

# Array of databases name
databases=($($ssh_cmd ${ssh_auth} \
    "mysql --batch --skip-column-names -u${remote_db_user} \
    -p${remote_db_pass} -e'SHOW DATABASES'"))

# the min between 15 and the line number to display
nb_line=$(echo "15
    ${#databases[*]}" | sort -n | head -1)

$DIALOG --backtitle "$SELF" \
    --title "Duplicate Remote Databases Locally" \
    --checklist "Databases to duplicate locally" 20 61 $nb_line \
    $(echo ${databases[*]} | tr ' ' '\n' | \
    awk '{print $1 " _ off\\"}') 2> "$choice_file" || abort

databases=$(cat "$choice_file")
[ ! "$databases" ] && {
    echo 'No Databases Selected.'
    abort
}

data_list() {
    echo ${databases} | sed 's/ /\\n/g'
}

(
    gauge 20 'Creating remote compressed databases dumps...'
    $ssh_cmd ${ssh_auth} \
        "mysqldump -u${remote_db_user} -p${remote_db_pass} \
    --databases ${databases} | bzip2 -c > ${remote_sql_file}.bz2" || abort


    gauge 60 'Downloading remote database dump...'
    ${scp_cmd} ${ssh_auth}:${remote_sql_file}.bz2 ${local_sql_file}.bz2 || abort


    gauge 90 'Deleting remote temporary files...'
    eval "$ssh_cmd ${ssh_auth} \"rm -f ${remote_sql_file}.bz2\"" || abort

    gauge 100 'Download Terminated...'

) | $DIALOG --backtitle "$SELF" \
    --title "Remote Process" --gauge "Process" 10 61 0

[ ! $? -eq 0 ] && exit 1

$DIALOG --backtitle "$SELF" \
    --title "Download Terminated" --defaultno \
    --yesno "A dump of the folowing databases have been downloaded.\n\n
$(data_list)\n\n
Do you want to install them locally ?" $((nb_line+2)) 61

if [ $? -eq 0 ]
then # Local Installation
    STEP=$((98/(nb_line+1)))
    [ $STEP -eq 0 ] && STEP=1
    COUNT=$STEP
    (
        gauge $COUNT 'Uncompressing dump database...'
        bzip2 -d -f "${local_sql_file}.bz2" || abort

        for database in $databases
        do
            gauge $((COUNT-1)) "Retrieve information about $database..."
            # Erase the quotes protection
            database=$(echo "$database" | sed 's/^"\|"$//g')
            db_exist=$(mysql -N --batch -u${local_db_user} -p${local_db_pass} \
                -e"SELECT IF(EXISTS (SELECT SCHEMA_NAME FROM information_schema.schemata \
WHERE schema_name = '$database'),'true','false');") || abort
            [ "$db_exist" == 'true' ] && { # Sauvegarde effective de la base
                gauge $((COUNT+1)) "Creating local database backup for ${database}..."
                database_backup="${database}_$(date +%Y_%m_%d:%H-%M-%S)"
                rename_local_db "$database" "$database_backup" || abort
                echo "${database} => ${database_backup}" >> "$back_db_file"
            }
            COUNT=$((COUNT+STEP))
        done
    ) | $DIALOG --backtitle "$SELF" \
        --title "Backup Process" --gauge "$SELF" 10 61 0

    [ ! $? -eq 0 ] && exit 1

    [[ $(cat "$back_db_file") != "" ]] && {
        MESSRENAME="The folowing databases have been RENAMED in order \
to intall the new ones :\n
$(cat $back_db_file)\n\n"
        $DIALOG --backtitle "$SELF" --title "Database Backup" --aspect 9 \
            --msgbox "$MESSRENAME" 0 0
    }

    (
        # Prevent external interruption of the sub-process
        trap "echo 1 > ${pid_file}.sub
echo \"\nDatabases installation process INTERRUPTED prematurely !\" \
>> $log_file" HUP INT ABRT QUIT SEGV TERM
        mysql -u${local_db_user} -p${local_db_pass} \
            < "$local_sql_file" && rm -f "$local_sql_file" "${local_sql_file}.bz2"
        echo $? > "${pid_file}.sub"
    ) &> "${log_file}" &

    # Gauge for the current process
    PID=$!
    COUNT=70
    ps -o "%p" | grep -sq $PID
    while [ $(echo $?) -eq 0 ]
    do
        gauge $COUNT 'Creating duplicate local databases...'
        COUNT=$(($COUNT+5))
        [ $COUNT -gt 90 ] && COUNT=70
        sleep 1
        ps -o "%p" | grep -sq $PID
    done | $DIALOG --backtitle "$SELF" \
        --title "Duplication Process" --gauge "Process" 10 61 0

    [ $(cat "${pid_file}.sub") -eq 0 ] && { # Installation successes
        MESS="The folowing databases have been installed :\n
$(data_list)\n\n"
        $DIALOG --backtitle "$SELF" --title "Process terminated" \
            --aspect 9 \
            --msgbox "Process terminated successfully.\n${MESSRENAME}${MESS}" 0 0
        $DIALOG --clear
        echo -e "${MESSRENAME}${MESS}"
    } || { # Installation fails
        $DIALOG --backtitle "$SELF" --title "Process terminated" \
            --aspect 9 \
            --msgbox "A problem occurs during the installation of the
downloaded databases." 0 0
        $DIALOG --backtitle "$SELF" --title "Process terminated" \
            --aspect 9 --textbox "$log_file" 0 0
    }
else # No Installation
    MESS="A dump of the folowing databases have been downloaded \
in the file ${local_sql_file}\n
$(data_list)\n\n"
    $DIALOG --backtitle "$SELF" --title "Database Backup" \
        --aspect 9 \
        --msgbox "$MESS" 0 0
    $DIALOG --clear
    echo -e $MESS
fi