dbcopy.sh

dbcopy.sh

#!/bin/bash

DBUSER=tempuser
DBPASS=tempuser
DB_OLD=nightly_test
DB_NEW=nightly_ttest
DBHOST=db2.int.fanxchange.com #localhost

while [[ $# > 0 ]]
do
  key="$1"
  value="$2"
  case $key in
      -u|--db-user)
      DBUSER=$value
      shift
      ;;
      -p|--db-pass)
      DBPASS=$value
      shift
      ;;
      -s|--db-source)
      DB_OLD=$value
      shift
      ;;
      -d|--db-dest)
      DB_NEW=$value
      shift
      ;;
      -h|--db-host)
      DBHOST=$value
      shift
      ;;
      *)
        echo "Option: "$key  # Unknown option
      ;;
  esac
  shift
done

echo $DBUSER
echo $DBPASSWORD
echo $DBSNAME
echo $DBNAME
echo $DBSERVER
echo 'DONE'

if [ -z "$DBUSER" ]; then
  echo "DB user is required"
fi

if [ -z "$DBPASSWORD" ]; then
  echo "DB pass is required"
fi

#DBHOST="db2.int.fanxchange.com";
#
#DB_OLD=nightly
#DB_NEW=nightly_dev_dev

DBCONN="--host=${DBHOST} --user=${DBUSER} --password=${DBPASS}";

MYSQL_CREATE='set foreign_key_checks = 0';
MYSQL_INSERT='set foreign_key_checks = 0';

echo "Begin database clone (may take a while)";
#echo -n "-- Do you want to create destination database? (if exists will be dropped) (y/n)?";
#read answer
#if echo "$answer" | grep -iq "^y" ;then
DATABASE_COLLATION=$(echo "SELECT @@character_set_database, @@collation_database;" | mysql $DBCONN $DB_OLD | tail -n 1 | awk '{ print $2 }');
echo "DROP DATABASE IF EXISTS ${DB_NEW}; CREATE DATABASE ${DB_NEW} COLLATE ${DATABASE_COLLATION}" | mysql ${DBCONN};
[ $? -ne 0 ] && exit $?;
#fi

echo -e "\n -- Select source table schemas";
MYSQL_TABLES=$(echo "SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'" | mysql $DBCONN $DB_OLD | tail -n +2 | awk '{ print $1 }');

[ $? -ne 0 ] && exit $?;
COUNT=0
for TABLE in $MYSQL_TABLES; do
    echo -n "${TABLE}";
    TABLE="\`${TABLE}\`";

    MYSQL_INSERT="${MYSQL_INSERT}; ALTER TABLE ${DB_NEW}.${TABLE} DISABLE KEYS";
    MYSQL_INSERT="${MYSQL_INSERT}; INSERT INTO ${DB_NEW}.${TABLE} SELECT * FROM ${DB_OLD}.${TABLE}";
    MYSQL_INSERT="${MYSQL_INSERT}; ALTER TABLE ${DB_NEW}.${TABLE} ENABLE KEYS";

    CREATE_SQL=$(echo "SHOW CREATE TABLE ${TABLE}" | mysql -B -r ${DBCONN} ${DB_OLD} |tail -n +2 | cut -f 2- | sed -e "s/NOT NULL DEFAULT '0000-00-00 00:00:00'/NOT NULL/g" | sed -e "s/NOT NULL DEFAULT '0000-00-00'/NOT NULL/g");
    MYSQL_CREATE="${MYSQL_CREATE}; ${CREATE_SQL}";

    [ $? -ne 0 ] && echo && exit $?;

    echo -e "\t [ok]";

    COUNT=$((COUNT+1))
done;
echo "-- Total ${COUNT} tables will be cloned ..."

MYSQL_CREATE="${MYSQL_CREATE}; set foreign_key_checks = 1";
MYSQL_INSERT="${MYSQL_INSERT}; set foreign_key_checks = 1";

[ $COUNT -gt 1 ] && echo " ]";

echo "-- Create destination table schemas";
echo "${MYSQL_CREATE};" | mysql $DBCONN $DB_NEW

[ $? -ne 0 ] && exit $?;

echo "-- Insert source data into destination tables";
echo "${MYSQL_INSERT};" | mysql $DBCONN $DB_NEW

[ $? -ne 0 ] && exit $?;

echo "Done.";