web5.doc - Index of

towerdevelopmentData Management

Dec 16, 2012 (5 years and 6 months ago)


Apache Web
Serving With Mac OS X, Part 5

Kevin Hemenway


Editor's Note: Kevin returns with his fifth article concerning Apache, OS X, and the immense power
available to the common man. Ha
ving 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 ag
ain, but he gets to the point rather quickly: "Listen, lots of
people have been asking me about databases and this ess
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 'e
m 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

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


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
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 t
o both.

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

Source code or double

Related Reading

We Mac users, I tell ya', we've had it easy for quite a while. Heck, even those
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
mpts, 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.

his 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
ngled type
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 othe
rwise 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
allows you to double
click a

file; Red Hat grants you an

file; and
Debian has

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

Below, I'm going to show you how to install MySQL. I'll show you where to get the double
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 a
nd 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 avai
lable, as well as various ways to install and compile from source.
We'll be focusing on the two I found most arousing:

Marc Liyanage's
clickable MySQL 3.23.49

compilable MySQL 3.23.49 source code

Apache: The
Definitive Guide,
2nd Edition

Vital Information for
Programmers and




Table of Contents


Sample Chapter

Read Online

Search this book on Safari:

Only This Book

Code F
ragments only

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

user, and the adminis
trative account would be

user, our MySQL server will run as the


Creating this account is rather simple:


Click on your Apple menu.


Choose System Preferences.


Click the Users preference panel.


Click New User.


For Name, enter "MySQL


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

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. Once the download is complete,
the instal
l 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 config
uring 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
installation wrap up."

Compiling MySQL from source

Compiling MySQL from s
ource 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 passwor
d. Also, the latest
OS X Developer Tools should be installed.


If you've installed the packaged version of MySQL (above), you do

need to do anything in
this section.

The first thing, obviously, is to get the source code itself. You can find the cl
osest mirror on
the MySQL
. Once the download is complete, get into your Terminal and create and move into the




mkdir src

cd src

Ah, 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

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

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
, including an alias (called a "symlink" in Linux ter
ms) from

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

zxvf /Users/morbus/Desktop/mysql.gz

We then move into the new directory:

cd mysql

At this point, you're going to use thr
ee commands that are very common when compiling source code.
The first command is

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 ac
t, where
you want it installed, etc.


line for MySQL is simply:


This line gives us an example of two things we can do with a

statement. The first option,
, 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,
, 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 outsi
the scope of this article to get into what all this really means; if you're interested, you can check out

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;

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



will take a look at the configuration you created (using that

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 al
l . . . this is the art of a compile

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

sudo make install

This command simply takes all of the compiled code from our

and installs it in the places we've
requested (said places being overridable using the

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

steps that ensure a properly
running MySQL. Read on, stalwart traveler!

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

irectory. 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

being a symlink to
. (Note, how
ever, that the client programs and server
are also installed in

*Directory* *Contents of directory*

3.23.49/bin' Client programs and the server

/data' Log files, databases

3.23.49/include' Include (header) files

3.23.49/lib' Libraries

3.23.49/scripts' `mysql_install_db'

are/mysql' Error message files

bench' Benchmarks

If you compiled from source, your directory structure becomes:

*Directory* *Contents of directory*


Client programs and scripts

`/usr/local/include/mysql' Include (header) files

`/usr/local/info' Documentation in Info format

`/usr/local/lib/mysql' Libraries

xec' The `mysqld' server

`/usr/local/share/mysql' Error message files

bench' Benchmarks and `crash
me' test

`/usr/local/var' Databases and log files

ith 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 &

# f
or 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 we
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

Apache Web
Serving With Mac OS X, Part 5


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
), and save the file as

within a Web site directory (either

, for example).


print "<pre>";

// log into our local server using the MySQL root user.

$dbh = mysql_connect( "l
ocalhost", "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.

// create a simplistic table.

le = "CREATE table wisdom (


wisdom char(255), author char(125) );";

$response = mysql_query( $table, $dbh );

if ($response) { print "The table was created correctly!
n"; }


Email article link



Blog this

else { pri
nt 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 * F
ROM 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:


print "</pre>";



Again, we're not going to explore the syntax of the PHP script, or the SQL commands that are
used. Suffi
ce 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

After you've saved the file, load it in your Web browser. I saved my copy in
, 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: "Must... remain... awake!"

If I continue running the script, changing the

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!"


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

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
, again from Marc Liyanage.
Upon installing this
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 y
ou're using MySQL on a development machine, but publicly
accessible servers need protection. Much like OS X has a

user with
ultimate control over the machine, MySQL also has a

user that has ultimate
control over the database server.

By default, t
he MySQL

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( "localhos
t", "root", "" );

The simplest step in beginning to secure our database server is to set a password for MySQL's

user. To do so, enter the following in a Terminal:

u root password

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", "
" );

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


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 b
y the realization that worrying about job security is a thing of the past. (Just be careful

Previously in the

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

You've got databases stored, features adored, and requests explored
. 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 t
aken 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
! (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

which bills itself as "content for the

Return to the
Mac DevCenter

Ah, so many things to discuss. .

. . One of the topics we're interested in is what you would like to
see next in this series.

You must be
logged in

to the O'Reilly Network to post a talkback.


appear below the discussion thread.

Showing messages 1 through 6 of 6.

Easy MySQL Install

05 17:24:33

anonymous [


MySQL.com now has package installers for Mac OSX here:


This makes installing MySQL on OS X a snap. Just remember to

read the ReadMe file that comes
with it.