AddThis Social Bookmark Button

Listen Print Discuss
Mac OS X > Excerpts >

Web Apps with Tiger: Security and MySQL
Pages: 1, 2

Installing MySQL, the Database Server

Most popular web apps require some sort of database back end, and there are two major open source players: MySQL ("My Ess-Que-Ell") and PostgreSQL ("Postgres Que-Ell"). Which one is "best" is as embroiled a flame war as any other. Some applications work only with MySQL, some only PostgreSQL, some both, some in between. The ones we'll be exploring throughout this series are generally tested and developed with MySQL, and the popular web acronym "LAMP," or "Linux + Apache + MySQL + PHP/Perl/Python," asserts MySQL's popularity.

At the time of this writing, the recommended version of MySQL is 4.1.13. Head on over to their download pages, scroll nearly all the way to the bottom, and choose the "Standard" version of the "Installer package (Mac OS X v10.4)." The "Debug" and "Max" versions are special builds that aren't necessary for our needs. Once the .dmg has finished downloading, mount it as usual and double-click the .pkg that reads "mysql-standard-4.1.13-" blah-blah-blah.

Once this .pkg is finished, you'll have a complete MySQL installation in /usr/local/mysql. Next, double-click the other .pkg in the archive: the MySQLStartupItem. This, oddly, ensures that MySQL will load every time your machine restarts. Nothing too exciting for that install either, which leaves us with the MySQL.prefPane. This Preference Pane gives us a cute little GUI to start-and-stop MySQL manually, which is something we'll rarely need to do. To install for just your user, drag it into ~/Library/PreferencePanes; for every user on your machine, use /Library/PreferencePanes.

Tweaking the Shell and Securing MySQL

The easy part is finished: MySQL is installed. Now we have to worry about our environment--imagine me waving my hands emphatically at the empty air around me. We're going to fiddle with our shell $PATH, which'll allow us to refer to our newly-installed programs as just mysql or mysql_secure_installation instead of the much more laborious /usr/local/mysql/bin/mysql.

How to do this depends on which shell you're using. The default OS X shell is tsch and its configuration lies in ~/.tcshrc. On the other hand, if you've told Terminal to use bash instead, config tweaks go into ~/.bash_profile. Open (or create) the file that corresponds to your shell.

For tcsh, add the following to ~/.tcshrc:

setenv PATH ${PATH}:/usr/local/mysql/bin

For bash, add the following to ~/.bash_profile:

PATH="$PATH:/usr/local/mysql/bin/"

Restart Terminal (so that the above changes will take effect) and run the following command: mysql_secure_installation. If everything has gone as intended, a new utility should start (as opposed to an error message about "command not found"). This utility leads us into our conclusion of this part of our series: securing MySQL. The loud intro admonishes:

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

But, honestly, this is a healthy step to use for any MySQL server, whether you're merely setting up a dev box or maintaining a database-driven list of all your "romance" movies. ("Research! I swear!") Thankfully, this script is more than happy to hold your hand throughout the process, and you can follow along with the desired answers and expected output below--I've bolded the places where your input is required.

In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into
the MySQL root user without the proper authorization.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!
 
Normally, root should only be allowed to connect from "localhost." This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named "test" that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all
changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done! If you've completed all of the above
steps, your MySQL installation should now be secure.

Grrr... I Wanna Do Sumthin'-Sumthin'!

Sadly, you'll have to wait until the next installment before we get our hands dirty with a real-life, honest-to-goodness web application. These first two articles have focused on the foundation of a production-ready web server, with an eye toward battening down the hatches of PHP and MySQL. And, as the tired cliche goes, it is only with a good and strong foundation that we can build something magical. As before, if you have specific web applications or features you'd like to see covered in this series, or questions about the tweaking of MySQL and PHP, don't hesitate to leave a comment below.

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