Shell Script for Importing MySQL into Homestead

homestead_mysql_import.sh

#!/bin/bash

R='\e[0;31m' # Red
G='\e[0;32m' # Green
Y='\e[0;33m' # Yellow
C='\e[0m'    # Text Reset
INFO="${Y}INFO${C}"
SUCCESS="${G}SUCCESS${C}"
ERROR="${R}ERROR${C}"

# Define a timestamp function
timestamp() {
  date +"%T"
}

OLD_DIR=$PWD
DATABASE="mydatabase"
FILENAME="mydatabase.sql"
DB_USER="homestead"
DB_PASS="secret"
WP_OPTIONS="wp_jkrg_options"
WP_POSTS="wp_jkrg_posts"
WP_POSTMETA="wp_jkrg_postmeta"
SITE_URL="http://mydomain.app"
CONNECT_MYSQL_CHECK="mysql -u$DB_USER -p$DB_PASS -s -N -e"
CONNECT_MYSQL_IMPORT="mysql -u$DB_USER -p$DB_PASS"
CONNECT_MYSQL_UPDATE="mysql -u$DB_USER -p$DB_PASS -D $DATABASE -e"
URL_FROM_USER=false
IMPORT_NEW=false


while getopts ":o:n:ih" opt; do
    case $opt in
        o)
            URL_FROM_USER=true
            OLD_SITE_URL=$OPTARG
            OLD_SITE_URL_WWW=$OPTARG
            ;;
        n)
            SITE_URL=$OPTARG
            echo "SITE_URL set to: ${SITE_URL}";
            ;;
        i)
            IMPORT_NEW=true
            ;;
        h)
            echo -e "You can pass the following arguments to the script:" >&2
            echo -e "\t-o\tOld URL - Requires an argument. Example: -o http://old-domain.com" >&2
            echo -e "\t-n\tNew URL - Requires an argument. Example: -n http://new-domain.com" >&2
            echo -e "\t-i\tPerform Import." >&2
            echo -e "\t-h\tShow this screen." >&2
            exit 1
            ;;
        \?)
            echo "Invalid option: -$OPTARG" >&2
            exit 1
            ;;
        :)
            echo "Option -$OPTARG requires an argument." >&2
            exit 1
            ;;
    esac
done

# Checking if MySQL database exists
echo -e "$(timestamp): Checking to see if $DATABASE already exists as a database...";
RESULT=$(${CONNECT_MYSQL_CHECK} "select schema_name from information_schema.schemata where schema_name='$DATABASE'");
if [ -z "$RESULT" ]; then
    echo -e "$(timestamp): -\t${INFO}: $DATABASE does not exist as a database.";
    echo -e "$(timestamp): -\t     Creating local WordPress database\n";
    CREATE=$(${CONNECT_MYSQL_CHECK} "create database $DATABASE");
    if [ -n "$CREATE" ]; then
        echo -e "$(timestamp): -\t${ERROR}: Database not created!\n";
    fi
else
    echo -e "$(timestamp): -\t${INFO}: $DATABASE already exists as a database!\n";
fi

if [ "$IMPORT_NEW" = true ]; then
    # Importing
    echo -e "$(timestamp): Importing WordPress tables into $DATABASE...";
    IMPORT=$(${CONNECT_MYSQL_IMPORT} ${DATABASE} < ${FILENAME});
    if [ -n "$IMPORT" ]; then
        echo -e "$(timestamp): -\t${ERROR}: Something went wrong!\n";
    else
        echo -e "$(timestamp): -\t${SUCCESS}: Import successful!\n";
    fi
fi

