Using MySQL on a Mac
For many Macintosh users, the relational database is first and last encountered through recent versions of FileMaker Pro. While Windows users have long utilized Microsoft Access, there has never been an equivalent product included in the Mac version of Microsoft Office. Since the move to Mac OS X and its BSD underpinnings, however, several of the favorite databases of the Unix crowd now run with little fuss on your Mac. One of the most popular of these databases is the open source MySQL. It is certainly not the only option if you are looking to try your hand with a full relational database, but it does have a number of friendly interface options and the additional advantage of being packaged with Mac OS X Server.
Recently, new or improved applications have made it possible to access most features of MySQL without having to master a command line interface or the nuances of SQL (Structured Query Language). Power users can still take advantage of the extensive capabilities for storing and retrieving complex data, but one no longer needs to become a full-time database administrator to create a handy index of clients, books, sales figures, or whatever material you need to store. There is even a program to import your iTunes library directly into MySQL (SQLTunes). If you are finding spreadsheets useful but unwieldy, you might want to consider MySQL.
Using most current relational databases requires knowing a bit about how their pieces fit together. The core of the database is software designed to store, index, and retrieve data efficiently, but it normally has no graphical user interface at all. You communicate with the database through a text console by sending it commands in SQL or its own variant of SQL. In effect, the database acts as a server, and its actual location matters little so long as you have an IP connection directly to it and the appropriate ports open in any firewalls along the way.
The front end to such a database is really just the equivalent of a Web browser in that it interprets your requests, sends them in language the database server can understand, and displays the output in a friendly fashion. In theory, a single front end could not only connect to multiple MySQL databases, it could connect to any database that speaks SQL. Unfortunately, there are many eccentricities in how different database products interpret SQL, so you are often limited to certain pairings of database and front end. ODBC (Open Database Connectivity) drivers are supposed to act as the interpreters between SQL and the database’s internal language, but they are sometimes proprietary pieces of code and not available for all situations. MySQL, as an open source product, offers more flexibility than many databases in letting you pick the front end you want to use.
Installing the MySQL Server — But before we get to the fun part of working with the graphical interface, you need to start the database server humming on your Mac. As I mentioned, if you are already running Mac OS X Server, you probably have MySQL installed and simply need to start it using the preference pane (in System Preferences) for that purpose.
Otherwise, if you have Mac OS X 10.3 or 10.4, a version is ready for you at the MySQL organization’s Web site. The current stable release is 5.0, with 5.1 undergoing further testing and development. Note that Apple includes a fairly old version with Mac OS X Server, so you may wish to upgrade to access the latest features. Simply follow the instructions in the Readme file to install the database (most often double-clicking on the package installer is all you need to do) and add the System Preferences pane included on the disk image. The preference pane needs to be placed in /Library/PreferencePanes; again, double-clicking the file should handle this task smoothly. The forums on the site can be helpful if you get stuck. The moment of truth comes after the database is installed and when you open System Preferences. Select the MySQL icon that should appear at the bottom of the window and click the Start MySQL Server button. You will need to supply an administrator password. If you are informed that your database is running, then you have successfully installed and started a full relational database.
Now you need to pick a front end… or several. There is no reason not to experiment with a number of the products I mention to find the one that best meets your needs. A good place to start, however, is with the tools offered by the MySQL organization. You will need, at a minimum, MySQL Administrator installed on your computer. Remember that what we have installed so far is only a server, so we must give instructions to the client you use on how to access it, just like you tell your Web browser what site you want to visit. When you start MySQL Administrator for the first time, it will ask you what database you wish to connect to. You should enter a server hostname of 127.0.0.1 (that IP address is a shortcut to your computer; the address “localhost” will also work), a port of 3306, a username of root, and no password. If all has gone as it should, you will be looking at a window with all sorts of information about your database. The first thing you should do is add a password for the root account (you will need to use it the next time you log into the database) and create an additional account for day-to-day use. Be sure to give this new account most of the privileges available, or at least Select, Insert, Update, Delete, Create, Drop, and Alter. Keep in mind that these privilege restrictions exist in case you want to create an account that, for instance, can only select data from the database but not make any changes.
Working with Tables and Data — Now that you have the database installed and running, and you have a way to administer it, you can begin to work with tables and data. Several software choices are available, all of which attempt to do the same thing: let you work with MySQL while limiting your reliance on SQL. Many people will opt for the MySQL organization’s toolset that includes MySQL Query Browser and MySQL Workbench. These programs overlap somewhat in features – the Table Editor is built into both MySQL Query Browser and MySQL Administrator. Workbench, also included in the download, aids in visually laying out and constructing a complex database. These programs are good, if basic, tools for creating tables and the data fields that they contain.
Unfortunately, these tools don’t always work as would be ideal. For example, creating a field using the CHAR data type to hold text prompts you to fill in the maximum number of characters that should be allowed. Using the similar but more flexible VARCHAR data type, however, does not prompt you for the field length, and if you forget to add it, applying your changes brings up a notice that there is a syntax problem with your SQL, but doesn’t specify the error. Given that one of the reasons to use the graphical interface is to avoid memorizing SQL, you are left with little recourse but to dive into the copious online documentation. That’s not to say the documentation isn’t useful; it is constantly expanded by helpful users through comments added at the bottom of each entry. In fact, I highly recommend you spend a few minutes learning about the different data types that MySQL offers for storing your data. There is always more to learn about using relational databases, but you can get surprisingly far with only a little bit of study.
The MySQL Query Browser won’t do enough for many novices in avoiding SQL when it comes to selecting specific data. Its features are more those of a drag-and-drop syntax helper for creating queries than a fully graphical interface. If you have never encountered a basic SELECT statement before, you might want to look to one of the more friendly free or commercial applications.
When we venture beyond the MySQL application family, we encounter some other open-source projects designed to interface with your database server. CocoaMySQL is a clean, exceedingly Mac-like piece of software built to let users administer MySQL databases. While it does not provide functionality much beyond that of MySQL Administrator, it manages to be more intuitive at practically every step of the way. It won’t help you build queries to retrieve your data, nor lay out a complex database diagram, but for quickly constructing a table with many columns it is an excellent application. Note that you will need the beta version to interface with MySQL 5.0, since the stable edition is now some three years out of date.
The SQuirreL SQL client is built on Java so as to be cross-platform, but it also requires that you have a recent version of Java installed on your computer and forsakes most Mac interface conventions. Because the SQuirreL client runs inside the Java runtime environment, you end up with an awkward double set of menus. Installation instructions suggest you need to venture into Terminal, but simply double-clicking the downloaded file works for most people. In general SQuirreL is an ungainly piece of software and only something the most advanced users will want to explore.
Another free, albeit limited, option comes from the phpMyAdmin Project. If you are already running a Web site using PHP and MySQL, then installing their set of PHP scripts enables you to administer most aspects of the database through a Web browser. It can also retrieve data for you if you already know enough SQL to create the queries you want to use. The situations where phpMyAdmin is handy are very specific and mostly concern webmasters, but using it can make your job easier… or at least keep you from having to go into the office on the weekend to clean up a database problem.
A similar but more extensible software tool is Webmin, based on the Perl language. This free product not only manages your MySQL database, but adds system accounts, configures file sharing, and performs dozens of other common server administration tasks. Webmin aims to be a complete and expandable system for running most common types of servers through a Web interface, but unless such servers are your everyday companions, the package is likely overkill for simply interacting with MySQL.
Moving away from the free tools and into generally more polished commercial products, we find a number of options at various levels of maturity and sophistication. Luckily, as with text editors and Web browsers, the variety of alternatives ensures there is probably a database front end that will suit every user’s needs, likes, and dislikes. Better yet, most of these products offer trial versions for download on their Web sites.
One of the most Mac-friendly applications out there is SQLGrinder from Advenio. At $60, SQLGrinder provides an elegant, universal binary front end with nice features like SQL auto-completion, excellent import and export capabilities, and AppleScript and Automator compatibility. Images are stored and viewed directly through the program and it even remembers query results locally, allowing you to flip back to recent sets of data without having to rerun complex queries. The application uses a single window approach that makes it fairly intuitive to use and speeds navigation through multiple connections to different databases. Unfortunately, it does not offer a graphical SQL query builder – an omission that will leave novices struggling to create basic queries – yet it remains an excellent and well-designed tool for the user already familiar with writing SQL code.
The awkwardly named SQL4X Manager J from InterServices is a universal binary program for $60 that allows one front end to manage most major relational databases, including the big commercial ones such as Oracle and Microsoft’s SQL Server. It is smart enough to adjust to whichever database you are connecting to by downloading recent database drivers, and it shows you only the features that work with that server. The interface is fairly complex (and could benefit from the addition of tooltips) but offers some very advanced features. You can add image files directly into BLOB (binary large object) fields and then view them in a slide-out drawer. There is even a basic reporting module like that found in the venerable Windows program Crystal Reports for creating dynamic documents from your data. Unfortunately for novices, there is no non-SQL query builder included, so with this product you must learn the language to leverage the power of your relational database. For people already comfortable with databases, however, SQL4X Manager J offers some unexpected capabilities in a single package.
Of all the commercial products (and there are more I tested but found either too unstable to mention or lacking the updates necessary to use with MySQL 5.0), two remain on my hard disk after experimenting with their features. The first, and the best one for power users, is Aqua Data Studio from AquaFold. This program requires Java 1.5 to be installed on your Mac, and it feels a little sluggish despite being a universal binary application, but it offers an excellent feature set that behaves similarly to the tools supplied by Microsoft to manage their SQL Server product. The average user will never utilize all of this program’s capabilities, but it includes a powerful graphical query builder, a full query analyzer to ease writing SQL by hand, and complete database administration functionality. The license allows for free personal and educational use; otherwise it costs $150. Aqua Data Studio has its quirks and takes some time to learn, but it is an expansive and exceedingly useful product.
The other standout program is Navicat from PremiumSoft, which delivers a clean and efficient front end for MySQL for $100. A 30-day trial is available for download. The program’s single primary pane and detailed editing windows let you create tables, modify their fields, add data to them, and retrieve result sets through complex queries, all without knowing a bit of SQL. If you switch between operating systems, Navicat is available for Windows and Linux in addition to Mac OS X. Navicat’s interface is very Mac-like and friendly, with clear, labeled icons and a sensible layout. The graphical query builder provides an introduction to SQL by enabling you to create queries through arranging and connecting representations of tables, while showing the code your actions produce in another tab. The query builder does not let you set selection, grouping, and sorting criteria in a single window the way Aqua Data Studio or Microsoft Access does, but its approach using multiple tabs to build the parts of a query works reasonably well. Navicat is currently my choice of the commercial options for the SQL beginner because of its ease of use, intuitive design, and graphical query builder.
If you end up spending a lot of time working with MySQL or value the time savings a well-designed interface provides, you may want to consider either of these two commercial products, which come with the added benefit of technical support.
Regardless of the front end selected, Mac users at last have access to the power and efficiency of a full relational database. When spreadsheets no longer meet your needs, it might be time to give MySQL a try.
[Jonathan D. Sousa, an online applications architect, owns Sousa Consulting, LLC in Washington, DC. When not at his computer, he can be found running long distances or practicing yoga.]
PayBITS: If this article helped you to get the most out of MySQL
on your Mac, consider thanking Jonathan via a PayBITS donation!
https://www.paypal.com/ xclick/ [email protected]
Read more about PayBITS: http://www.tidbits.com/ paybits/