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


Apache Web Serving With Mac OS X

Apache Web-Serving With Mac OS X, Part 5

by Kevin Hemenway
03/08/2002

Editor's Note: Kevin returns with his fifth article concerning Apache, OS X, and the immense power available to the common man. Having established a good, firm pool of Apache knowledge in the first four articles, Kevin takes the time to focus on a popular supplementary technology, namely the database server MySQL.

Even after getting that prime parking spot, and even after getting one of those deficit-inducing, dotcom-deflating chairs, you stare down at the latest GatesMcFarlaneCo company email and realize that you've done a little too much enchanting. That jocular, mustached, stomach-clutching behemoth you call your boss is positively smitten with Mac OS X, and his eyes are round with the possibilities.

"Intrepid reader!" he writes, "Our lemming leaps into your waiting arms!" He's quoting from the GatesMcFarlaneCo Employee Handbook again, but he gets to the point rather quickly: "Listen, lots of people have been asking me about databases and this ess-queue-ell stuff. They figure, with OS X and your skill, it should be easy to set up, right? I told them we'd demonstrate something for 'em this afternoon. See you at 3!"

You glance down at your watch. It's 1:30. Your stomach grumbles. Back to your watch. Eyes on the monitor. "See you at 3!" The grumbles are louder. Watch. Monitor. Stomach. Keanu Reeves whispers, "You've got 3 seconds; what do you do? WHAT DO YOU DO?!"

Time for lunch!

Before we begin

In Part 5 of this series, we're going to look at installing MySQL as well as creating a simple PHP script that creates a database, adds some information, and then returns the data to the browser. Be aware that this article is not a tutorial for SQL or the intricacies of running MySQL--we focus on the installation on OS X only. If you need a brush up on SQL, check out O'Reilly's excellent aboutSQL series.

Inevitably, someone is going to ask, "Hey! Why'd you choose MySQL over PostgreSQL (another popular database server)?" This isn't a Quidditch match, folks; use whichever one suits you better. MySQL is more popular and easier to use, but PostgreSQL has more of the features that die-hard database users will say "make the man." If you're new to databases and SQL, stick with MySQL for a while. Once you're making the big bucks, go with PostgreSQL. Here we focus on MySQL, but the concepts apply equally to both.

Before we get to the article, we have one final concept to discuss:

Source code or double-clickable?

Related Reading

Apache: The Definitive Guide
Vital Information for Apache Programmers and Administrators
By Ben Laurie, Peter Laurie

We Mac users, I tell ya', we've had it easy for quite a while. Heck, even those Windows-type people have had a smooth ride through most of their years. See, when we want to install software, we double-click. We follow the prompts, and perhaps a reboot later, we've got our new software and we're ready to go.

There are breeds of computer users, however, that fall into a different category entirely. They delight in mucking about with makefiles, configure scripts, source code, and more. They like choosing esoteric options for which the only result is scant optimization that may or may not truly exist. They like watching screenfuls of information flash by, faster than they can read, and they revel in re-examining what they missed.

This is the world of "compiling source."

Rest assured, I'm not going to force you to do anything you don't want to do. Some of you have heard of this compiling junk, and it turns your stomach. You're thinking, "Wait, the Mac was easy! They added this old-fangled type-in-the-window thing, and now they want me to quack like a programmer?! Bollocks on you. I'm tellin' Mom!"

There are advantages to compiling from source: tailoring the installation to your work environment; turning on or off options that may otherwise be unused; and modifying code for those "special moments" that the original developer hadn't prepared for.

Most Linux-like systems, Mac OS X included, cater to both the user who wants to double-click and the user who wants to compile from source. OS X allows you to double-click a .pkg file; Red Hat grants you an .rpm file; and Debian has .deb files. All distributions allow you to compile from source, provided you have the right tools available. For OS X, this means you have the latest Developer Tools installed.

Below, I'm going to show you how to install MySQL. I'll show you where to get the double-clickable version, as well as how to build, from source, an exact copy. It's actually pretty easy and won't take you more than a few minutes (which is good, since your lunch break was rather loooonng). Let's get started.

Installing MySQL

MySQL is one of the most popular database servers, available for all flavors of Linux, Windows, and now Mac OS X. Due to its immense popularity, you'll often see Web hosts and ISPs offering it as one of the default features for new accounts. It's a good database to get your feet wet with.

One of the drawbacks to its popularity, however, is the number of ways you can install it. There are various double-clickable packages available, as well as various ways to install and compile from source. We'll be focusing on the two I found most arousing:

Before we go much further, we have to create a MySQL user. This is the account that our MySQL server will run itself as. (Whereas you may be the morbus user, and the administrative account would be the root user, our MySQL server will run as the mysql user.)

Creating this account is rather simple:

  1. Click on your Apple menu.
  2. Choose System Preferences.
  3. Click the Users preference panel.
  4. Click New User.
  5. For Name, enter "MySQL User".
  6. For Short Name, enter "mysql".

The Login Picture and Password can be anything you wish. Once you've got all the information filled in, click the OK button, and you'll see the "MySQL User" entry in the User list. This step is required for both the double-clickable install and compiling the source code--don't skip it.

Installing the MySQL package

Comment on this articleAh, so many things to discuss. . . . One of the topics we're interested in is what you would like to see next in this series.
Post your comments

We're ready to move on with our double-clickable install, so download the MySQL 3.23.49 package from Marc's site. Once the download is complete, the install is much like any other OS X package; you'll need an administrative password, and a few button clicks later, the installer will be finished.

That's the extent of installing MySQL in package form--nothing fancy, really. There are a few more steps to configuring a properly working MySQL database, but since they're needed for both the packaged and compiled versions, we'll get to them after the next section. You can skim on down to "Post-installation wrap up."

Compiling MySQL from source

Compiling MySQL from source is relatively easy. In the next few steps, we'll create and install the MySQL database with the same configuration as the one available in the prepackaged form above. To compile MySQL, you'll need access to your Terminal and an administrative password. Also, the latest OS X Developer Tools should be installed.

Note: If you've installed the packaged version of MySQL (above), you do not need to do anything in this section.

The first thing, obviously, is to get the source code itself. You can find the closest mirror on the MySQL site. Once the download is complete, get into your Terminal and create and move into the /usr/local/src/ directory:


    cd /usr/local/
    mkdir src
    cd src

When you're installing something new on a Linux-like system (such as Mac OS X), you really should keep most of your efforts and work environment centered around the /usr/local/ directory. This helps give a distinctive separation between software installed by the operating system and software installed by you. When you install the MySQL package (above), for example, it installs everything into /usr/local/mysql-3.23.49/, including an alias (called a "symlink" in Linux terms) from /usr/local/mysql/.

Next, we decompress our downloaded file (your path and filename will be different):


    tar -zxvf /Users/morbus/Desktop/mysql.gz

We then move into the new directory:


    cd mysql-3.23.49/

At this point, you're going to use three commands that are very common when compiling source code. The first command is configure and, funnily enough, it creates a configuration file that is used by the other two commands. In most cases, this is where you choose how you want your program to act, where you want it installed, etc.

The configure line for MySQL is simply:


    ./configure --mandir=/usr/local/share/man --with-innodb

This line gives us an example of two things we can do with a configure statement. The first option, --mandir=/usr/local/share/man, shows an example of how you can override a built-in default. In this case, we're saying, "No, MySQL, don't install the documentation in the normal directory; install it over in this one instead."

The second option, --with-innodb, is an example of turning a feature on--one that normally is not. In this case, InnoDB is a way of adding foreign keys, transactions, and more to MySQL. (These are some of the things that purportedly make a "man's database," as mentioned in our introduction.) It's outside the scope of this article to get into what all this really means; if you're interested, you can check out InnoDB.com for more information.

After you run the above command line, you'll see a decent amount of output, most of which probably won't make sense. That's OK, though; configure scripts often check your "build environment" to make sure they know everything they need to do before you actually compile the source code. In essence, they're taking all the guesswork out of the eventual compilation. As long as there are no glaring errors (there shouldn't be), you can move on.

The next step is the actual compilation phase. This is where you take the source code you've configure'd, and turn it into an executable program for OS X. To do this, simply enter the following:


    make

make will take a look at the configuration you created (using that configure command) and go about creating a custom installation based on your whims and desires. Often, this can take minutes; it can also take seconds. (On my Dual 450 G4, it took a good eight or nine minutes, with three or four other programs open.) Either way, you're going to see a lot of stuff saunter by on your screen. You don't have to worry about reading or understanding it all . . . this is the art of a compile-in-progress.

Be careful that you don't get confused by the concepts of "compiling" and "installing." Just because we're compiling our source code with make, there's no guarantee that we can use it to conquer the world. Our last command in our trio-of-temerity handles that aspect:


    sudo make install

This command simply takes all of the compiled code from our make and installs it in the places we've requested (said places being overridable using the configure command, if you recall). After you run make install, the code you've compiled is ready for your use. You can either begin using the program right away, or you can continue tweaking extra settings.

In MySQL's case, there are a few more commands we need to run--basic steps that ensure a properly running MySQL. Read on, stalwart traveler!

Post-installation wrap up

Depending on how you installed MySQL (either as a package or by compiling the source code), certain files will be in different places under the /usr/local/ directory. This is normal and is covered in the install documentation, which I've excerpted below.

In the case of the package installation, your directory layout is shown below, with /usr/local/mysql/ being a symlink to /usr/local/mysql-3.23.49/. (Note, however, that the client programs and server are also installed in /usr/local/bin/.)


    *Directory*                             *Contents of directory*
    `/usr/local/mysql-3.23.49/bin'           Client programs and the server
    `/usr/local/mysql-3.23.49/data'          Log files, databases
    `/usr/local/mysql-3.23.49/include'       Include (header) files
    `/usr/local/mysql-3.23.49/lib'           Libraries
    `/usr/local/mysql-3.23.49/scripts'       `mysql_install_db'
    `/usr/local/mysql-3.23.49/share/mysql'   Error message files
    `/usr/local/mysql-3.23.49/sql-bench'     Benchmarks

If you compiled from source, your directory structure becomes:


    *Directory*                              *Contents of directory*
    `/usr/local/bin'                         Client programs and scripts
    `/usr/local/include/mysql'               Include (header) files
    `/usr/local/info'                        Documentation in Info format
    `/usr/local/lib/mysql'                   Libraries
    `/usr/local/libexec'                     The `mysqld' server
    `/usr/local/share/mysql'                 Error message files
    `/usr/local/sql-bench'                   Benchmarks and `crash-me' test
    `/usr/local/var'                         Databases and log files

With the above hierarchy, your final steps are within walking distance:


    # for package installations
    cd /usr/local/mysql/
    sudo ./scripts/mysql_install_db
    sudo chown -R mysql /usr/local/mysql
    sudo ./bin/safe_mysqld --user=mysql &

    # for source installations
    cd /usr/local/
    sudo ./bin/mysql_install_db
    sudo chown -R mysql /usr/local/var
    sudo ./bin/safe_mysqld --user=mysql &

The above orders will initialize the core MySQL database (which takes care of access control), as well as start the MySQL server in the background. If everything goes smoothly, you should see something similar to this (the pathname is based on which install you chose earlier):


   Starting mysqld daemon with databases from /usr/local/var

Hello, MySQL!

If the above went smoothly, it's time to make a quick PHP script to make sure database communication is possible. Copy the following code into your favorite text editor (like BBEdit), and save the file as test.php within a Web site directory (either /Library/WebServer/Documents/ or /Users/morbus/Sites, for example).


<?
   print "<pre>";

   // log into our local server using the MySQL root user.
   $dbh = mysql_connect( "localhost", "root", "" );


   // select the 'test' database created during installation.
   mysql_select_db( "test" ) or die ( mysql_error() . "\n" );
   print "Connection to the database has been established.\n";


   // create a simplistic table.
   $table = "CREATE table wisdom (
             id int(4) PRIMARY KEY AUTO_INCREMENT,
             wisdom char(255), author char(125) );";

   $response = mysql_query( $table, $dbh );
   if ($response) { print "The table was created correctly!\n"; }
   else { print mysql_error () . "\n"; }


   // now, we'll add some data to our newly created table.
   // to add different wisdom, just change the 'values'.
   $insert_data = "INSERT into wisdom ( wisdom, author ) 
                   values ( 'Must... remain... awake!', 'Morbus' );";

   $response = mysql_query( $insert_data, $dbh );
   if ($response) { print "The data was inserted correctly!\n"; }
   else { print mysql_error () . "\n"; }


   // and read it back for printing purposes.
   $get_table_data = "SELECT * FROM wisdom;";
   $response = mysql_query( $get_table_data, $dbh );
   if ($response) { print "We successfully got all the table data.\n"; }
   else { print mysql_error () . "\n"; }


   // now print it out for the user.
   while ( $one_line_of_data = mysql_fetch_array( $response ) ) {
       extract ( $one_line_of_data );
       print "#$id: $author sez: \"$wisdom\"\n"; 
   }

   print "</pre>";
?>

Note: Again, we're not going to explore the syntax of the PHP script, or the SQL commands that are used. Suffice it to say that this script will create a table in the MySQL 'test' database, add some data, and then spit back the total contents of the 'wisdom' table. If you need a brush-up on PHP or MySQL, be sure to check out ONLamp.com.

After you've saved the file, load it in your Web browser. I saved my copy in /Users/morbus/Sites/test.php, so I loaded http://127.0.0.1/~morbus/test.php in my browser. After the first run, this is what I saw:


    Connection to the database has been established.
    The table was created correctly!
    The data was inserted correctly!
    We successfully got all the table data.
    #1: Morbus sez: "Must... remain... awake!"

If I continue running the script, changing the INSERT line each time, my output will start to look like:


    Connection to the database has been established.
    Table 'wisdom' already exists
    The data was inserted correctly!
    We successfully got all the table data.
    #1: Morbus sez: "Must... remain... awake!"
    #2: Morbus sez: "Sleeping makes Derrick angry!"
    #3: Morbus sez: "And I'm 23 minutes away from 3 o'clock!"

The above output certifies that our PHP-to-MySQL communication is working perfectly. With 23 minutes to spare before the boss and his goons come, we've got just enough time to chow down the last remnants of our ketchup potato chips and wash them down with a swig of Moxie.

Two minor additions

Previously in the Series

Apache Web-Serving with Mac OS X, Part 6

Apache Web-Serving with Mac OS X, Part 4

Apache Web-Serving with Mac OS X: Part 3

Apache Web-Serving with Mac OS X: Part 2

Apache Web-Serving with Mac OS X: Part 1

When we turn on our Web server (through the Sharing preference panel), OS X will happily restart Apache if our machine ever needs a reboot. Out of the box, MySQL doesn't restart automatically. Thankfully, there's a double-clickable solution, again from Marc Liyanage. Upon installing this StartupItem, MySQL will be at your beck and call after every reboot.

With the above instructions, MySQL is woefully unsecured. Anyone can become the administrative MySQL user and wreak havoc with our data. This may not be an issue if you're using MySQL on a development machine, but publicly accessible servers need protection. Much like OS X has a root user with ultimate control over the machine, MySQL also has a root user that has ultimate control over the database server.

By default, the MySQL root user has no password assigned to it. If you take a gander back at our PHP script, you'll see that we connect to our database with that field blank:


   // log into our local server using the MySQL root user.
   $dbh = mysql_connect( "localhost", "root", "" );

The simplest step in beginning to secure our database server is to set a password for MySQL's root user. To do so, enter the following in a Terminal:


    mysqladmin -u root password new_password_here

Once we do that, we'll have to modify our PHP code as well:


   // log into our local server using the MySQL root user.
   $dbh = mysql_connect( "localhost", "root", "new_password_here" );

This is just the start of securing a MySQL installation. You can go much deeper, like restricting access to certain databases by host name, much like we restricted access to certain Web directories with Apache's Allow and Deny directives (see "Choosing Who Sees What" in Part 3 of this series).

After the demonstration . . .

Thankfully, the meeting only lasted half an hour. The wide-eyed wonder boys have left and the redheaded stepchildren have wandered off. You're sitting, once again, with a smug look on your face, complemented by the realization that worrying about job security is a thing of the past. (Just be careful of netslavery.)

You've got databases stored, features adored, and requests explored [1]. Yet you realize there's so much more to Apache lurking beneath the surface, yelling out, "Discover me!" with a glee normally unfounded in typical Mac software. Where would you like to be taken next?


[1] Along with the bosses abhorred, benefits scored, and welcome awards, mouths floored and derision ignored, nothing deplored and happiness poured, computer love has been restored, and with no principles discord! Hail the mighty Fnord! (Yes, I could go on.)

Kevin Hemenway is the coauthor of Mac OS X Hacks, author of Spidering Hacks, and the alter ego of the pervasively strange Morbus Iff, creator of disobey.com, which bills itself as "content for the discontented."


Return to the Mac DevCenter.

Copyright © 2009 O'Reilly Media, Inc.