Advertisement
Guest User

Untitled

a guest
Feb 6th, 2017
173
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.83 KB | None | 0 0
  1. #!/bin/bash
  2.  
  3. DBUSER=tempuser
  4. DBPASS=tempuser
  5. DB_OLD=nightly_test
  6. DB_NEW=nightly_ttest
  7. DBHOST=db2.int.fanxchange.com #localhost
  8.  
  9. while [[ $# > 0 ]]
  10. do
  11. key="$1"
  12. value="$2"
  13. case $key in
  14. -u|--db-user)
  15. DBUSER=$value
  16. shift
  17. ;;
  18. -p|--db-pass)
  19. DBPASS=$value
  20. shift
  21. ;;
  22. -s|--db-source)
  23. DB_OLD=$value
  24. shift
  25. ;;
  26. -d|--db-dest)
  27. DB_NEW=$value
  28. shift
  29. ;;
  30. -h|--db-host)
  31. DBHOST=$value
  32. shift
  33. ;;
  34. *)
  35. echo "Option: "$key # Unknown option
  36. ;;
  37. esac
  38. shift
  39. done
  40.  
  41. echo $DBUSER
  42. echo $DBPASSWORD
  43. echo $DBSNAME
  44. echo $DBNAME
  45. echo $DBSERVER
  46. echo 'DONE'
  47.  
  48. if [ -z "$DBUSER" ]; then
  49. echo "DB user is required"
  50. fi
  51.  
  52. if [ -z "$DBPASSWORD" ]; then
  53. echo "DB pass is required"
  54. fi
  55.  
  56. #DBHOST="db2.int.fanxchange.com";
  57. #
  58. #DB_OLD=nightly
  59. #DB_NEW=nightly_dev_dev
  60.  
  61. DBCONN="--host=${DBHOST} --user=${DBUSER} --password=${DBPASS}";
  62.  
  63. MYSQL_CREATE='set foreign_key_checks = 0';
  64. MYSQL_INSERT='set foreign_key_checks = 0';
  65.  
  66. echo "Begin database clone (may take a while)";
  67. #echo -n "-- Do you want to create destination database? (if exists will be dropped) (y/n)?";
  68. #read answer
  69. #if echo "$answer" | grep -iq "^y" ;then
  70. DATABASE_COLLATION=$(echo "SELECT @@character_set_database, @@collation_database;" | mysql $DBCONN $DB_OLD | tail -n 1 | awk '{ print $2 }');
  71. echo "DROP DATABASE IF EXISTS ${DB_NEW}; CREATE DATABASE ${DB_NEW} COLLATE ${DATABASE_COLLATION}" | mysql ${DBCONN};
  72. [ $? -ne 0 ] && exit $?;
  73. #fi
  74.  
  75. echo -e "\n -- Select source table schemas";
  76. MYSQL_TABLES=$(echo "SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'" | mysql $DBCONN $DB_OLD | tail -n +2 | awk '{ print $1 }');
  77.  
  78. [ $? -ne 0 ] && exit $?;
  79. COUNT=0
  80. for TABLE in $MYSQL_TABLES; do
  81. echo -n "${TABLE}";
  82. TABLE="\`${TABLE}\`";
  83.  
  84. MYSQL_INSERT="${MYSQL_INSERT}; ALTER TABLE ${DB_NEW}.${TABLE} DISABLE KEYS";
  85. MYSQL_INSERT="${MYSQL_INSERT}; INSERT INTO ${DB_NEW}.${TABLE} SELECT * FROM ${DB_OLD}.${TABLE}";
  86. MYSQL_INSERT="${MYSQL_INSERT}; ALTER TABLE ${DB_NEW}.${TABLE} ENABLE KEYS";
  87.  
  88. 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");
  89. MYSQL_CREATE="${MYSQL_CREATE}; ${CREATE_SQL}";
  90.  
  91. [ $? -ne 0 ] && echo && exit $?;
  92.  
  93. echo -e "\t [ok]";
  94.  
  95. COUNT=$((COUNT+1))
  96. done;
  97. echo "-- Total ${COUNT} tables will be cloned ..."
  98.  
  99. MYSQL_CREATE="${MYSQL_CREATE}; set foreign_key_checks = 1";
  100. MYSQL_INSERT="${MYSQL_INSERT}; set foreign_key_checks = 1";
  101.  
  102. [ $COUNT -gt 1 ] && echo " ]";
  103.  
  104. echo "-- Create destination table schemas";
  105. echo "${MYSQL_CREATE};" | mysql $DBCONN $DB_NEW
  106.  
  107. [ $? -ne 0 ] && exit $?;
  108.  
  109. echo "-- Insert source data into destination tables";
  110. echo "${MYSQL_INSERT};" | mysql $DBCONN $DB_NEW
  111.  
  112. [ $? -ne 0 ] && exit $?;
  113.  
  114. echo "Done.";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement