TUTORIAL: Developing Locally on WordPress with Remote Database Over SSH

Today, I went about setting up a local WordPress install for some development I am doing at work. The problem that existed is that I didn’t want to bring the database from the existing development server site into my local MySQL instance. It’s far too big. I figured this could be done via an SSH tunnel and so, I set abut trying to figure it out. The situation worked flawlessly and so, for your sake (and for myself for the future), I give you the steps.

Setting up the SSH Tunnel

I run a local MySQL server and that runs on the standard MySQL port 3306. So as these things go, I can’t bind anything else to port 3306 locally. I have to use an alternate port number. I chose 5555, but you can use whatever you want.

The command to run in a Terminal window is:

ssh -N -L 5555:127.0.0.1:3306 remoteuser@remotedomain.com -vv

A little bit about what this means.

the -N flag means that when connecting via SSH, we are not going to execute any commands. This is necessary for tunnelling as, we literally, will not execute any commands on the remote server. Therefore, we won’t get a command prompt.

the -L flag tells SSH that we are going to port forward. The following portion, 5555:127.0.0.1:3306 combined with the -L flag means, literally, forward all traffic on localhost (127.0.0.1) connecting on port 5555 to the remote server’s port 3306 (standard MySQL listening port).

The remote server and ssh connection is handled by remoteuser@remotedomain.com. This seems obvious, but just in case. You may be prompted to enter your SSH password.

The final part can be omitted, but I like to keep it there so I know what’s happening. The -vv flag tells the SSH daemon to be extra verbose about what is happening with the connection. It’s sort of a good way to debug if you need to, and to know that the port forwarding is actually taking place.

Configuring WordPress to use the Tunnel

Now that we have a successful SSH tunnel, you have to configure WordPress to use it. In the wp-config.php file, simply modify the DB_HOST constant to read:

1
define( 'DB_HOST', '127.0.0.1:5555' );

You need to add two more variables, though, to override WordPress’ existing siteurl and home options to allow you to work with the localhost domain, instead of redirecting to the remotedomain.com that is configured in WordPress.

1
2
3
define( 'WP_HOME', 'http://localhost' );

define( 'WP_SITEURL', 'http://localhost' );

BOOM!

With these configurations in place, loading up WordPress should now load in the database content from the remote host and you can get to work on local development. Word to the wise… don’t close the terminal window with the tunnel or the tunnel will be severed. If you have to minimize it so it’s not annoying you, go for it… just don’t close it.

HOLIDAY WORDPRESS DEAL: 30 Minute WordPress Consultation

As the holidays kick into full gear, and people start looking at a short sprint to gift times with family, I am offering a 30 minute phone consultation related to WordPress for $100. This is a great thing for someone who needs to figure out how to do something that may be unfamiliar for them or if they are just getting setup with WordPress and need plugin recommendations, or the like.

To setup your 30 minute appointment or to inquire about gifting that appointment, fill out this form. For the description, just reference this deal.

For longer term commitments or consulting engagements, including custom plugin development, I am booking clients for Dec and Jan, so feel free to ask about those opportunities as well.

This offer is only good today, Friday Nov 23, 2012 until midnight Central Time.

This offer is good through Midnight, Monday, November 26, 2012 – Cyber Monday.

GNIP Spells a Whole New World for Data APIs

Allow me to get nerdy.

It has been a long time since I got downright giddy about something developer-oriented. Lots of new APIs are coming out all the time and I usually take a once over look at them to determine if there is something cool there. A lot of time there are cool things and I promise myself to come back and explore the possibilities later. I rarely do.

However, with the announcement of GNIP today, I finally feel like my incessant mulling of API frameworks might be coming to an end.

Let me back up. A few weeks ago, I was fiddling with a bunch of APIs trying to create some mashup I was working on. I sent Keith a direct message pitching a “crazy idea”. An API for all APIs. One API to rule them all. His response, “A meta API?”

That made sense and made me laugh because I know how much he hates the word “meta”.

My idea quickly dissipated as I realized it was probably pretty futile to create an API for all these varied services that all had different data formats and types and my need for it wasn’t all that important at the time anyway.

I could have also used the concept when I was working on Mokonji, the project that now sits dead because Trackur beat me to the punch.

The idea with GNIP, bringing this story full circle, is that it is a meta-API. It sits in front of “data producers” (Digg, Flickr, Disqus) and provides a standardized API for “data consumers” (Plaxo, MyBlogLog, even Lijit!) to exchange data.

Since this is still so very early, there are bound to be other data producers and consumers. Also notable is that the only data format is XML. XMPP and JSON are missing. That will likely change over time too.

Data Producers not yet involved that should be:

And a few Data Consumers that are also missing:

For PHP Devs, a Twitter PHP Class

At the end of this post, this site is going into a twitter free period of two weeks. I’m sensitive to the fact that we talk about Twitter quite a lot and not always doing a good job of reaching into all of real life like we’d like. So after this post, Twitter will not be mentioned here until June 12. :-)

However, I wanted to get this out the door for devs to knock on and bang out. Awhile ago, I created the dctwits Twitter group and released the generic code. It included a Twitter class created by David Billingham and slightly modified for our use.

A few days ago, I released the WP-Twitterpitch plugin which also used the same class. It’s a very useful class but, to be honest, was a little messy, didn’t support XML and JSON and didn’t have support for all the Twitter API.

So I cleaned it up, extended it, fleshed it out a bit more, brought in Keith Casey as a developer and we’re basically launching the class as a version 1.0-beta today.

I’ll work on documenting things a bit more but there is some basic usage on the site and the code itself is pretty well documented. I need testers to bang on this code and submit issues back, via the Google Code page. Patches welcome as well. And I’d love to see how you use this. You can download direct or via SVN.

When Keith gets done with the DC PHP Conference, we’ll look at pushing it out as stable.

The Art of the Mashup

The other day, I was talking to the CTO of a company that is working to build a web technology solution for a problem that exists due to the arcane infrastructure and systems already in place in the niche target industry. He was mourning the fact that, after spending gobs of time wireframing and re-wireframing a solution, the parties who initially expressed interest in licensing the technology, had decided to walk away from the table for a variety of reasons.

The big conglomerate that had decided to walk had expressed concern over the fact that they already had systems for billing and other management aspects of their company and didn’t want to invest in something unknown and untried over their long-standing, yet antiquated, solution.

Over the course of an hour or so, and even since then, we looked over his wireframes determining what the company should look like in order to make some sales, if not all the sales he wanted. I realized that his product was designed in such a way that dependencies were created everywhere. If a customer wanted just this one portion that does employee management but not the other part that does billing, there was no productive way to do this so he could make a sale without making the big sale.

In the web world, we talk about mashups. Take a google map and mash it up with Twitter and you have Twittervision. Mashup Basecamp with FreshBooks and tie in Salesforce and you’ve got a complete back-office CRM-Billing system to build your company on top of.

The strength of mashups is the distributed nature of the work. I no longer have to store my own video files because YouTube will do it for me and give me a means to access it, thus eliminating the overhead and cost of doing business associated with that video. I no longer have to worry about the development time and money needed to distribute a widget containing my content to other websites because Clearspring does that work for me. The trick is in APIs which allow others to innovate on top of the technologies created by others.

My advice to this entrepreneur was to create APIs between his various modules and build out-of-the-box products that he could sell that utilize those APIs. In fact the APIs can be open and still be paid access, which provides another stream of revenue – especially when his clients have the money to pay top dollar for those APIs if they consider the alternative cost of throwing out entire chunks of their existing infrastructure and using an out of the box solution that may not meet their unique needs.