Hack 99 Installing the MySQL Database


Add the functionality of the most popular open source database server to the underpinnings of your Mac-powered web site.

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 best:

  • Marc Liyanage's double-clickable MySQL 3.23.49 (http://www.entropy.ch/software/macosx/mysql/)

  • MySQL's compilable MySQL 3.23.49 source code (http://www.mysql.com/downloads/mysql-3.23.html)

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 both for the double-clickable install and for compiling the source code ? don't skip it.

99.1 Installing the MySQL Package

We're ready to move on with our double-clickable install, so download the MySQL 3.23.49 package from Marc's site (http://www2.entropy.ch/download/mysql-3.23.49.pkg.tar.gz). 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.

99.2 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 discussed earlier. To compile MySQL, you'll need access to your Terminal and an administrative password. Also, the latest OS X Developer Tools should be installed.

If you've installed the packaged version of MySQL (discussed earlier), 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 (http://www.mysql.com/downloads/mirrors.html). Once the download is complete, get into your Terminal [Hack #48], create and move into the /usr/local/src/ directory:

% cd /usr/local
% sudo 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 (as shown previously), 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):

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

We then move into the new directory (again, yours will probably be different):

% 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, and so on.

The configure line for MySQL is simply:

% sudo ./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. Getting into what this really means is outside the scope of this hack; if you're interested, you can check out InnoDB's web site (http://www.innodb.com/) for more information.

After you run the previous 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 configured and turn it into an executable program for OS X. To do this, simply enter the following:

% sudo 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!

99.3 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 later in this hack.

In the case of the package installation, your directory layout is shown here, 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 preceding 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 &

These 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

99.4 Hello, MySQL!

If the foregoing steps 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 (http://www.barebones.com/)), and save the file as test.php within a web-site directory (either /Library/WebServer/Documents/ or /Users/morbus/Sites, for example).

In order for this to work, you should have already turned on PHP [Hack #93] in your onboard Apache web server. If you've not already done so, do so first and then come right back.

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 ( RETURN
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 ) RETURN
values ( 'Hello, World!', '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>";

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 (http://www.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 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: "Hello, World!"

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

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: "Hello, World!"
#2: Morbus sez: "Here is one more."
#3: Morbus sez: "And another."

This output certifies that our PHP-to-MySQL communication is working perfectly.

99.5 Two Minor Additions

When we turn on our web server (through the System Preferences figs/U2192.gif Sharing 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 (http://www2.entropy.ch/download/mysql-StartupItem.pkg.tar.gz), again from Marc Liyanage. Upon installing this startup item, MySQL will be at your beck and call after every reboot.

With the previous 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 as 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" );