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.

TECHcocktail DC – The DC Tech Scene is definitely back

I have seen my share of networking events. Back during the dotcom era it was full of open bars and crazy companies with the latest software to change your life in some way. Then it was all about buying stuff on the web or a portal for something or another.

After the bubble burst most people were just trying to hold on and all that you had a choice between in the DC area were NVTC (Northern VA Tech Council) and Potomac Officer Club events. NVTC was very government focused and who mostly showed up were service providers (I have the 100’s of insurance and lawyer business cards to prove it). POC events were big events with well known people but not alot of good networking.

One good networking event I liked was the Tech Prayer breakfast but that was only once a year. What most of us were left with was going to conferences, usually not here, to get our networking on and find fellow entrepreneurs and real innovative thinkers.

Lately, there has been a change in the winds here in the DC area. With events like PodCampDC and Social Media Club’s events we are starting to see our cutting edge tech scene finally re-emerge. Last Thursday night it was totally confirmed with the TECH Cocktail DC event. It was held at MCCXXIII (1223) in DC. A swanky place that is over-priced for my usual weekend partying but this event had cheap drinks (thank you drink tickets) and about 300 people.

Below is a picture of the scene at the height of the evening.

200804271640.jpg

While there have been many events that have drawn 400 people, this was different. Almost everyone was doing something startup related that was really cutting edge. There were social media people there (Technosailor and me included), innovative startups and actual investors looking to network.

There were also a great group of sponsors with great products to demo. Here is a great list from Jimmy over at EastCoastBlogging:

AwayFind – a product aimed at helping combat the email problem by letting your contacts get in touch with you via an online form.

iGala – a digital photo frame with a touchscreen interface that connects directly to Flickr and Gmail to stream photos to the frame like a slideshow.

Loladex – offers local recommendations from your trusted network of Facebook friends.

Odeo – launched a new beta verision which offers both search and personalized content (audio and video) recommendations.

Voxant – a free licensed content offering for publishers which offers a pageview based revenue share to anyone that embeds the content on a their site.

WhyGoSolo – a new social networking site aimed at helping you to create spontaneous new connections so, as its name implies, you won’t go solo any longer.

A huge amount of thanks go out to Frank Gruber and Eric Olson who do the TECH Cocktails around the country and they need to do it more than once a year here.

The vibe around this region is changing and since we will never will be Silicon Valley and never want to be, it is fantastic to see that there is a refreshed ecosystem of entrepreneurship here in the region.

Photo courtesy of jgarber

Editor’s Note: Some comments don’t seem to apply to this post as viewers of a show I was on were instructed to leave comments on this blog to get an invite to BrightKite. These comments will be approved but do not necessarily go with this post. Sorry!

SXSW Recap

I’m back on Maryland soil now after changing my flight to come back home Wednesday instead of Thursday. It’s been a heck of a trip and I’m so exhausted. Nonetheless, it was one of the best trips I’ve ever been on. I’ll have to catch up on the sessions I wanted to attend but did not. (Last year, they were all released as podcasts after the event so I’m assuming the same will be done this year).

2318977983_d3c20ccc01.jpg

Amazing people everywhere. That’s the summary, as simplistic as that sounds. The overlapping of all my various circles and networks of people: DC folks interacting with Canadian friends interacting with the PodCamp circle of friends interacting with b5media folks. Not to mention the vast presence of my Twitter friends everywhere I looked. As I said, it was truly amazing.

The past few days, if you’ve been keeping up with this blog, you know that I’ve interviewed six fantastic folks: Brian Solis, “Pistachio” Laura Fitton, Frank Gruber, “Copyblogger” Brian Clark, Christina Warren and Rainer Cvillink. Obviously a very productive day. Those were just the quick video sit downs that I did. We also did our regular weekly District of Corruption live from Austin, appeared on a variety of videos and podcasts by Chris Brogan, Scott Stead, and Kris Smith to name a few.

Though I met many, many new folks this week, I was very pleased to get the opportunity to meet (for the first time), Shel Israel, Erin Kotecki Vest, Micah Baldwin, Grant Robertson, Christina Warren, and Mark Cuban. Yes… I did just say I met Mark Cuban. It was only for a brief handshake as he breezed through the Washington VC sponsored Rock Band party Tuesday night.

2323197929_cab8dd8415.jpg

Old friends reconnected include the inimitable Loren Feldman, Brian Clark, Darren “Problogger” Rowse, Scott Brooks, Alex Hillman and, as usual, many more.

On a light note, I’m a little miffed that the bulk of the coverage of the “Beacon Sucks” heckler moment during Mark Zuckerberg’s keynote wasn’t properly attributed. Christina did, but CNET, Valleywag and the rest of the coverage did not. It was me, of course, which makes me either the voice of the thoughts of all of us or just rude. Not sure which. You be the judge.

IMG_7981-1.jpg

I want to thank the b5media crew that made the event a lot of fun for me. Thanks to Steph Agresta (aka, Internet Geek Girl) for being the face and voice of the Bloghaus. I know you’re wiped out from it, but it was great and I hope for you it was worth it. Lijit and Outbrain for sponsoring the “b5 ranch” – yes it was a real ranch. Grant and Christina for dinner, drinks and so much more with myself and the b5’ers. It’s a pretty cool dynamic to work for a competing blog network and still be some of the coolest people around.

Austin, I’m out. You were wonderful. Until SXSW ’09, stay weird Austin (that’s a tee shirt I saw today).

Virtual Solutions to Shared Network Resources and Email Solutions

I’ve been looking into a number of different resources for b5media. Namely, I’m looking for a better email solution for mail serving as well as an offsite backup and shared resources solution. As we’re a fairly large infrastructure spread globally, we have requirements as well.

Email Solution

Currently, we run all our mail locally through sendmail/dovecot and while this is a workable solution, it’s time consuming to have to go in and edit

1
/etc/mail/virtusertable

and

1
/etc/aliases

all the time. Not to mention trying to get spamassassin to actually deal with spam appropriately. On the surface, I’m a fan of Google Apps for Domains. It allows a nice interface for adding email accounts. Forwarders is a short step from there. And of course, Gmail’s spam filtering is next to none. Add to that the ability to have POP/IMAP/SMTP access via Gmail and it becomes the workaround solution for most of our users.

Then of course, we use Googlegroups for mailing lists.
Continue reading