Navigation

Archive | MySQL

My “Copy WordPress site to local” script

By on April 8, 2011 in Linux, MySQL, WordPress

I wanted to write a script that would copy a WordPress site to the local machine from a remote server (securely over ssh). This would allow me to set up a test environment or backup. First I need to make sure the site exists on the source server. Then I want to get the current wp-config settings, like the database name: 

echo "Checking $SRCSERVER for a site named $SRCSITENAME"
CHECKCMD="ls /PATH/TO/$SRCSITENAME/wp-config.php | grep wp-config.php > /dev/null; echo \$?"
CHECKFILE=$(ssh $SRCSERVER $CHECKCMD)
if [ $CHECKFILE -eq 0 ]; then
        echo "Source Site $SRCSITENAME on $SRCSERVER exists"
        SRCMYSQLSITEUSER=$(ssh $SRCSERVER grep "DB_USER" /PATH/TO/$SRCSITENAME/wp-config.php | cut -f 2 -d " " | cut -f 2 -d "'")
        SRCMYSQLSITEPASS=$(ssh $SRCSERVER grep "DB_PASSWORD" /PATH/TO/$SRCSITENAME/wp-config.php | cut -f 2 -d " " | cut -f 2 -d "'")
        SRCMYSQLSITEDB=$(ssh $SRCSERVER grep "DB_NAME" /PATH/TO/$SRCSITENAME/wp-config.php | cut -f 2 -d " " | cut -f 2 -d "'")
        echo "Source mySQL Site Database:"$SRCMYSQLSITEDB
        echo "Source mySQL Site Username:"$SRCMYSQLSITEUSER
        echo "Source mySQL Site Password:"$SRCMYSQLSITEPASS
else
        echo "Source Site $SRCSITENAME on $SRCSERVER does not exist"
        exit;
fi

Next I provision a new site with a new virtual host, and I copy over the files:

echo "Copying site from $SRCSERVER:/PATH/TO/$SRCSITENAME/ to /PATH/TO/$DESTSITENAME"
rsync --archive $SRCSERVER:/PATH/TO/$SRCSITENAME/ /PATH/TO/$DESTSITENAME

Next I provision a new blank MySQL database with the name, username, and password received in the first part for code, then I copy over the db data via ssh:

echo "Copying mysql data from $SRCSERVER to local database name $DESTSITENAME"
ssh $SRCSERVER "mysqldump -u root $SRCMYSQLSITEDB" | mysql -u root $DESTSITENAME

Finally I run a couple of SQL queries to change the URL that WordPress has inside the DB.


echo "What is going to be the WordPress URL"
read NEWURL

OLDSITENAME=$(mysql --batch --skip-column-names -e "use $DESTSITENAME;select guid from wp_posts LIMIT 1;" | cut -d"/" -f3)
echo "Changing the wp_posts guids from $OLDSITENAME to $NewURL

mysql --batch --skip-column-names -e "use $DESTSITENAME;UPDATE wp_posts SET guid = REPLACE (guid, '"http://$OLDSITENAME"','"http://$NEWURL"');"

echo "Changing the home and siterurl to $NewURL"

mysql --batch --skip-column-names -e "use $DESTSITENAME;UPDATE wp_options SET option_value = replace(option_value, '"http://$OLDSITENAME"', '"http://$NEWURL"') WHERE option_name = 'home' OR option_name = 'siteurl';"

Quick check if a mysql database exists

By on February 8, 2011 in Linux, MySQL

Here is my bash code that checks if a db exists before I try to create one in a script:

$DBNAME="dblookingfor"
DBEXISTS=$(mysql --batch --skip-column-names -e "SHOW DATABASES LIKE '"$DBNAME"';" | grep "$DBNAME" > /dev/null; echo "$?")
if [ $DBEXISTS -eq 0 ];then
	echo "A database with the name $DBNAME already exists. exiting"
	exit;
fi

This will exit out if there is a database with the name you are searching for. The tricky part for me (and always is) was this double quotes inside the single quotes in the LIKE statement.

Powered by WordPress. Designed by WooThemes