Script bash pour transférer une base de donnée MySQL d’un serveur à l’autre

#!/bin/bash

# ////////////////  Some config  //////////////
TMPDIR="/tmp/"
mysql=`which mysql`
mysqldump=`which mysqldump`

#
#    MigrateMySQL v 1.0
#    by Etienne Lachance and Renoir Boulanger
#
#    https://renoirboulanger.com/projets/
#
#    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
#    (at your option) 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/>.
#

# ////////////////  Actual script  //////////////
PN=`basename "$0"`          # program name
VER='1.0'

usage () {
echo "
USAGE INFORMATION

  NAME
       $PN

  DESCRIPTION
       Tool to help transfer a MySQL database from a remote site using ssh and
       mysqldump. You only need one copy of this script, on the server you want
       to import the databases to.

  SYNOPSIS
       $PN transfer
       $PN import

  OPTIONS
       transfer     transfer database dump file from remote host to localhost
       import       import database dump to database

  FILES
       $PN.conf
                    Config file saved in the directory that you invoked $PN

  LICENCE
       MigrateMySQL is a software provided under the GNU GENERAL PUBLIC LICENCE
       Copyright (C) 2010 by Etienne Lachance and Renoir Boulanger
       This program comes with ABSOLUTELY NO WARRANTY; for details, see source code.
       This is free software, and you are welcome to redistribute it
       under certain conditions;

                                     https://renoirboulanger.com/
"
    exit 1
}

dump_config_file () {
echo "#
# Generated by $PN version $VER on `date`
#" > ${PWD}/$PN.conf
echo 'SSH_SOURCE="hostname"
SSH_PORT=
SSH_USER="username"
#SSH_PASS=""
ALT_PURGE=""                      # En cas ou le mysqldump utilise a pas de DROP TABLE (default: false)
DB_SOURCE_DBNAME=""
DB_SOURCE_USER=""
DB_SOURCE_PASS=""
DB_SOURCE_HOST=""                 # e.g. mysql4.local    (defaults: localhost)
DB_DEST_DBNAME=$DB_SOURCE_DBNAME
DB_DEST_USER="root"
DB_DEST_PASS="root"
DB_DEST_HOST=""                   # e.g. mysql4.local    (defaults: localhost)
DB_DEST_USERHOST=""               # e.g. webservers-%    (defaults: localhost)
DB_DEST_USERNAME="root"           # site username
DB_DEST_USERPW="root"             # site password
#OPTS_KEEP_COMPRESSED=""          # 1 or 0               (1=true) (not yet implemented)
OPTS_COMPRESSION_PROG="gzip"        # bzip2,gzip,zip       (only gzip is implemented)
' >> ${PWD}/$PN.conf
echo " * Config file ${PWD}/$PN.conf created, modify it, then run $PN again.";
}

function dump_prepare_sql {
  echo "-- Generated by $PN version $VER on `date`" > ${TMPDIR}/$PN.$DB_DEST_DBNAME.sql.tmp
}

function dump_prepare_createdb {
  echo "CREATE DATABASE $DB_DEST_DBNAME;" >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp
}

function dump_prepare_addpriv {
  echo "CREATE USER '$DB_DEST_USERNAME'@'$DB_DEST_USERHOST' IDENTIFIED BY '$DB_DEST_USERPW';" >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp
  echo "GRANT USAGE ON * . * TO '$DB_DEST_USERNAME'@'$DB_DEST_USERHOST' IDENTIFIED BY '$DB_DEST_USERPW';" >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp
  echo "GRANT ALL PRIVILEGES ON \`$DB_DEST_DBNAME\` . * TO '$DB_DEST_USERNAME'@'$DB_DEST_USERHOST';" >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp
  echo "FLUSH PRIVILEGES;" >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp
}

function dump_stdout_tellpasswords {
  echo "
Here is the application database credentials.

 --------------------------------------------------------
|         for WordPress, copy that code block            |
 --------------------------------------------------------
define('DB_SERVER',   '$DB_DEST_HOST');
define('DB_NAME',     '$DB_DEST_DBNAME');
define('DB_LOGIN',    '$DB_DEST_USERNAME');
define('DB_PASSWORD', '$DB_DEST_USERPW');
 --------------------------------------------------------
"
}
function fatal { echo "FAILED: $1"; exit 1; }

