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