Oct 11 2009

Duplication de bases de données MySQL par connexions sécurisées ssh

Category: Linux,Script Shell,SQL

Résumé

Voici un script shell bash, getdb.sh, qui permet de lister, sélectionner et installer une copie locale de bases de données d'un serveur distant; le tout de façon totalement sécurisée via des connexions ssh. De plus, si le script détecte que des bases de même nom existent sur le poste client, il les renomme afin d'en assurer un backups (une sauvegarde en bon français) datées à la seconde pour empêcher les risques de collision.
L'impossibilité de lancer deux instances de ce script sur le même poste, assure aussi un meilleur niveau de sécurité.

Avertissements

Malgré toute l'attention que j'ai apporté à la confection de ce script il peut comporter des erreurs ou des incompatibilités avec votre système; son utilisation est « à vos risques et périls ».

Configuration

Les précautions d'usage étant faites, avant d'exécuter le script, il faut créer un fichier de configuration, disons serveur1.sh, de préférence dans le répertoire ~/.getdb/ que l'on créera pour l'occasion. Ce fichier de configuration permet de définir les paramètres de connexion ssh et MySQL. La documentation de la configuration est obtenue en faisant getdb.sh -h mais, comme ici on ne se refuse rien, la voici:

###############################################
########### 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 ###########
###############################################
  

Après avoir vérifié que le programme dialog est bien installé et effectué les sauvegardes nécessaires, on peut tester le script... soit en spécifiant le fichier de configuration avec l'option -f : getdb.sh -f ~/.getdb/serveur1.sh, soit sans option. Dans ce dernier cas, si getdb.sh trouve le fichier de configuration par défaut ~/.getdb/default.sh, il l'utilise, sinon une boite de dialogue permettra de sélectionner un fichier de configuration.

Présentation des processus et de l'interface

Lançons le script, les étapes de récupération des bases suivent.

Sélection des bases distantes

Une fois l'éventuelle boite de sélection du fichier de configuration passée, apparaît une boîte de sélection qui contient la liste de toutes les bases MySQL du poste distant :

dialog select box
Ceux qui aiment Xdialog peuvent décommenter la ligne suivante dans le script; Xdialog sera alors automatiquement utilisé sous X.

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

Sous X on peut alors obtenir cette interface à la place :

dialog select box
Par la suite les copies d'écran utilisant dialog seront présentées.

Création du dump distant et téléchargement

Pendant la création du dump distant et son téléchargement, une boîte de dialogue vous informe de l'avancement des taches.

dialog gauge

Installation locale des bases téléchargées

Une fois le téléchargement terminé, un nouvelle boîte de dialogue nous rappel l'état d'avancement des opérations et nous demande s'il faut installer les bases sur le poste client.

dialog gauge download


Si l'on choisit de les installer et que des bases de même nom sont déjà installées, une copie des ses bases est effectuée et un boîte de dialogue donne la liste des bases ainsi renommées.

dialog backup/rename

Une jauge nous informe de l'avancement de l'installation des bases et quand le processus est terminé, une boîte de dialogue récapitule les opérations importantes.

dialog gauge installation

Le script

Assez bavardé, voici le script getdb.sh.

#!/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

Conclusion

J'utilise très souvent ce script pour synchroniser, dupliquer ou simplement sauvegarder les bases de données de sites afin d'en avoir une version de développement local à jour. Il est bien sûr améliorable et l'on peut imaginer d'autres fonctionnalités sur cette base; par exemple un mode non interactif qui permettrait de planifier une tache cron de sauvegarde incrémentale de base de données.

Les idées d'améliorations ou de développements sont les bienvenues; ne pas hésiter à laisser des commentaires ou à me contacter.

Mots-clés :