Wesley's Tool-Box

You know, installing MySQL stumped me a lot in the past. I'm a little better at it now, hopefully. Let's dive in!

3. MySQL

Now we need another pillar to put up - a database engine. MySQL is a popular open-source choice for this and works with most of the solutions needing database functionality. Installation of MySQL completes are triple-module setup commonly referred to as "APM" (Apache-PHP-MySQL) or "AMP". Since it's installed on OS X, the whole server setup is called "MOXAMP" (Mac OS X-Apache-PHP-MySQL). Install on Linux and you have "LAMP". You get the idea. The APM setup is prequisite for a lot of dynamic website configuration: you have a web server (Apache) that accesses various database entries (MySQL) through scripting language (PHP), like a blog post.

Oh, yeah, the installation.

Go to the Downloads section of the Developer Zone of the MySQL website and click on MySQL 4.1 under "MySQL database server & standard clients". So far this version is the latest recommended release, and I think it's the most trouble-free, too. Scroll down A LOT and you'll get to the Mac OS X distribution packages. Pick 'Standard 4.1.12' (version number may change) of the 'Installer package (Mac OS X v10.3)'. At this time there is no package for 10.4, but 10.3 package works great on my 10.4.1, so I think it's alright.

Get the thing downloaded, and this one's a .dmg file, too, so open it up to find the installation packages. You have the mysql-(looong name).pkg and MySQLStartupItem.pkg. Install the first one to get the whole MySQL thing set up. Then run the second one so MySQL starts up every time you boot. There's MySQL.prefPane thingy you want to install, too. Copy this under /Library/PreferencePanes/ and you'll have 'MySQL' icon in System Preferences. Go to this preference pane after install and start the MySQL server to get down to business. Alternatively, you can start the server in Terminal by typing like this:

sudo /Library/StartupItems/MySQLCOM/MySQLCOM start

You'll need the to enter your admin password. Note that once it's started running and the preference pane has the 'automatically start' option checked, you won't have to bother with these stuff thereafter. It'll start running on its own like Apache. Now, although MySQL is running all nice and stuff we're not done yet, unfortunately. The root and anonymous account for MySQL do not have a password set up and this can be a big security hole if you don't do anything about it. Let's do something about it.

Okay, get Terminal running. Uh... 'Tiger' users can just type something like 'term' in Spotlight and you'll have the app in the list. It's in /Applications/Utilities/, by the way. The nitty-gritty is explained in detail in section 2.9.3 of MySQL manual, but let's get to the nutshell. Type like this:

/usr/local/mysql/bin/mysql -u root

This runs the MySQL monitor under 'root' account. Note that you didn't enter any passwords because there is currently none set up. Leave it like this and ANYONE can manipulate your database under root privileges. That's bad. So let's get that password thingy taken care of. You'll be under mysql> prompt so make sure you're in that. Type like this to find out your hostname:

SELECT Host, User FROM mysql.user;

Next to 'root' user, look for the host that is not 'localhost'. That is your hostname. Remember that. Now we need to actually change the password, like so:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
SET PASSWORD FOR 'root'@'hostname' = PASSWORD('newpwd');

A few things to note here. The newpwd should be replaced with the password of your choice and the hostname is the hostname we found out earlier. Do not forget the single quotes around them.

We now need to remove the anonymous account. We don't want any anonymous accounts lying around, as they are security risks. Type this:

DELETE FROM mysql.user WHERE User = '';

Note that the last part before the semicolon is two single quotes, not one double quote. After all these lines are appropriately typed in, you can type

exit

to get out of the MySQL monitor. After configuring the passwords, next time you want to get in using root account you must type like this:

/usr/local/mysql/bin/mysql -u root -p

Note the -p option added. Omitting this will cause an error. You could put the password after -p, but you shouldn't, for security reasons. Typing like above will present you with a password prompt anyway and you can type your password there.

Alright, if you made it this far, you now have the APM setup ready for some real action. Let's look at setting up some actual applications in the next article so lazy bums like me don't need to actually create a whole web page just to have something looking nice on the server.