Fork me on GitHub

couples counseling for PHPStorm and MySQL

I have drunk the koolaid, and it is purple and orange. Swirled. Without mixing. Because magic.

I thought JetBrains just made Eclipse knockoffs until I started watching the short AngularJS tutorials at Resident guru John Lindquist wields WebStorm like a syntax-highlighted scalpel, flying through line after line of auto-generated code as easily as most of us breathe. I got excited just watching. The AngularJS tutorial content is great too, but that editor. I'm fast in vim, but not that fast.

On the other hand, WebStorm doesn't really touch my mostly-PHP day job. Enter Mathias Verraes and his refactoring tutorial in PHPStorm. Same blazing speed and mobility as WebStorm, but now with PHPUnit integration and coverage reports! Insanity. I caved. Down the rabbit hole I went.

Feet get wet

My base OS is Windows, so I do a lot of work in local Linux virtual machines. Fortunately, PHPStorm has lots of options for talking to remote servers. I mounted the VM project directory on my host with win-sshfs and chose PHPStorm's Remote server, mounted drive option when I created the project. Pretty progress bar, couple spinny cursors, and I was in. The very first tip-of-the-day (don't turn them off!) told me to check out the tool shortcuts in the bottom left corner:

tool menu

Project, TODO, Favorites, blah blah, Database--wait, what?

Choose that Database option, and you'll get a full data source manager, supporting about a dozen engines. I plugged in the settings for my VM's MySQL database, hit Connect, didn't work!

Why is it ignoring my database?!

This particular VM was running Debian Wheezy. MySQL configuration defaults may be different for other distributions, but here's what I had to do to get PHPStorm and MySQL talking:

Don't use bind_address

By default, MySQL binds to (i.e. only accepts connections from) You want to connect from your VM host, so comment out the bind_address line in your MySQL configuration (/etc/mysql/my.cnf):

commented bind address

(Note that you should not do this on any machine that's accessible from outside your local network. It's bad. It's so bad.But we're working with a local VM right now, so we don't care!)

Create a user with privileges to connect from anywhere.

Again by default, MySQL's root user only has local connection privileges--it can't connect from outside the VM. If you don't have one already, you'll need a MySQL user that can connect from your VM host:

create user 'gemma'@'%' identified by 'yourpassword';
grant all privileges on *.* to 'gemma'@'%' with grant option;

Restart MySQL

Restart the MySQL server to apply the configuration changes:

sudo service mysql restart

Bringing back the romance

Now that MySQL is behaving itself, we can work on PHPStorm. Pull up that Database tool window from before and create a new MySQL data source: Green plus sign (or Alt + Insert) → Data SourceMySQL

create a mysql data source

In the window that appears, plug in your connection settings.

mysql connection config

  1. If you haven't already, click the link to download the driver files. It's painless; PHPStorm does it all in the background.
  2. Host and Port should match the connection information for your VM. I have a hostname set up (myvm.local), but you could just as easily use a raw IP address.
  3. Database should be the name of the MySQL database you'll be using for this project.
  4. The user credentials should match the new user you created above.

Wedded bliss

That's it! That empty Database manager should now be populated with all the content from the database you specified. You can view tables, run queries, create diagrams--really everything I normally do in an external client. Pretty slick.

(If you want to keep going, check out the PHPStorm Data Source documentation!)