Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/bin/bash -x
- set -e
- RSERVER="remote_server_ip"
- RSRV_USER="root"
- RHOST="remote_database_ip"
- RPORT="3306"
- RUSER="some_user"
- RPASS="some_pass"
- RDB="some_database"
- echo -e "\n###################################################"
- echo -e "### Setting up tunnel to remote database server"
- echo -e "###################################################\n"
- ssh -A -l "${RSRV_USER}" -L "10009:${RHOST}:${RPORT}" -N &
- sleep 1
- echo -e "\n###################################################"
- echo "### Testing database connection"
- echo -e "###################################################\n"
- if ! test -z "$( mysql "${RDB}" --host 127.0.0.1 -P 10009 -u "${RUSER}" -p"${RPASS}" --execute 'select 1 limit 0;' )" ; then
- echo "Failed to connect through local tunnel"
- exit 1
- fi
- echo -e "\n###################################################"
- echo -e "### Creating sed database script!"
- echo -e "###################################################\n"
- cat <<EOF > sql.sed
- #!/bin/sed -f
- s/DEFINER=.*[\*]/DEFINER=\`$RUSER\`@\`%\`*\/ \/\*/g
- 1s/^/\/\*\!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 \*\/;\\n/
- 1s/^/\/\*\!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 \*\/;\\n/
- 1s/^/\/\*\!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' \*\/;\\n/
- \$s/$/\\n\/\*\!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS \*\/;/
- \$s/$/\\n\/\*\!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS \*\/;/
- \$s/$/\\n\/\*\!40014 SET SQL_MODE=@OLD_SQL_MODE \*\/;/
- EOF
- echo -e "\n###################################################"
- echo -e "### Dumping database $RDB schema!"
- echo -e "###################################################\n"
- mysqldump --host "${RHOST}" -P 10009 \
- --opt \
- --force \
- --single-transaction \
- --insert-ignore \
- --quote-names "$RDB" \
- --ignore-table="${DRB}.innodb_monitor" \
- -u "$RUSER" \
- -p"$RPASS" \
- --no-data 2> >(grep -v "Using a password" 1>&2) | sed -f sql.sed | mysql
- if [ "$?" -ne 0 ] ; then
- echo "Failed to create schema!"
- exit 1
- fi
- echo -e "\n###################################################"
- echo -e "### Dumping database $DRB data"
- echo -e "###################################################\n"
- mysqldump --host "${RHOST}" -P 10009 -u "$RUSER" -p"$RPASS" \
- --opt \
- --single-transaction \
- --force \
- --insert-ignore \
- --quote-names "$DRB" \
- --ignore-table="${DRB}.innodb_monitor" \
- --ignore-table="${DRB}.log_url" \
- --ignore-table="${DRB}.log_url_info" \
- --ignore-table="${DRB}.log_customer" \
- --ignore-table="${DRB}.log_quote" \
- --ignore-table="${DRB}.report_event" \
- --ignore-table="${DRB}.dataflow_batch_export" \
- --ignore-table="${DRB}.dataflow_batch_import" \
- --ignore-table="${DRB}.core_file_storage" \
- --ignore-table="${DRB}.log_visitor_info" \
- --ignore-table="${DRB}.log_visitor" \
- --ignore-table="${DRB}.ecomdev_stock_import_tmp" 2> >(grep -v "Using a password" 1>&2) | sed -f sql.sed | mysql
- [ -f sql.sed ] && rm sql.sed
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement