Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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;
- 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
- exit 1
- }
- if [ $# -eq 16 ] || [ $# -eq 18 ] || [ $# -eq 20 ]; 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
- ;;
- -* )
- 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}"
- 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;
- done
- green "=== Finished"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement