Switching the WordPress database between local and remote

I use MAMP to develop and test PHP applications, including WordPress. It’s a great tool, and it definitely solves more problems than it creates, but developing locally does have a few drawbacks when it comes to WordPress.

To start with, the database in production changes all the time. So when you need to add a feature, you’re probably starting with old content. Sometimes that doesn’t matter, but sometimes you’re trying to fix a bug that has to do with how current data is being displayed, or you need to fix the particular custom field that being used. It’s a problem often enough that it can be a point of pain.

So you go into phpMyAdmin, do a backup of the database, import it into your local database, and then you have to go into your local phpMyAdmin panel and manually change two options that contain the url of the site to your local url (http://localhost:8888 or something like that).

Once you’re done, you might have made changes to the database that need to be copied to the remote version. Maybe you’re using the advanced custom fields plugin, and you’ve added a new field for your “Events” custom post type. Now you have to go back to the remote version of WordPress and do exactly the same thing you just did locally, or you need to do the reverse of what you did before, changing the options back to the remote url, exporting the local version of the site, and importing it in production.

If this happens often, which I’ve found to be the case, it can become a huge waste of time. Enter the command line!

mysql_dump FTW!

You can do all of the above tasks from the command line, plus a few extra (back up the database before you overwrite it!) using the command line. I have a bash script that I use for all my WordPress sites that downloads the database from one environment, backs up the other one, switches the urls, and imports it. It will switch from local to remote if you pass it the -r option, and the other way around if you pass it -l.


#! /bin/bash

# Universal settings
options_tbl="wp_options"


# Local DB credentials
local_user="root"
local_pass="root"
local_host="localhost"
local_dbname="wp_rq"

# Other local settings
local_url="http://localhost:8888/rq"
######
local_dbfile="dbbu_from_local/rq_bu_$(date +%Y.%m.%dT%H%M%S).sql"
local_mysql="/Applications/MAMP/Library/bin/mysql"
local_mysqldump="/Applications/MAMP/Library/bin/mysqldump"


# Remote DB credentials
remote_user="user"
remote_pass="pass"
remote_host="host"
remote_dbname="db_name"

# Other remote settings
remote_url="http://rickquantz.com"
######
remote_dbfile="dbbu_from_remote/rq_bu_$(date +%Y.%m.%dT%H%M%S).sql"
remote_mysql="mysql"
remote_mysqldump="mysqldump"




####################################################
# ---------- Do not edit below this point ----------

while getopts "rl" opt; do
	case $opt in
		r)
			action="to remote"
			;;
		l)
			action="to local"
			;;
	esac		
done

switch_url() {
	$local_mysql -u $local_user -h $local_host -p$local_pass $local_dbname <<EOF
		UPDATE $options_tbl
		SET option_value = "$1"
		WHERE option_name = "siteurl"
		OR option_name = "home"
EOF
}

if [ "$action" == "to local" ]; then
	
	echo "Transfering remote database to local"	
	
	echo "Backing up local database to $local_dbfile"
	$local_mysqldump -u $local_user -p$local_pass -h $local_host $local_dbname > $local_dbfile
	
	echo "Exporting remote database to $remote_dbfile"
	$remote_mysqldump -u $remote_user -p$remote_pass -h$remote_host $remote_dbname > $remote_dbfile

	echo "Importing remote database to local"
	$local_mysql -u $local_user -p$local_pass -h $local_host $local_dbname < $remote_dbfile

	switch_url $local_url
	echo "Database converted to url: $local_url"
		
fi

if [ "$action" == "to remote" ]; then
	
	echo "Transfering local database to remote"
	
	echo "Backing up remote database to $remote_dbfile"
	$remote_mysqldump -u $remote_user -p$remote_pass -h$remote_host $remote_dbname > $remote_dbfile
	
	switch_url $remote_url
	echo "Database converted to url: $remote_url"

	# Export database
	echo "Exporting local database to $local_dbfile"
	$local_mysqldump -u $local_user -p$local_pass -h $local_host $local_dbname $gt; $local_dbfile
	
	echo "Exporting local database to remote database"
	$remote_mysql -u $remote_user -p$remote_pass -h$remote_host $remote_dbname < $local_dbfile
	
	echo "Returning local database to local settings"
	switch_url $local_url
fi

A few notes if you decide to use this:

  • Be sure the file's permissions are set to executable.
  • There are two versions of the mysql commands that need to be configured. One is your system mysql, the other ($local_mysql) is the mysql utility for your local server. The one above is for MAMP. If you're using something else, you'll need to find that yourself.
  • Make sure to create two folders for your backups, dbbu_from_local and dbbu_from_remote. They're included in the zip file if you download it.
  • If you use MediaTemple, like I do, be sure to enable your IP for remote DB access in the MT admin panel.

I keep a copy for every WordPress site I maintain, with the auth details filled out. Could it be more robust? Certainly. Has it saved me lots of time? You bet it has.

Download the script