How to Install and Configure MySQL in Mountain Lion

In my previous two articles, we looked at how to get Apache Web Sharing and PHP up and running in OS X 10.8 Mountain Lion via some quick trips to the Terminal. While Apache and PHP are a powerful duo indeed, with their ability to serve up web pages and dynamically script the creation of web pages respectively, web developers everywhere will tell you the package is not complete without MySQL.

MySQL is a free and open source relational database. Perhaps due to it being free, or maybe because of its easy integration with PHP, MySQL took off in popularity near the end of the 20th century. The completely free combination of Linux, Apache, MySQL, and PHP - commonly referred to as the LAMP stack - allowed anyone with a little desire ( and a little bit of time to learn) to create dynamic web pages with ease. Dynamic web pages differ from regular "static" pages in that their content can be updated based on different conditions, with PHP manipulating the page content - often with data fetched from a MySQL database.

Unlike Apache and PHP, MySQL is not pre-installed on OS X. The good news is that it's easily installed without any fiddling with the Terminal. The Terminal-based steps in this article are mostly optional and just make it a little easier to integrate with PHP and interact directly with the database server.

To get the MySQL installer, head over to the MySQL downloads page. Once there, it looks as if you need to register to download the installer, but you don't. Just look for the link that says, "No thanks, just take me to the downloads!" You'll then see several downloads for OS X, some for older versions of the operating system, and some that are source-code versions that you can compile yourself. For Mountain Lion, what you want to grab is the installer package labeled Mac OS X ver. 10.6 (x86, 64-bit), DMG Archive.

Although this may give you the impression that this version is only for Snow Leopard, it's actually for 10.6 and higher, and it works just fine with Mountain Lion. Download the DMG disk image which, when mounted, will present you with three items: the MySQL installer package, a MySQL Preference Pane for System Preferences, and an installer for a Startup Item. Install each by double-clicking them and running through the install procedure.

Once everything is installed, you can start MySQL by going to the MySQL pane in System Preferences. MySQL is now indeed up and running, and you can access it many different ways: through its command-line client (which is just called mysql) or through one of several GUI clients. A good free one is Sequel Pro. This app, formerly known as CocoaMySQL, affords you complete control over your newly installed database server - and also for inserting, updating, and deleting data. Using its Console feature, you can even view the commands that would be used in the command-line mysql program, which is a great way to learn SQL - the Structured Query Language used by MySQL and many other SQL variants.

Since the focus of this series of articles has been about getting Apache, PHP, and MySQL up and running on Mountain Lion, we'll need to do one step in the Terminal to link PHP to MySQL. This step fixes a small problem with the default PHP configuration on OS X, wherein PHP looks for MySQL's socket file (a file that allows local connections to the MySQL server) in one place when it actually exists in another. One way to solve the problem would be to edit the PHP config file (php.ini), but many programs actually look for MySQL's socket to be in the alternate location, so I recommend running this command in the Terminal to set up a symlink (a Unix function similar to Mac OS aliases or Windows shortcuts, but definitely not the same thing) between the location of the socket file, so that PHP and other programs will find the socket file in the place they expect:

sudo mkdir /var/mysql; sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

If you find yourself wanting to try out the mysql command line program, it can be accessed with this command:

/usr/local/mysql/bin/mysql -u root

You may notice the long path leading up to the mysql part, and that's because the path to mysql is not in the list of default paths that the Terminal's bash shell uses by default. To add this directory to that list of paths, open up (or create, if need be) the file .bash_profile in your home folder. Add this line to the top of the file:

export PATH="/usr/local/mysql/bin:$PATH"

You'll need to close your existing Terminal and open a new one to load the change, and after doing so, you can now access the program by just typing:

mysql -u root

It would probably be a good idea to give the MySQL "root" user a password; this can be done via the command-line program or within Sequel Pro. I will leave that up to the reader to Google around for how to do that, in the interest of brevity; for a local, personal installation of MySQL with your OS X firewall enabled, not setting a password is not a major security breach.

In the next column, we'll look at MAMP, a fantastic app that bundles its own Apache, MySQL, and PHP into one easy-to-use development environment.