MacDevCenter    
 Published on MacDevCenter (http://www.macdevcenter.com/)
 See this if you're having trouble printing code examples


Managing MySQL on Mac OS X

by Robert Daeley
12/13/2005

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.

Getting MySQL

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.

MySQL Administrator

Related Reading

MySQL in a Nutshell
By Russell Dyer

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.

Clientele

Beyond administration is day-to-day data manipulation. Here are some good options for interacting with your data.

YourSQL

Where MySQL Administrator is an omnipotent admin machine, YourSQL (by Ludit) concentrates on the everyday needs of folks making simple database edits. The simplicity is a bit deceptive, as it can enable power users to get their work done, too.

The Download YourSQL page presents you with a pop-up menu of versions to choose from, which currently lists "1.8.0e development" as the most recent. Unless there's some compelling reason for you to choose the bleeding edge, I would go with "1.7.2", which is the last stable version and which works in Mac OS X 10.3 and newer. Once again, a drag-and-drop is all that stands between download and installation.

YourSQL plays on the familiar Column View in OS X with a Host -> Database -> Table arrangement at the top of the main window. In the bottom half are fields for table design, browsing, and data entry. The overall arrangement is pretty handy, especially if you deal with multiple servers -- visually navigating the hierarchy is a snap.

A toolbar at the very top of the window provides some expected functionality like creating databases, tables, and columns. Deleting things is handled by default either through the menu bar or, if you're in the know, by contextual menus (control- or right-clicks).

One area that could use some improvement is schema design, and you really need to know your varchar from your text before trying to design a table. Assuming you know what you're doing, however, the interface is straightforward. With the working table selected above, add columns below, providing a Type and other definition as necessary. One tip is that you can change Column Types via a submenu under the Schema menu, or via a right-click on the column.

Once you've designed your table's columns, switch to Show Data to begin adding your rows. This part is pretty easily accomplished if you've ever used a spreadsheet, as it's the same idea. A couple of tips: in the very bottom of this area, to the right of the Search field, are two large buttons, one with a photo icon, the other with a text icon. These can be used to enter BLOB images and text blocks a lot more easily than in the small fields above. Also, back in that Search field, the small triangle on the left allows you to choose a particular column to search by.

And if you're truly 1337, head to the next tab, Free-Form SQL, where you can enter raw SQL statements to manipulate your data. There are also Load and Dump commands in the File menu which make it easy to back up data or move tables from one database to another.

YourSQL is best suited for users who are looking for a lightweight MySQL data solution, especially if you have multiple databases on multiple servers. I've used YourSQL to access tables of thousands of rows without making it unusable, although the more data the slower it performs. I wouldn't recommend it for those with slow connections, older computers, overloaded servers, or huge datasets. You may also find some small glitches in UI elements. YourSQL is a GPL open-source project, though, so feel free to head into the source files and play if you're willing and able.

phpMyAdmin

If you're not familiar with phpMyAdmin, add the initials SQL between the My and the Admin, and now you know what it's all about -- PHP-based MySQL administration. It is normally accessed via a web browser interface, and in fact you'll need to have a web server available to run it.

Since you'll be controlling your database on the web, you will need to have a secure setup protecting the phpMyAdmin directory itself. Most of the time, you can use simple Apache htpasswd authentication, but check with your server administrators or ISP for how they want it done. In the case of pre-installations (say, attached to a hosting service), this may be in place already.

Of course, using Mac OS X, you have a web server at your fingertips, simply by turning on Web Sharing under System Preferences. This brings up an important point often missed by users: phpMyAdmin does not need to be installed on the same server as your database. In fact, you could throw it in your local Sites folder for easy access, as long as you configure it with the correct remote login information. There are security considerations with remote administration, however, so ask your admin if you are unsure; they may very well rather you access phpMyAdmin on the same server as your database.

What's important is what your MySQL user account's privileges are, and what host it is allowed to connect from. No matter where you install phpMyAdmin, you'll need to have a MySQL user account on the host you want to edit. This is a different username/password set than the web server login, a distinction sometimes confused by beginners.

Downloading phpMyAdmin will give you a compressed file that expands into a directory with a name matching its version number: e.g., phpMyAdmin-2.7.0, which is the latest as of this writing. You could leave the name alone and just drop it in your web directory, but you also might like to change its name to something easier to type.

Assuming you are setting it up yourself, the two files you'll definitely need to concern yourself with are at the top level of the phpMyAdmin directory: config.inc.php and Documentation.html. While I can't duplicate that information in this article, there are a handful of critical variables that need setting in the config file, which I'll touch on briefly. Read the documentation for full details.

The first one is not too far from the top of the config.inc.php file: PmaAbsoluteUri, which is just the full web address for your phpMyAdmin directory. Assuming you've renamed it to phpmyadmin and put it in your Sites folder, that line will look like this:

$cfg['PmaAbsoluteUri'] = 'http://localhost/~username/phpmyadmin/';

Farther down in the file, under the "Server(s) configuration" section:

$cfg['Servers'][$i]['host']          = 'localhost';

Substitute your MySQL server hostname where it says localhost above. Then just below that:

$cfg['Servers'][$i]['user']         = 'username';
$cfg['Servers'][$i]['password']     = 'password';

These are the username and password for your user account on the MySQL database server. Once more, these are different than your web server login information.

Once you have phpMyAdmin configured and you've confirmed you can log in, you'll want to have a scan through the online documentation to get a sense of what's available -- quite helpful if you're already familiar with MySQL and just want to see what you can and can't do. On that note, while you can't (arguably) administer things as easily as with MySQL Administrator, there are a few basic options that you might want to familiarize yourself with, so that you can do things in more than one place. I highly recommend the user privileges section for starters.

The Command Line

Loved by some, feared by others, the all-powerful command line offers some of the best options for controlling MySQL . . . if you know what you're doing. Also, if you're in a situation where your only access to your server is via SSH and a terminal, using the command line might just save the day.

Now, this might just be me, but I've actually found a lot of value in using this option as a training tool for new users. By showing them the literal grammar of what's going on -- SELECT name FROM USERS WHERE hat = 'Stetson' -- gives them both a sense of what's really being done, and a foundational vocabulary when they turn around and use other programs, whether GUI or web-based.

You can get started with the command line by checking out the online documentation. I'd even recommend downloading the docs locally so you have them at the ready, for easy reference while you're learning and working.

A Note on CocoaMySQL

Another entry in the Cocoa-based GUI apps was CocoaMySQL, which I used quite a bit before switching to MySQL 4.1. Certain changes in the database security methods that came with 4.1 (explained in this FAQ) have left the application unable to connect to newer servers, although at least one forked version is reportedly available that takes care of the problem.

All of the Above

Which one should you choose? Well, it naturally depends on what kind of user you are, as well as how experienced you are -- but also on what kind of access you have to the database server in question.

All things being equal, I would obtain and learn all of the above. Each has its strengths and weaknesses, yet if you can absorb even a little of all of them, you'll both broaden your MySQL knowledge and add utilities to your toolbelt.

Robert Daeley is a writer and programmer in Southern California. By day he is a mild-mannered server administrator and website developer; by night, in addition to his super-hero duties, he cooks, bikes, hikes, cheers on the Dodgers, and writes fiction.


Return to the Mac DevCenter

Copyright © 2009 O'Reilly Media, Inc.