Managing MySQL on Mac OS Xby Robert Daeley
MySQL is a popular database management system with which most web developers are familiar. You can't swing a cat on the internet without hitting a website running it on the back end. In fact there's an acronym stemming from the ubiquitous open source combo of Linux, Apache, MySQL, and PHP (or Perl or Python): LAMP, which you can find out all about over at O'Reilly's ONLamp.com. While there's quite a bit of lively debate about its relative merits compared to other systems, there's no denying MySQL is widespread.
Mac OS X, in its current 10.4 Tiger incarnation, is well suited as a MySQL-supporting platform, especially since it includes Apache and the P-languages out of the box. (MAMP has yet to catch on as an acronym, but maybe someday.) Earlier versions of the OS supported MySQL well enough, but only recently have MySQL AB's installer and documentation support begun to shine.
There are myriad ways of controlling and manipulating information on a MySQL server -- some are stand-alone GUI apps, some are web-based, and of course the venerable (and powerful) command-line option is always available. I'm presuming you're using Mac OS X 10.4 -- the newer the better. (10.4.3 is the current incarnation as of this writing.)
Some of the listed applications have downloads still available that will work with older OS versions -- obviously the later your OS X, the more likely things will work properly and as advertised.
Full instructions on obtaining and installing MySQL are beyond the scope of this article, but this is actually a relatively easy process nowadays, thanks to MySQL AB's new binary installer package. Marc Liyanage's MySQL page has a good introduction to the process.
No discussion of this subject is complete without acknowledging Marc's contributions. He provided downloads and instructions for MySQL (among other packages) beginnning in the early days of Mac OS X and enabled thousands of people to access powerful tools that they otherwise would have been unable to utilize. Thanks, Marc!
If you check out his old MySQL site now, you'll find this note:
Since version 4.0.11, I am no longer building (from source code) and hosting the MySQL binary packages because the people at MySQL started to provide very nice official binary packages.
Since Apple seems to be concentrating on the 4.1 branch of MySQL in their Mac OS X Server line (where it comes pre-installed), it's probably best to stick with that for now, assuming you're installing on Mac OS X Client. You can download the Standard version in a .dmg disk image. This includes not just a database server .pkg installer package, but also a MySQLStartupItem.pkg -- enabling the server to start up when the computer does -- and a MySQL.prefPane -- which makes starting and stopping your MySQL server as easy as clicking a button in System Preferences. Not too shabby!
Unfortunately, proper configuration of the server is not as simple as installation -- it will require some investment on your part in learning the subject if you are going to be administering your particular server. Lucky for you, MySQL AB has got you covered.
As Marc said above, MySQL AB's Mac OS X support has improved dramatically this past year or so, as evidenced by the spiffy MySQL Administrator application. Once you've got it installed with a simple drag-and-drop, you launch to a login window, type in your host, username, and password, and suddenly you have a spiffy GUI control panel for your MySQL server. And unlike other, similar programs, this one does not skimp on what parts of the server you have access to -- assuming you have the proper privileges for that host.
There are ten "panes" in the main window, similar to a preferences panel. The Information pane gives you just the basic version info on your current connection, for server and client, but things get deeper quickly as you move to the right. Service shows you the server's status and startup log messages. The modestly named Options pane is actually nine levels deep, accessed via a pop-up menu, and gives you almost complete control over every aspect of your server.
Moving farther right, you'll find the very important Accounts, wherein you'll grant and revoke privileges for individual users at various hosts. Connections, Health, and Logs are handy administrative tools for monitoring your server's performance. Backup and Restore do just what they say, working in concert.
Finally, there's Catalog, where you'll likely spend most of your time, creating your database and table schema. A lot of the power is hidden here -- adding a new database and then a table reveals a several-layers-deep control window. Control freaks will have a lot of fun in here, futzing with columns, indices, table options, row options, storage options, merge table options, etc. Needless to say, you should know what you're doing before messing around too much, at least on a live server. And while you're in the main Catalog window, have fun creating Views, Stored Procedures, and Functions.
However, one thing that MySQL Administrator is not as suited for: adding and editing data. Given the program's name, this should not necessarily be a surprise. None of the other solutions mentioned in this article does anywhere near as good a job at simplifying the complicated task of MySQL server administration (unless, of course, you're well versed in the CLI version). It isn't really for the casual user, though, so you'll want to look at the others if that describes you.
I've run into a couple of issues with the application. One is that it doesn't seem to like remembering passwords, despite your being able to add them in the Connection Editor (in the app's Preferences). I'm not sure if this is on purpose or not -- I would assume not, since the field is there in Saved Connections -- but it is rather annoying. The other issue is that some of the control menus are laid out a bit confusingly, at least to begin with. Once you've gotten used to some of the peculiarity, you'll be fine.
MySQL Administrator is available in two versions, 1.0 and 1.1, and you'll want to run the latter unless it can't be used because of your server version. Download 1.1 from this page. It is released in MySQL's usual dual licensing, which means:
Under this model, users may choose to use MySQL products under the free software/opensource GNU General Public License (commonly known as the "GPL") or under a commercial license.
The same is true of the database server itself. Complete MySQL Administrator documentation is also online. Finally, the app is available for multiple platforms, so if you've used it on one, you'll be up to speed already.