if [ ! $URL_FROM_USER ]; then
    # Finding wp_options.option_value where option_name = 'siteurl'
    echo -e "$(timestamp): Trying to find old site_url...";
    FIND=$(${CONNECT_MYSQL_UPDATE} "select option_value from $WP_OPTIONS where option_name = 'siteurl'");
    # Storing the MySQL query results into the array COLS
    read -ra COLS<<<${FIND}
    OLD_SITE_URL=${COLS[1]}
    OLD_SITE_URL_WWW=${COLS[1]}; #Setting this to be the same to make sure the code doesn't break further down
    if [ -z "$OLD_SITE_URL" ]; then
        echo -e "$(timestamp): -\t${ERROR}: Failed to find old siteurl!\n";
    else
        echo -e "$(timestamp): -\t${SUCCESS}: Found old siteurl -> $OLD_SITE_URL";
        if [[ "$OLD_SITE_URL" == *"www"* ]]; then
            SUB_URL=${OLD_SITE_URL:0:11}
            echo "SUB_URL is $SUB_URL";
        else
            echo -e "$(timestamp): -\t${INFO}: Old siteurl does not contain 'www'";
            PRE_URL=${OLD_SITE_URL:0:7}
            POST_URL=${OLD_SITE_URL:7}
            OLD_SITE_URL_WWW=$PRE_URL"www."$POST_URL
            echo -e "$(timestamp): -\t${SUCCESS}: Added 'www' to old siteurl -> $OLD_SITE_URL_WWW.  Will update both.\n";
        fi
    fi
fi


# Updating wp_options.option_value where option_name = 'siteurl'
echo -e "$(timestamp): Trying to update siteurl: $OLD_SITE_URL -> $SITE_URL...";
UPDATE=$(${CONNECT_MYSQL_UPDATE} "update $WP_OPTIONS set option_value = replace(option_value, '$OLD_SITE_URL', '$SITE_URL') where $WP_OPTIONS.option_name = 'siteurl'");
# Verifying that the update was successful
FIND=$(${CONNECT_MYSQL_UPDATE} "select option_value from $WP_OPTIONS where option_name = 'siteurl'");
# Storing the MySQL query results into the array COLS
read -ra COLS<<<$FIND
if [ ! -z "${COLS[1]}" ] && [ "${COLS[1]}"="$SITE_URL" ]; then
    echo -e "$(timestamp): -\t${SUCCESS}: site_url successfully updated to ${COLS[1]}!\n";
else
    echo -e "$(timestamp): -\t${ERROR}: Failed to update site_url!\n";
    echo -e $UPDATE;
fi

# Updating wp_options.option_value where option_name = 'home'
echo -e "$(timestamp): Trying to update home: $OLD_SITE_URL -> $SITE_URL...";
UPDATE=$(${CONNECT_MYSQL_UPDATE} "update $WP_OPTIONS set option_value = replace(option_value, '$OLD_SITE_URL', '$SITE_URL') where $WP_OPTIONS.option_name = 'home'");
UPDATE=$(${CONNECT_MYSQL_UPDATE} "update $WP_OPTIONS set option_value = replace(option_value, '$OLD_SITE_URL_WWW', '$SITE_URL') where $WP_OPTIONS.option_name = 'home'");
# Verifying that the update was successful
FIND=$(${CONNECT_MYSQL_UPDATE} "select option_value from $WP_OPTIONS where option_name = 'home'");
# Storing the MySQL query results into the array COLS
read -ra COLS<<<$FIND
if [ ! -z "${COLS[1]}" ] && [ "${COLS[1]}"="$SITE_URL" ]; then
    echo -e "$(timestamp): -\t${SUCCESS}: home successfully updated to ${COLS[1]}!\n";
else
    echo -e "$(timestamp): -\t${ERROR}: Failed to update home!\n";
    echo -e $UPDATE;
fi

# Updating wp_posts.guid
echo -e "$(timestamp): Trying to update guid values: $OLD_SITE_URL -> $SITE_URL...";
UPDATE=$(${CONNECT_MYSQL_UPDATE} "update $WP_POSTS set guid = replace(guid, '$OLD_SITE_URL', '$SITE_URL')");
UPDATE=$(${CONNECT_MYSQL_UPDATE} "update $WP_POSTS set guid = replace(guid, '$OLD_SITE_URL_WWW', '$SITE_URL')");
# Verifying that the update was successful
FIND=$(${CONNECT_MYSQL_UPDATE} "select guid from $WP_POSTS order by rand() limit 1");
# Storing the MySQL query results into the array COLS
read -ra COLS<<<${FIND}
if [ ! -z "${COLS[1]}" ] && [ "${COLS[1]}"=~"$SITE_URL" ]; then
    echo -e "$(timestamp): -\t${SUCCESS}: guid values successfully updated to $SITE_URL!\n";
else
    echo -e "$(timestamp): -\t${ERROR}: Failed to update guid values!\n";
fi

# Updating wp_posts.post_content
echo -e "$(timestamp): Trying to update post_content to include: $OLD_SITE_URL -> $SITE_URL...";
UPDATE=$(${CONNECT_MYSQL_UPDATE} "update $WP_POSTS set $WP_POSTS.post_content = replace(post_content, '$OLD_SITE_URL', '$SITE_URL')");
UPDATE=$(${CONNECT_MYSQL_UPDATE} "update $WP_POSTS set $WP_POSTS.post_content = replace(post_content, '$OLD_SITE_URL_WWW', '$SITE_URL')");
echo $UPDATE;
# Verifying that the update was successful
FIND=$(${CONNECT_MYSQL_UPDATE} "select post_content from $WP_POSTS order by rand() limit 1");
# Storing the MySQL query results into the array COLS
read -ra COLS<<<${FIND}
POST_CONTENT=${COLS[1]}
while [ -z $POST_CONTENT ]; do
    FIND=$(${CONNECT_MYSQL_UPDATE} "select post_content from $WP_POSTS order by rand() limit 1");
    read -ra COLS<<<${FIND}
    POST_CONTENT=${COLS[1]}
done
if [ ! -z "${COLS[1]}" ] && [ "$POST_CONTENT/$SITE_URL"="$SITE_URL" ]; then
    echo -e "$(timestamp): -\t${SUCCESS}: post_content successfully updated to include $SITE_URL!\n";
else
    echo -e "$(timestamp): -\t${ERROR}: Failed to update post_content!\n";
fi


# Updating wp_postmeta.meta_value
echo -e "$(timestamp): Trying to update meta_values to include: $OLD_SITE_URL -> $SITE_URL...";
UPDATE=$(${CONNECT_MYSQL_UPDATE} "update $WP_POSTMETA set $WP_POSTMETA.meta_value = replace(meta_value, '$OLD_SITE_URL', '$SITE_URL')");
UPDATE=$(${CONNECT_MYSQL_UPDATE} "update $WP_POSTMETA set $WP_POSTMETA.meta_value = replace(meta_value, '$OLD_SITE_URL_WWW', '$SITE_URL')");
# Verifying that the update was successful
FIND=$(${CONNECT_MYSQL_UPDATE} "select meta_value from $WP_POSTMETA order by rand() limit 1");
# Storing the MySQL query results into the array COLS
read -ra COLS<<<${FIND}
POST_CONTENT=${COLS[1]}
while [ -z $POST_CONTENT ]; do
    FIND=$(${CONNECT_MYSQL_UPDATE} "select meta_value from $WP_POSTMETA order by rand() limit 1");
    read -ra COLS<<<${FIND}
    POST_CONTENT=${COLS[1]}
done
if [ ! -z "${COLS[1]}" ] && [ "$POST_META/$SITE_URL"="$SITE_URL" ]; then
    echo -e "$(timestamp): -\t${SUCCESS}: meta_values successfully updated to include $SITE_URL!\n";
else
    echo -e "$(timestamp): -\t${ERROR}: Failed to update meta_values!\n";
fi


#echo "Using Artisan for migrations."
#echo cd laravel
#echo php-cli artisan migrate
#echo cd $OLD_DIR