Skip to content

Instantly share code, notes, and snippets.

@maravedi
Last active August 29, 2015 14:13
Show Gist options
  • Save maravedi/840718b99cbc8b726beb to your computer and use it in GitHub Desktop.
Save maravedi/840718b99cbc8b726beb to your computer and use it in GitHub Desktop.
Shell Script for Importing MySQL into Homestead
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment