Home » Technology » Internet/Web » ISP-In-A-Box: The $500 Mac mini (Part III, MySQL)

The Most Versatile VoIP Provider: FREE PORTING

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:

  • update mysql.user set password = password('secret') where host<>'localhost' ;
  • 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:

  • select host,user,password from mysql.user ;
  • 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.

  • flush privileges ;
  • q
  • 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):

  • To start MySQL manually: sudo /library/startupitems/mysql/mysql start
  • To stop MySQL manually: sudo /library/startupitems/mysql/mysql stop
  • To restart MySQL manually: sudo /library/startupitems/mysql/mysql restart
  • 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):

  • Switch to root user access: sudo su
  • Provide admin password if prompted: type your password
  • Move to the folder where MySQL is stored: cd /usr/local
  • List the contents of the folder: ls
  • Write down the exact file names of all files beginning with mysql : there should be two, a file and a directory
  • Stop MySQL manually and make sure it says it is stopped: sudo /library/startupitems/mysql/mysql stop
  • Delete the MySQL symbolic link file: rm mysql
  • Delete the MySQL directory and subdirectories using the long file name you wrote down above: rm -rf mysql.standard.rest.of.filename
  • Move to the Receipts folder: cd /library/receipts
  • Delete the MySQL directory and subdirectories using the long file name you wrote down above: rm -rf mysql.standard.rest.of.filename
  • Move to the startup folder: cd /library/startupitems
  • Delete the MySQL startup directory: rm -rf mysql
  • Delete the other possible MySQL startup directory: rm -rf mysqlcom

  • 16 Comments

    1. Nice articles. But you won’t be able to su until you have set up root access which wasn’t mentioned. ie: Using NetInfoManager

      [WM: I forgot this step. The way to do this is explained here. But, as is noted below, you can accomplish the same thing without the aggravation by just typing "sudo su" instead. Thanks.]

    2. Excellent series of articles!

      I can’t emphasize this strongly enough: DON’T EVEN CONSIDER USING PHP-NUKE. It has more security holes than windows. If you run a Nuke site, it’s guaranteed to get hacked several times a month. Some script kiddies seem to make a career of hacking Nuke sites, since it’s so vulnerable to SQL injection hacks.

      I switched MacMegasite from PHP-Nuke to Drupal and it’s been a huge improvement.

    3. I don’t know if it just me, but the download link for MySQL was incorrect in this article. it should be pointing to mysql-standard-4.1.9-apple-darwin7.7.0-powerpc.dmg no the mysql-standard-4.1.9-apple-darwin6.8-powerpc.dmg version as it does.
      [WM: You’re correct. The MySQL folks updated it since I wrote the article… which is one of the problems with providing hot links. They’re not hot for long in this business. The other version works just as well, however.]

    4. I’ve tried to reinstall mysql, because I’m having privileges issues, but apparently I’m not deleteing all the files, because I’m still having the same issues.
      This is what I’ve deleted:
      /usr/local/mysql-standard-4.1.9-apple-darwin7.7.0-powerpc/
      /Library/StartupItems/MySQLCOM/
      /Library/PreferencePanes/MySQL.prefPane/
      Am I missing something?
      Thanks

      [WM: I’ve updated the body of the article to respond to your questions.]

    5. uninstall?

      i installed mysql on my ibook in the hope of fooling around with it to experiment with php, but my package didn’t come with the system prefence file and i can’t seem to get into it to edit even the admin/guest proviledges, so i want to uninstall and i will try the package out another time, but i can’t seem to find the best way to remove the install. i can’t delete the files from the mysql folder in usr cause they are in use and, being a new switcher, i have no idea how to do a proper uninstall of this program or any other mac program i have if they don’t have an uninstall program.

      can you help me out, please email me a response and thanks oh so much
      mike

      [WM: I’ve updated the body of the article to respond to your questions.]

    6. Unless I enter sudo su before connecting to the mysql server, I cannot create a new database–I get ERROR 1044 (42000): Access denied for user "@’localhost’ to database ‘menagerie’. Should I really have to go the sudo su route to do this?

      [WM: Yep. Everything is working correctly. There is a guest MySQL account, but the account has no privileges. Without switching to the root user, you are accessing the MySQL server as guest. Of course, if you use PhpMyAdmin, it accesses MySQL as root so you’ll be fine there without switching to root access on your machine.]

    7. In my thoughts, installing a database and/or an application server on these first-version of Mac mini is not a really good bet. Unless this is only an experience and you don’t really want to go more "commercial".

      The hard disk is a slow laptop model(4500 rpm and 5400 rpm I think) and these HD are not really built for quickness and reliability. Depending on the load you will receive on your SQL server, this internal 2 1/2 inch HD should be over-used..

      Personaly, I still prefer SCSI drives(parallel and upcoming serials) because it is more than home-level quality drives. Other 3 1/2 inch drives such as SATA should became reliable in the future I guess !?

      The problem for actual Mac mini can be solved by adding an external "real drive" to your mac mini. Keep the OS and web server on the internal, then installing the high-demanding parts on the external drive. Unfortunately, right now, the provided firewire port is a 400Mbits and not a 800.

      [WM: Each to his or her own, I suppose. Unless you’re planning to run something like eBay out of your home using your Mac mini, I don’t think most users will really be able to tell a difference in the speed of your hard disk. Even at 400 megabits, we’re still talking about 40-50 million characters per second of throughput. That’s a lot of web pages.]

    8. I downloaded and installed MySQL on Tiger. However when entering "/usr/local/mysql/bin/mysql I get the following error ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (61) Any ideas?

      [WM: Sounds like the MySQL database server is not running. Start it up with the command: sudo /library/startupitems/mysql/mysql start. Then try again.]

    9. In case this may help others, I figured out what the reinstall problem was. I did some more research and someone suggested that I clear the /Library/Receipts directory of MySQL packages (rm -rf mysql.standard.rest.of.filename) in order to completely reinstall everything. I guess this was why it said "Upgrade" instead of "Install" when I was (re)installing MySQL. I had 4 files in there related to MySQL from my various reinstallations and such. I deleted all of them and then installed MySQL. This time it said "Install." I used the prefpane mentioned above and now everything is working smoothly.

    10. When trying to uninstall mysql, I can remove everything other than the second file with the long filename, it comes up as being a directory, and that’s all it tells me. Any ideas?

      [WM: Reread the uninstall instructions in the body of the article. The correct command to remove the directory and all its contents is included.]

    11. I’ll echo everyone elses thoughts.. great articles! My question is this: PHPadmin is providing me with a red message at the bottom indicating that the config file contains settings (root with no password) that correspond to the default mysql server priviliged account. Your mysql server is running with this default, is open to intrusion, & you really should fix this security hole. Question is … how/where do I gain access to fix this?

      [WM: The significance of the red bar and how to fix it is covered in the tutorial. Also reread the MySQL tutorial. You don’t want to foul up the password scheme we’ve recommended.]

    12. How do you uninstall mysql 5 so I can install mysql 4 on my mac? I can’t find any tutorials on that.

      Does the example you gave to us work for all versions of mysql?

      [WM: Our tutorials assume you’re using a stock MacOS X. Once you install a new version of MySQL or PHP or anything else, you’re on your own. Sorry.]

    13. Re: update mysql.user set password = password(‘secret’) where host<>‘localhost’ ;

      The 4 accounts the MySQL installation created on my machine were:
      User Host
      root localhost
      root [mymachinename].local
      [blank] localhost
      [blank] [mymachinename].local

      Issuing the update command has applied the password to the two ‘[mymachinename].local’ accounts. Was this really the intended effect?
      At present, I’m just testing and not allowing any connections to my machine. When I do, I’d like to be sure I haven’t left myself exposed 😉

      [WM: Correct. Just the .local accounts… and it’s only necessary if you’re running Asterisk@Home 1.x. versions. The newer versions already have passwords.]

    Comments are closed.