function purge_tables {
  echo -n " * Will purge tables from $DB_DEST_DBNAME ... "
  echo "-- Generated by $PN version $VER on `date`" > ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.purge.tmp

  echo -n 'Generating tmp file ... '
  DROPTABLES=`$mysql -u $DB_DEST_USER -h $DB_DEST_HOST -p"$DB_DEST_PASS" -Bse 'show tables' $DB_DEST_DBNAME |sed 's/^/drop table /g' | sed 's/$/;/g'`
  echo $DROPTABLES >> ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.purge.tmp

  echo -n 'Executing ... '
  cat ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.purge.tmp | $mysql -h $DB_DEST_HOST -u $DB_DEST_USER -p"$DB_DEST_PASS" $DB_DEST_DBNAME

  echo -n 'Deleting tmp file ... '
  rm -rf ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.purge.tmp
  echo "DONE"
}

# ///////////////////// ACTUAL EXECUTION //////////////////////
echo >&2 "$PN - Migrate MySQL database, $VER by Evocatio Solutions Technologies  http://evocatio.com/"
if [ ! -z $1 ]; then
  if [ ! -f ${PWD}/$PN.conf ]; then
    echo " * You need to have a configuration file"
    dump_config_file
    exit 1
  fi
  echo -n " * Reading configuration file ${PWD}/$PN.conf ... "
    source ${PWD}/$PN.conf
  echo "DONE"
fi

