Install MySQL 5.1 for Development and Enable remote connections

Overview

I keep getting tripped up by this each time I install MySQL so I thought I would post this as a reminder to myself with the hopes it may help someone else out too.   I have probably setup and installed MySQL on Ubuntu at least 5 or 6 times in the past 2 years.  Each time I think wow how easy and each time I get stuck when it comes to remotely accessing the MySQL instance.  In my production environment this hasn't hit me because I have MySQL on the same VM as my application and only allow local connections.  However, in my development environment I don't want that restriction. So naturally, when faced with an information problem,  I use a search engine and try looking for this information. Inevitably I either find the posts that show how to change the configuration of the service OR I find the posts showing how to adjust the user privileges but I never seem to find a combination post.  Here's my combo post.

Installing MySQL on Ubuntu

sudo apt-get update

sudo apt-get upgrade --show-upgraded

sudo apt-get install mysql-server

Other things you might want to consider I have found useful in this Linode.com post on setting up MySQL on a VM from their service. (which I have and think it's awesome)

Enabling some security

sudo mysql_secure_installation 

When running this utility you will be given an option to allow root "remote" access which I would think you should ONLY do in a secure environment if you know what your doing.  Have you heard that before?  You probably also heard the speech about setting a good root password, so I'll spare that one for you and just say I would if I were you.

Allow remote connections

Now to make sure that your user connections can actually connect you need to actually grant the users privileges.  That looks something like this in my case.

grant all privileges on *.* to 'devuser'@'192.168.1.%' identified by "CHANGEME";

If you don't understand what this does you should probably due some reading.The % character can be used in any portion of the address or even as the user name.  Refer to the MySQL 5.1 Access Control - Connection Verification section of the docs for MySQL.  (If you don't understand why I used 192.168.1.% then I can't help you.)


This should get you up and running and connecting remotely.


References
Linode Reference Library
MySQL 5.1 Access Control - Connection Verification
A useful post with some firewall notes too