Remote MySQL Database Import

import-db.sh

#!/bin/bash

# Import Live Database
#
# This bash script will import the live catalog database into your local catalog db.
# You can choose to only dump the table structure, all the data,
# or you can specify exactly which tables you wish to import.
#
# Simply run `bash import-live-db.sh` and you will be prompted with what to do
#
# @author Kevin Jantzer, Blackstone Audio
# @since 2014-06-24


PULLHOST='remotedb.com'
PULLPORT='3306'
PULLUSER='dbuser'
PULLPW='dbpw'
PULLDB='dbname'

PUSHHOST='127.0.0.1'
PUSHPORT='3306'
PUSHUSER='root'
PUSHPW='root'
PUSHDB='dbname'

################################################################################################
# change below at your own risk
################################################################################################

OPTIONS='--single-transaction --compress' #--set-gtid-purged=OFF

# print out where data is importing from and to
echo "================== DB IMPORT ==================="
echo "Pulling $PULLDB ($PULLHOST)
Pushing to $PUSHDB ($PUSHHOST)"
echo

# ask user if data should be imported or only structure
read -p "Import Data? YES (y) or NO, structure only (n): " -n 1 -r
echo
if [[ $REPLY =~ ^[Yy]$ ]]
then

	# ask user which tables should be imported - if blank, all tables will be imported
	read -p "List tables to import – leave blank for ALL: " TABLES
	OPTIONS="$TABLES $OPTIONS"
	
else
	OPTIONS="$OPTIONS --no-data"
fi

# if importing tables, print out which tables we are importing
if [[ ! $TABLES =~ ^$ ]]
then
echo "Importing tables: $TABLES"
fi
echo

# if a password is required push database, setup that commmand
if [[ $PUSHPW ]]
then
	PUSHPW=" -p$PUSHPW"
fi

DOPULL=`mysqldump -h$PULLHOST --port=$PULLPORT -u$PULLUSER -p$PULLPW $PULLDB $OPTIONS | mysql -h$PUSHHOST --port=$PUSHPORT -u$PUSHUSER $PUSHPW $PUSHDB`

echo "------------------------------------------------"
echo "All done!"
echo "================================================"