Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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.";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement