Sync MySQL schema. This script doesn't delete table record if tables is exists AND if table doesn't exists, import table without record.

schemaSync.sh

#!/bin/bash -eu
#
# This script can sync MySQL schema without `DROP TABLE`.
# And If table isn't exists, import tables from source.
#
# Finally there is a confirmation screen.
#
# Need these packages: mysql, mysqldump, mysqldiff
# GitHub: @rluisr

# Color init
# ============
NORMAL=$(tput sgr0)
GREEN=$(tput setaf 2; tput bold)
YELLOW=$(tput setaf 3)
RED=$(tput setaf 1)

function red() {
    echo -e "$RED$*$NORMAL"
}

function green() {
    echo -e "$GREEN$*$NORMAL"
}

function yellow() {
    echo -e "$YELLOW$*$NORMAL"
}

# Script init
# ==============
CMDNAME=`basename $0`

FLAG_SRC_SSL=false;
FLAG_DST_SSL=false;
IGNORE_CHECK=false;

function Usage() {
  echo "Usage: ${CMDNAME} [--src-host <host>] [--src-user <user>] [--src-pass <pass>] [--src-db <db name>] [--dst-host <host>] [--dst-user <user>] [--dst-pass <pass>] [--dst-db <db name>]"
  echo "Option: [--src-ssl-ca <path>] [--dst-ssl-ca <path>]" # Note ssl option not supported yet
  echo "Option: [-y] execute alter command without user input."
  exit 1
}

if [ $# -ge 16 ]; then
  :
else
  echo "Not enough argument"
  Usage
fi

PARAM=()
for OPT in "$@"; do
  case "${OPT}" in
    "--src-host" )
      if [[ -z "$2" ]] || [[ "$2" =~ ^-+ ]]; then
        echo "${PROGNAME}: option requires an argument -- $( echo $1 | sed 's/^-*//' )" 1>&2
        exit 1
      fi
      readonly SRC_HOST="$2"
      shift 2
      ;;
    "--src-user" )
      if [[ -z "$2" ]] || [[ "$2" =~ ^-+ ]]; then
        echo "${PROGNAME}: option requires an argument -- $( echo $1 | sed 's/^-*//' )" 1>&2
        exit 1
      fi
      readonly SRC_USER="$2"
      shift 2
      ;;
    "--src-pass" )
      if [[ -z "$2" ]] || [[ "$2" =~ ^-+ ]]; then
        echo "${PROGNAME}: option requires an argument -- $( echo $1 | sed 's/^-*//' )" 1>&2
        exit 1
      fi
      readonly SRC_PASS="$2"
      shift 2
      ;;
    "--src-db" )
      if [[ -z "$2" ]] || [[ "$2" =~ ^-+ ]]; then
        echo "${PROGNAME}: option requires an argument -- $( echo $1 | sed 's/^-*//' )" 1>&2
        exit 1
      fi
      readonly SRC_DB="$2"
      shift 2
      ;;
    "--src-ssl-ca" )
      readonly SRC_SSL_CA="$2"
      FLAG_SRC_SSL=true
      shift 2
      ;;
    "--dst-host" )
      if [[ -z "$2" ]] || [[ "$2" =~ ^-+ ]]; then
        echo "${PROGNAME}: option requires an argument -- $( echo $1 | sed 's/^-*//' )" 1>&2
        exit 1
      fi
      readonly DST_HOST="$2"
      shift 2
      ;;
    "--dst-user" )
      if [[ -z "$2" ]] || [[ "$2" =~ ^-+ ]]; then
        echo "${PROGNAME}: option requires an argument -- $( echo $1 | sed 's/^-*//' )" 1>&2
        exit 1
      fi
      readonly DST_USER="$2"
      shift 2
      ;;
    "--dst-pass" )
      if [[ -z "$2" ]] || [[ "$2" =~ ^-+ ]]; then
        echo "${PROGNAME}: option requires an argument -- $( echo $1 | sed 's/^-*//' )" 1>&2
        exit 1
      fi
      readonly DST_PASS="$2"
      shift 2
      ;;
    "--dst-db" )
      if [[ -z "$2" ]] || [[ "$2" =~ ^-+ ]]; then
        echo "${PROGNAME}: option requires an argument -- $( echo $1 | sed 's/^-*//' )" 1>&2
        exit 1
      fi
      readonly DST_DB="$2"
      shift 2
      ;;
    "--dst-ssl-ca" )
      readonly DST_SSL_CA="$2"
      FLAG_DST_SSL=true
      shift 2
      ;;
    "-y" )
      readonly IGNORE_CHECK=true
      ;;
    -* )
      echo "${PROGNAME}: illegal option -- '$( echo $1 | sed 's/^-*//' )'" 1>&2
      exit 1
      ;;
    * )
      set +u
      if [[ -n "$1" ]] && [[ ! "$1" =~ ^-+ ]]; then
        PARAM+=( "$1" ); shift 2>&1
      fi
      set -u
      ;;
  esac
done

green "=== Backup"
# DBのバックアップを取得する
# ===========================
mysqldump -h ${SRC_HOST} -u ${SRC_USER} -p${SRC_PASS} ${SRC_DB} > /tmp/backup_${SRC_DB}.sql
mysqldump -h ${DST_HOST} -u ${DST_USER} -p${DST_PASS} ${DST_DB} > /tmp/backup_${DST_DB}.sql

green "=== Import table if not exists"
# 存在しないテーブルをインポートする
# ===================================
mysqldump --skip-add-drop-table -h ${SRC_HOST} -u ${SRC_USER} -p${SRC_PASS} -d ${SRC_DB} > /tmp/${SRC_DB}.sql
mysql -h ${DST_HOST} -u ${DST_USER} -p${DST_PASS} -f ${DST_DB} < /tmp/${SRC_DB}.sql > /dev/null 2>&1

green "=== Getting table names"
# ソースのテーブル名を配列に入れて，スキーマーなどを宛先と比較する
# =================================================================
# // テーブルの数は一緒なので片方からのみテーブル名は配列に入れる
tablesName=`mysql -h ${SRC_HOST} -u ${SRC_USER} -p${SRC_PASS} ${SRC_DB} -e "SHOW TABLES \G" | grep "Tables_in" | awk '{print $2}'`

arrayTablesName=(`echo ${tablesName}`)

green "=== Get diff of source and destination"
for i in "${arrayTablesName[@]}"; do
  set +e
  mdiff=`mysqldiff --force --skip-table-option --changes-for=server2 --difftype=sql --server1=${SRC_USER}:${SRC_PASS}@${SRC_HOST} --server2=${DST_USER}:${DST_PASS}@${DST_HOST} ${SRC_DB}.${i}:${DST_DB}.${i}`
  set -e

  if echo "${mdiff}" | grep -sq "PASS"; then
    continue
  fi

  alterCommand=`echo ${mdiff} | cut -d ' ' -f 37- | sed -e "s/ # Compare.*//g"`

  echo "${alterCommand}"

  # If set -y option
  if [ ${IGNORE_CHECK} = true ]; then
    executeAlter=`mysql -h ${DST_HOST} -u ${DST_USER} -p${DST_PASS} ${DST_DB} -e "${alterCommand}"`
    echo ${executeAlter}
  else
    while true; do
    read -p 'Execute this alter command? [Y/n]' Answer
    case ${Answer} in
      '' | [Yy]* )
        executeAlter=`mysql -h ${DST_HOST} -u ${DST_USER} -p${DST_PASS} ${DST_DB} -e "${alterCommand}"`
        break;
        ;;
      [Nn]* )
        echo "Ignored"
        break;
        ;;
      * )
        echo "Please type [Y/n]"
    esac
  done;
fi
done

green "=== Finished"

Changelog

• 2017/08/27 Add -y argument: execute alter command without user input.