case "$1" in
  transfer)             # Initiate transfer
      echo -n " * Checking if needed vars are set ... "
        ERROR_MSG=""
        if [ "$SSH_SOURCE" = "" ]; then ERROR_MSG="$ERROR_MSG NO SSH_SOURCE";fi
      if [ -z "$SSH_USER" ]; then ERROR_MSG="$ERROR_MSG NO SSH_USER";fi
      if [ ! -z "$SSH_PORT" ]; then SSH_PORT_STR="-p $SSH_PORT";fi
      if [ -z "$DB_SOURCE_DBNAME" ]; then ERROR_MSG="$ERROR_MSG NO DB_SOURCE_DBNAME";fi
      if [ -z "$DB_SOURCE_USER" ]; then ERROR_MSG="$ERROR_MSG NO DB_SOURCE_USER";fi
      if [ -z "$DB_SOURCE_PASS" ]; then ERROR_MSG="$ERROR_MSG NO DB_SOURCE_PASS";fi
      if [ -z "$DB_SOURCE_HOST" ]; then DB_SOURCE_HOST="localhost";fi
      if [ -z "$OPTS_KEEP_COMPRESSED" ]; then OPTS_KEEP_COMPRESSED="1";fi
      if [ -z "$OPTS_COMPRESSION_PROG" ]; then OPTS_COMPRESSION_PROG="gzip";fi
      if [ ! -z "$ERROR_MSG" ]; then fatal "$ERROR_MSG";fi
    echo "DONE"

      echo -n " * Checking if remote database exist ... "
        DB_EXIST=0
        DBS=`ssh $SSH_PORT_STR $SSH_USER@$SSH_SOURCE "$mysql  -h $DB_SOURCE_HOST -u $DB_SOURCE_USER -p\"$DB_SOURCE_PASS\" -Bse 'show databases'| egrep -v 'information_schema|$mysql'"`
      for db in $DBS; do
        if [ "$db" = "$DB_SOURCE_DBNAME" ]; then DB_EXIST=1; fi;
      done
      if [ "$DB_EXIST" = "0"  ]; then fatal "DB_SOURCE_DBNAME : $DB_SOURCE_DBNAME does not exist";fi
      echo "DONE"

      echo -n " * Get remote database dump file ... "
        ssh $SSH_PORT_STR $SSH_USER@$SSH_SOURCE "$mysqldump -u $DB_SOURCE_USER -p"$DB_SOURCE_PASS" --opt $DB_SOURCE_DBNAME > ${TMPDIR}$DB_SOURCE_DBNAME.sql; gzip -f ${TMPDIR}$DB_SOURCE_DBNAME.sql; cat ${TMPDIR}$DB_SOURCE_DBNAME.sql.gz" > $DB_SOURCE_DBNAME.sql.gz
      echo "DONE"

      echo " * The local database file is $DB_SOURCE_DBNAME.sql.gz"
      echo "Finished!"
      exit 0;;


  import)                   # Initiate import
      echo -n " * Checking if needed vars are set ... "
        ERROR_MSG=""
      if [ -z "$DB_DEST_DBNAME" ]; then ERROR_MSG="$ERROR_MSG NO_DEST_DBNAME";fi
      if [ -z "$DB_DEST_USER" ]; then ERROR_MSG="$ERROR_MSG NO_DEST_USER";fi
      if [ -z "$DB_DEST_PASS" ]; then ERROR_MSG="$ERROR_MSG NO_DEST_PASS";fi
      if [ -z "$DB_DEST_HOST" ]; then DB_DEST_HOST="localhost";fi
      if [ -z "$DB_DEST_USERHOST" ]; then DB_DEST_USERHOST="localhost";fi
      if [ -z "$OPTS_COMPRESSION_PROG" ]; then OPTS_COMPRESSION_PROG="gzip";fi
      if [ ! -z "$ERROR_MSG" ]; then fatal "$ERROR_MSG";fi
    echo "DONE"

    if [ -f $DB_SOURCE_DBNAME.sql.gz ]; then
      echo -n " * Uncompressing the database dump (Only gzip is supported) ... "
      gunzip $DB_SOURCE_DBNAME.sql.gz
      echo "DONE"
    fi

    if [ ! -f $DB_SOURCE_DBNAME.sql ]; then
      fatal "You should have a database dump $DB_SOURCE_DBNAME.sql"
    fi

    echo -n " * Checking if the requested database exists ... "
        DB_EXIST=0
        DBS=`$mysql -u $DB_DEST_USER -h $DB_DEST_HOST -p"$DB_DEST_PASS" -Bse 'show databases'| egrep -v 'information_schema|$mysql'`
      for db in $DBS; do
        if [ "$db" = "$DB_DEST_DBNAME" ]; then DB_EXIST=1; fi;
      done
      if [ "$DB_EXIST" = "1"  ]; then
        echo -n "$DB_DEST_DBNAME exists, overwrite? 'y/n': "
        read DOWEPURGE

        case "$DOWEPURGE" in
            'y')
            if [ ! -z $ALT_PURGE ]; then
                            purge_tables
            fi
            OVERRIDEUSERCREATION="true"
            ;;
            *)
              fatal "DB_DEST_DBNAME : $DB_DEST_DBNAME exists. And we did not understand the answer to purge DB.";
              ;;
        esac
      else
          echo "DONE"
        echo -n " * Creating database ... "
        dump_prepare_sql
        dump_prepare_createdb
        cat ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp | $mysql -h $DB_DEST_HOST -u $DB_DEST_USER -p"$DB_DEST_PASS"
        echo "DONE"
      fi

    if [ -z $OVERRIDEUSERCREATION ] ; then
    echo -n " * Creating default user ... "
      dump_prepare_sql
      dump_prepare_addpriv
      cat ${TMPDIR}$PN.$DB_DEST_DBNAME.sql.tmp | $mysql -h $DB_DEST_HOST -u $DB_DEST_USER -p"$DB_DEST_PASS"
    echo "DONE"
    fi

    echo -n " * Restoring database ... "
      cat $DB_SOURCE_DBNAME.sql | $mysql -h $DB_DEST_HOST -u $DB_DEST_USER -p"$DB_DEST_PASS" $DB_DEST_DBNAME
    echo "DONE"

    dump_stdout_tellpasswords

      echo "Finished!"

      exit 0;;
  *)  usage;exit 0;;
esac