Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Sync MySQL schema. This script doesn't delete table record if tables is exists AND if table doesn't exists, import table without record.
#!/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"
Owner

rluisr commented Aug 27, 2017

Changelog

  • 2017/08/27 Add -y argument: execute alter command without user input.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment