-
-
Save arx76/b4735610fb405b5adbdd3cda6ff14302 to your computer and use it in GitHub Desktop.
Revisions
-
csonuryilmaz revised this gist
Jan 29, 2017 . 1 changed file with 18 additions and 11 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -13,30 +13,37 @@ 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"; -
Jan 26, 2017 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -19,7 +19,7 @@ echo "DROP DATABASE IF EXISTS ${DB_NEW}; CREATE DATABASE ${DB_NEW}" | mysql ${DB [ $? -ne 0 ] && exit $?; echo "-- 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=1 -
christopher-hopper revised this gist
Jan 15, 2014 . 1 changed file with 2 additions and 2 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -24,9 +24,9 @@ MYSQL_TABLES=$(echo "SHOW TABLES" | mysql $DBCONN $DB_OLD | tail -n +2); [ $? -ne 0 ] && exit $?; COUNT=1 for TABLE in $MYSQL_TABLES; do 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"; MYSQL_CREATE="${MYSQL_CREATE}; $(echo SHOW CREATE TABLE ${TABLE}|mysql -B -r ${DBCONN} ${DB_OLD}|tail -n +2|cut -f 2-)"; [ $? -ne 0 ] && echo && exit $?; -
Jan 15, 2014 . 1 changed file with 7 additions and 2 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -9,8 +9,8 @@ DB_NEW=clone_mydatabase 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 "-- Create destination database"; @@ -24,7 +24,9 @@ MYSQL_TABLES=$(echo "SHOW TABLES" | mysql $DBCONN $DB_OLD | tail -n +2); [ $? -ne 0 ] && exit $?; COUNT=1 for TABLE in $MYSQL_TABLES; do 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" MYSQL_CREATE="${MYSQL_CREATE}; $(echo SHOW CREATE TABLE ${TABLE}|mysql -B -r ${DBCONN} ${DB_OLD}|tail -n +2|cut -f 2-)"; [ $? -ne 0 ] && echo && exit $?; @@ -36,6 +38,9 @@ for TABLE in $MYSQL_TABLES; do COUNT=$((COUNT+1)) done; 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"; -
Jan 15, 2014 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -31,7 +31,7 @@ for TABLE in $MYSQL_TABLES; do [ $COUNT -le 1 ] && echo -n " [ "; echo -n "."; [ $COUNT -ge 65 ] && echo " ]" && COUNT=0; COUNT=$((COUNT+1)) done; -
Jan 15, 2014 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,51 @@ #!/bin/bash DBUSER="root"; DBPASS=""; DBHOST="localhost"; DB_OLD=mydatabase DB_NEW=clone_mydatabase DBCONN="--host=${DBHOST} --user=${DBUSER} --password=${DBPASS}"; MYSQL_CREATE=''; MYSQL_INSERT=''; echo "Begin database clone (may take a while)"; echo "-- Create destination database"; echo "DROP DATABASE IF EXISTS ${DB_NEW}; CREATE DATABASE ${DB_NEW}" | mysql ${DBCONN}; [ $? -ne 0 ] && exit $?; echo "-- Select source table schemas"; MYSQL_TABLES=$(echo "SHOW TABLES" | mysql $DBCONN $DB_OLD | tail -n +2); [ $? -ne 0 ] && exit $?; COUNT=1 for TABLE in $MYSQL_TABLES; do MYSQL_INSERT="${MYSQL_INSERT}; INSERT INTO ${DB_NEW}.${TABLE} SELECT * FROM ${DB_OLD}.${TABLE}"; MYSQL_CREATE="${MYSQL_CREATE}; $(echo SHOW CREATE TABLE ${TABLE}|mysql -B -r ${DBCONN} ${DB_OLD}|tail -n +2|cut -f 2-)"; [ $? -ne 0 ] && echo && exit $?; [ $COUNT -le 1 ] && echo -n " [ "; echo -n "."; [ $COUNT -ge 25 ] && echo " ]" && COUNT=0; COUNT=$((COUNT+1)) done; [ $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.";