cluster-db-smartcopy.sh sourcehost sourcedb desthost destdb password

cluster-db-smartcopy.sh

#!/bin/bash

#Source
USER=root
PASS=$5
PORT=3306
HOST1=$1
HOST2=$1

#Dest
DEST_HOST1=$3
DEST_HOST2=$3
DEST_USER=root
DEST_PASS=$5
DEST_PORT=3306
DEST_DATABASE=$4

DATABASE=$2
TABLE_EXCLUDE=cache%
CONDITION="AND table_schema IN ('$DATABASE') AND table_name NOT LIKE '$TABLE_EXCLUDE' AND table_name NOT IN ('search_index', 'search_dataset', 'search_node_links', 'watchdog', 'feeds_item', 'feeds_item_copy', 'feeds_item_copy2')"
COMPRESS=0

BIGTABLEROWS=50000

PVARGS="-p -t -e -r -b -a -c"

THREADS=5

# Thread manager
function threadlimiter {
   while [ `jobs | wc -l` -ge $THREADS ]
   do
      sleep 5
   done
}

# Create destination if needed.  TODO: enable compression by default
mysql -h${DEST_HOST} -u${DEST_USER} -p${DEST_PASS} -P${DEST_PORT} -e "create database ${DEST_DATABASE} default charset utf8;"

COUNT=0
FAILS=0
echo "Locating tables.."
for DBTB in `mysql -h${HOST1} -u${USER} -p${PASS} -A --skip-column-names -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') $CONDITION ORDER BY (data_length+index_length) DESC"`
do
    (( COUNT++ ))

    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`

    if [ $((COUNT%2)) -eq 0 ]; then
      HOST=$HOST1
      DEST_HOST=$DEST_HOST1
    else
      HOST=$HOST2
      DEST_HOST=$DEST_HOST2
    fi

    if nc -w 5 -zv ${HOST} ${PORT} 2>/dev/null
    then
      HOSTACTIVE=1
    else
      echo "${HOST} not available."
      HOSTACTIVE=0
      (( FAILS++ ))
    fi

    # Wait for an available thread
    threadlimiter

    # Init next table
    COPYTABLE=0

    # compare row counts
    if [ $COPYTABLE -ne 1 ]; then
      TEST="row count"
      COMPAREA=`mysql -h${HOST} -u${USER} -p${PASS} -P${PORT} ${DB} -sre "SELECT COUNT(*) FROM ${TB}" | grep -o '[0-9]*'`
      COMPAREB=`mysql -h${DEST_HOST} -u${DEST_USER} -p${DEST_PASS} -P${DEST_PORT} ${DEST_DATABASE} -sre "SELECT COUNT(*) FROM ${TB}" | grep -o '[0-9]*'`
      SOURCEROWS=$COMPAREA
      if [[ "$COMPAREA" != "$COMPAREB" ]]; then
        COPYTABLE=1
 #       echo "$HOST:$DB.$TB -> $DEST_HOST:$DEST_DATABASE.$TB: $COMPAREA != $COMPAREB ($TEST)"
      fi
    fi

    # compare checksum
    if [ $COPYTABLE -ne 1 ]; then
      TEST="checksum"
      COMPAREA=`mysql -h${HOST} -u${USER} -p${PASS} -P${PORT} ${DB} -sre "CHECKSUM TABLE ${TB}" | grep -o '[0-9]*'`
      COMPAREB=`mysql -h${DEST_HOST} -u${DEST_USER} -p${DEST_PASS} -P${DEST_PORT} ${DEST_DATABASE} -sre "CHECKSUM TABLE ${TB}" | grep -o '[0-9]*'`
      if [[ "$COMPAREA" != "$COMPAREB" ]]; then
        COPYTABLE=1
#        echo "$HOST:$DB.$TB -> $DEST_HOST:$DEST_DATABASE.$TB: $COMPAREA != $COMPAREB ($TEST)"
      fi
    fi

    if [ $COPYTABLE -eq 1 ]; then

      # more than $BIGTABLEROW rows, we handle differently
      if [ $SOURCEROWS -gt $BIGTABLEROWS ]; then
#        echo "BIG TABLE: $TB"
        mysqldump -h$HOST -u$USER -p$PASS -d --quick --single-transaction --skip-comments --skip-dump-date ${DB} ${TB} | mysql -h${DEST_HOST} -u${DEST_USER} -p${DEST_PASS} -P${DEST_PORT} ${DEST_DATABASE}
        mysqldump -h$HOST -u$USER -p$PASS --quick --extended-insert=FALSE --single-transaction --skip-comments --skip-dump-date --no-create-info=TRUE --hex-blob --triggers ${DB} ${TB} | pv ${PVARGS} -l -s ${SOURCEROWS} -N ${TB} | mysql -h${DEST_HOST} -u${DEST_USER} -p${DEST_PASS} -P${DEST_PORT} ${DEST_DATABASE} &
      else
        mysqldump -h$HOST -u$USER -p$PASS -d --quick --single-transaction --skip-comments --skip-dump-date ${DB} ${TB} | mysql -h${DEST_HOST} -u${DEST_USER} -p${DEST_PASS} -P${DEST_PORT} ${DEST_DATABASE}
        mysqldump -h$HOST -u$USER -p$PASS --quick --extended-insert=TRUE --delayed-insert --single-transaction --skip-comments --skip-dump-date --no-create-info=TRUE --hex-blob --triggers ${DB} ${TB} | pv ${PVARGS} -l -s ${SOURCEROWS} -N ${TB} | mysql -h${DEST_HOST} -u${DEST_USER} -p${DEST_PASS} -P${DEST_PORT} ${DEST_DATABASE} &
      fi
    else
      COPYTABLE=0
#      echo "SKIP: $HOST:$DB.$TB == $DEST_HOST:$DEST_DATABASE.$TB: $COMPAREA == $COMPAREB ($TEST)"
    fi

done

wait
echo "Done."