ISP-In-A-Box: The $500 Mac mini (Part III, MySQL)

This is the third installment in our five-part series on building a full-featured Internet hosting server with a Mac mini. On the first day, we covered hardware requirements and installation and operation of the Apache Web Server. Day two added mail services including SMTP, POP3, and IMAP servers. Today we begin the fun part of being the master of your own Internet universe with the installation of the MySQL data base management system. Why would you want a data base server in general and MySQL in particular? The short answer is that installation of MySQL today and PHP tomorrow truly opens up the Internet universe and gives you access to literally hundreds of fantastic, free, commercial-quality applications. These include blogs such as WordPress (which you are reading now), photo galleries such as Coppermine, content management applications such as phpWebSite, discussion boards such as phpBB2, project management apps such as PHProjekt, customer service systems such as PHP Support Tickets, polling and surveying systems such as phpESP and PHPsurveyor, wikis such as PhpWiki, mailing lists such as phpList, and other terrific applications including PHPauction, phpCOIN, phpFormGenerator, WebCalendar and on and on the list goes. Plus, you can build your own databases and applications to your heart's content without spending a dime for the software or the tools. So let's get started.

Our game plan is to get MySQL installed on your Mac today and give you the rest of the day to play with it. We're going to install MySQL 4.1 even though version 5 is also available. The reason is that most ISPs still run version 4.1, and many of the applications we listed above are not yet compatible with version 5. Then tomorrow we'll add PHP and PHPmyAdmin to the mix, and you'll never have to stare at a MySQL command prompt again... unless you want to. But, it's fun once so just play along. First things first. We need to download the correct version of MySQL to match the correct operating system and correct version of that operating system. This began as a Mac mini project so we're assuming you're running Mac OS X v10.3 aka Panther. If not, you need to make the necessary adjustments as we go along. If you haven't already done so, load the latest Apple patches on your system so that you are current before we begin this installation. Now choose a mirror site that's close to you from this list and download the MySQL installer to your desktop. Once the .dmg image file downloads, it should automatically mount on your desktop. If not, double click on it to mount it now. In the image folder, there are two package files, a preference pane installer to install MySQL in your System Preferences tray, and a readme file. Drag the readme.txt file to your desktop for safekeeping.

Now we're ready to begin the MySQL installation. First, we will install MySQL. Next, we'll install the automatic startup package which is responsible for making sure the MySQL daemon always starts up when you boot up your Mac. And finally, we'll install the new preference pane to start and stop the MySQL server. To get started, double-click on the mysql-standard package. Answer the prompts, choose your default drive, agree to the license, and enter your administrator password. If there is a problem during the installation, consult the readme file. Otherwise, you'll see a message in a minute or so indicating that the installation was successful. Now double-click on the MySQLStartupItem package, choose your default drive, enter your administrator password, and wait for the installation to complete. Lastly, double-click on the PrefPane icon, tell it to install for you (the administrator) only, and wait for the installation to complete. Just to be sure everything went according to plan, restart your Mac.

When your Mac comes back to life, click on the Apple icon in the upper-left corner of the screen, choose System Preferences, and then click on the MySQL icon in the bottom of your System Preferences tray. It should show MySQL running with a check mark next to the option to automatically start during system startup. If not, make the necessary corrections to start MySQL and to assure that it automatically starts in the future. The MySQL installation is now complete, but MySQL is not yet secure! So ... keep reading.

Don't ask me why but MySQL's default installation sets up anonymous and admin user accounts that are wide open (i.e. password-free) for all the world to hack, and they have. Kinda reminds you of another company in the great Northwest, doesn't it. If you don't plug this security hole, any person that discovers your IP address can totally destroy every MySQL database you build and take remote control of your machine! In short, you're toast! It should be noted that the anonymous and admin accounts for localhost access (which are separate accounts) are also password-free, but this shouldn't be a problem so long as you have control of your physical machine. So let's fix the mess and set up MySQL so it's easy to access (for you and your web server, not the world). Open a terminal window by choosing the Terminal application in your Applications/Utilities folder. Type sudo su and press the enter key.

You now can connect to your MySQL server by typing /usr/local/mysql/bin/mysql at a bash command prompt. Let's do it now. You should then see a MySQL command prompt that looks like this: mysql>. First think up a very secure password for remote access to your MySQL server (you're never going to use it probably so make it a good one). Now you're ready to type the following command substituting your new password for the word "secret" and don't forget the two pairs of single quotes and the semi-colon on the end of the command:

When you press the enter key, MySQL should report that two records were changed. If not, try again. Cut and paste the command above if you have to. Once you get the "two records changed" message, double-check your work by typing this command:

MySQL should list four accounts. The two localhost accounts should have blank passwords, and the other two should have passwords that you can't decipher. If not, repeat the drill until you get it, or you might just as well never use MySQL. My apologies for the diatribe. You'll thank me in ten years when you never get hacked. Here are the last two commands to enter in MySQL for now. The first makes certain that your changes get written to disk, and the second gets you out of MySQL gracefully. Once back at a bash command prompt, type exit and enter twice and then Command-Q to close the terminal window.

Your homework for tonight is to take a look at the MySQL documentation so that you get a feel for what can be done with a relational database. Using Safari or any web browser, open the MySQL documentation by entering this address: file:///usr/local/mysql/docs/manual.html#Tutorial . To get back to the MySQL command shell: open a terminal window, type /usr/local/mysql/bin/mysql and press enter. Have fun! Don't forget to end your commands with a semicolon, or MySQL just sits there. Don't forget to exit gracefully (q) when you're finished.

Feb. 9 Supplement. Several common questions have arisen since this article first appeared. I wanted to address them in the body of the article for the benefit of new visitors who might not read the comments.

Missing Preference Pane. On some older Macs (apparently not Mac mini's), there is no Preference Pane option in the MySQL installation folder to install in System Preferences. This is not a big deal. All this preference pane does is allow you to stop and start MySQL. You can do the same thing from a Terminal window (opened from Applications/Utilities):

Uninstalling MySQL. Here are the steps. Don't skip any! You cannot undo these deletions!! All your data will be lost, too!!! If something goes wrong in a step, do not go to the next step. Send me an email. First open a Terminal window (opened from Applications/Utilities):