Forked from christopher-hopper/clone-mysql-db.sh
Last active
August 29, 2015 14:23
-
-
Save dupsatou/fdfb9433908a49fa1496 to your computer and use it in GitHub Desktop.
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 characters
| #!/bin/bash | |
| DBUSER="root"; | |
| DBPASS=""; | |
| DBHOST="localhost"; | |
| DB_OLD=mydatabase | |
| 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"; | |
| 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}; 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 $?; | |
| [ $COUNT -le 1 ] && echo -n " [ "; | |
| echo -n "."; | |
| [ $COUNT -ge 65 ] && echo " ]" && COUNT=0; | |
| 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"; | |
| 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."; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment