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

Category: Linux,Script Shell,SQLPh. Ivaldi @ 1 h 36 min

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 :


syncsite.sh: shell script to synchronize files and databases from a web site through secure SSH connection

Category: Divers,Linux,Script Shell,Typo3,WordPressPh. Ivaldi @ 21 h 59 min

Presentation

syncsite.sh is a bash shell script that allow to duplicate, backup or synchronize a remote WEB site, like CMS, through a secure connection SSH. Thank to integrated preprocessing and postprocessing variables to his external configuration system files, it can be adapted to all type of CMS.

Operation

All the connections mentioned here are secure.
  • The data base
    • syncsite connects to the remote site, request the generation of a compressed MySQL dump of the database referenced by the variable db_name, so transfers the dump on the client and, if the variable auto_install_db is true, it decompresses the file ;
    • in the case of an automatic DB, the bash shell script contained in the configuration variable pre_process_dump_shell_script is applied to the SQL dump before his execution ;
    • always in the case of an automatic DB installation, the DB is installed after a backup of the database has been made ; when the variable make_db_backup is false, this backup is destroyed once the installation was successful.
    • finally, if the variable post_process_sql_file_script is set, the SQL file which it refers is executed on the data base.
  • The files
    • remote directories referenced by the variable remote_sync_dirs are synchronized using the software rsync in the client directory referenced by the variable local_root_dir ; this allows only update files that have been changed since the last synchronization ;
    • finally, the shell script contained in the variable post_process_shell_script is executed with the parameter local_root_dir.

Configuration

syncsite is configured using external files and to specify the configuration file to use one uses the option -f.
This gives eg syncsite.sh -f ~/.syncsite/my_site.sh.
If no configuration file is specified, synsite looks for the configuration file ~/.syncsite/default.sh ; if this file does not exist, a dialog interface allows to choose one ergonomically.

Configuration files are already predefined for the CMS TYPO3 and WordPress. They allow in particular to automatically retrieve the name of the database and authentication settings directly in the configuration files of the site.

TYPO3

The configuration file for TYPO3 can be found HERE

WordPress

The configuration file for WORDPRESS can be found HERE

Conclusion

The project is under Git control at this address.
It depends of bash, MySQL, bizip2 and rsync.

Feel free to propose the configuration file of your favorite CMS.

Mots-clés :