MySQL, PHP and MySQL Tools for MacOS X

Preface

MySQL is no questions the most popular SQL database server which currently powers over 1 million Web sites. It is extremely fast, reliable, and what is very important - free for anyone who is not distributing modified version of MySQL itself (it means, for example, that you can sell your own databases which just use MySQL, or install unmodified version of MySQL on unlimited number of servers without any fees).

MySQL and Apache PHP module are included with MacOS X Server, so please install these components only if you are running client version of MacOS X, or if they are built as Fink deb packages!

Which MySQL?

As with any Open Source (GPL) software, there are many ways to obtain the same software package:

  • Downloading source code from developer's site or CVS tree and compiling yourself.
  • Downloading compiled binaries for particular platform from master distribution site (like www.MySQL.com for MySQL or www.php.net for PHP).
  • Using customized for particular platform or purposes package(s) built by volunteer(s).
  • Buying shrink-wrapped commercial distributions which often include proprietary extensions and configuration tools, and possibly service and/or consulting package.
  • Buying or downloading OS distribution which already includes software package(s) interested.

For MacOS X client version users I would like to recommend two types of precompiled MySQL packages:

  1. MySQL built as deb for the MacOS X port of Debian Linux package management system called Fink. This is the best way because it will not install anything into or alter MacOS X system directories. Additionally, debs are very easy to upgrade or remove.
  2. MacOS X native packages of MySQL and PHP built by Marc Liyanage.

Installing MySQL deb

First of all you need to install Fink. Fink tutorial for beginners is available here ("Safe and Happy UNIX Hacking with MacOS X"). Then, run "sudo apt-get update", "sudo apt-get install mysql" in terminal. Precompiled MySQL deb will be downloaded from Fink site and installed with all other necessary dependent packages. Fink will add mysql user, create initial tables and set owner/permissions for MySQL data directory. To create startup item for MySQL run "daemonic enable mysql".

You should never login with MacOS X Finder as mysql user if it is created by Fink!

In order to deploy MySQL with extra features (like InnoDB transaction safe table support) or more recent version of MySQL than the one provided by Fink, you have to download or build custom MySQL deb yourself. Please follow this link to another article "Safe and Happy UNIX Hacking with MacOS X" for instruction how to build deb packages on MacOS X.

MySQL data files may be found in "/sw/var/mysql" (visible but not accessible in the Finder). To make data directory accessible in read only mode run "sudo chmod go+rx+rx /sw/var/mysql".

Installing MySQL MacOS X Native Package

MacOS X native packages do not offer advantages of debs (easy upgrade, removal, etc.). If for some reasons you still not convinced,, just follow this instruction step by step.

  1. Open System Preferences -> Users and create user with short name mysql (all lowercase).
  2. Download and install MySQL and MySQL startup item packages from Marc Liyanage web site (you do not need to login as mysql user to do it).
  3. After installation will be completed, open Applications -> Utilities -> Terminal and run consecutively these commands (type them without quotes): "sudo mysql_install_db" (to create initial tables), "sudo chown -R mysql /usr/local/var/" (to set owner of MySQL data directory). Command "sudo" means "do something as superuser" (or another user), so you will be asked for MacOS X administration password.
MySQL (built as MacOS X native package) keeps its data files in UNIX directory "/usr/local/var" by default, which is not visible in the Finder. You have to use terminal to access these files, or better, install MySQL deb (see above).

Security

Run "mysqladmin -u root password your-new-password" or "mysqladmin -u root -h localhost -p password your-new-password" in order to set up password for MySQL root user (do not be confused with MacOS X/UNIX root user, MySQL users and UNIX users are not the same entity)! Try to run "mysql -u root". If this command succeed, it means anyone can connect to your database with full privileges and do everything he likes! Additionally, never run MySQL daemon as UNIX root, only as mysql user! You should study "General Security Guidelines" from MySQL manual very carefully.

Running MySQL

Launch MySQL daemon from terminal with "sudo safe_mysqld --user=mysql &" (for 3.x version) or "sudo mysqld_safe --user=mysql &" (for version 4.x) command. You do not have to run this command if you have installed MySQL startup item and restarted MacOS X.

MySQL 3.23.4x and 4.0 alpha do not shutdown cleanly due to incomplete POSIX thread implementation in Darwin/MacOS 10.1. This problem is fixed in MySQL 3.23.47 and later.

Installing PHP

Now its time to install middleware which will allow to publish MySQL data on the Web. The most often used one (along with PERL/DBI) is HTML server-side embedded scripting language called PHP (stands as "Hypertext Preprocessor"). Server-side means that scripts are executed on the server, not on the client workstation (like JavaScript). With PHP its possible to generate HTML pages, simple graphic, PDF and Flash animation on the fly. Since MacOS X client runs Apache Web server, you will need only to install Apache PHP module and update Apache configuration file.

Please be aware that Apple may ship Apache PHP module with MacOS X client anytime they want. Additionally, any MacOS X update which alters Web Sharing may remove you PHP module and/or reset Apache configuration to defaults.

To check if PHP module is installed and enabled (it may be installed but not enabled), create a text file with line"<?php phpinfo() ?>" and save it in Users -> Your Home Directory -> Sites with the name test.php. Do not forget to enable Web Sharing in System Preferences!. Now launch Web browser and open URL "http://your-IP/~your-name/test.php" (if you are connecting on the same machine where you are running Web server, use "http://127.0.0.1/~your-name/test.php"). PHP test page displayed will confirm that everything is OK, and you may skip next section and proceed further, otherwise see instruction below.

Quit Text Edit if its already running (you need to launch Text Edit in superuser mode in order to have enough privileges to modify UNIX configuration files), then run "sudo open -a TextEdit /etc/httpd/httpd.conf" in terminal. Text Edit will open Apache configuration file. Find lines "# LoadModule php4_module libexec/httpd/libphp4.so", "# AddModule mod_php4.c", "# AddType application/x-httpd-php .php" and "# AddType application/x-httpd-php-source .phps", remove hash marks (#), save configuration file, and finally run "apachectl graceful" (it will instruct Apache Web server to restart).

Try to load again PHP test page as have been described above. Success means that PHP module was installed but have not been enabled by default. If not, you have to download and install PHP module yourself (module and comprehensive instruction are available here). In short, just run consecutively these commands in terminal: "wget http://www.access.ch/ml/software/macosx/libphp4.so.gz" (please wait for download); or if UNIX "wget" command is not available (it was removed in MacOS X 10.1), download "libphp4.so.gz" manually with your Web browser from Marc Liyanage Web site into Users -> Your home directory), then run "gunzip libphp4.so.gz" (to unpack module), "sudo apxs -i -a -n php4 libphp4.so" (to install PHP module, "apxs" stands as Apache extension tool), "apachectl graceful" (to restart Apache) . Now PHP test should work. If everything is OK you can safely remove (or archive) "libphp4.so.gz" and "libphp4.so" from your home directory.

Installing phpMyAdmin

phpMyAdmin is a powerful Open Source (GPL) Web-based administration utility for MySQL server written in PHP. Create mysqladm user, login as mysqladm, download stable (not experimental) release of phpMyAdmin, unpack archive and drop all files and folders into Users -> mysqladm -> Sites (of course, you can do the same using UNIX commands in terminal without even re-login). Find and open file named "config.inc.php". Read in documentation how to properly setup phpMyAdmin, but for basic (insecure) configuration (when MySQL is running on your local workstation) you need to change only these variables: "$cfgPmaAbsoluteUri = 'http://127.0.0.1/~mysqladm';", "$cfgServers[1]['host'] = 'localhost';", "$cfgServers[1]['adv_auth'] = FALSE;","$cfgServers[1]['user'] = 'root';" and "$cfgServers[1]['password'] = 'your-mysql-root-password-here';". Set up permissions for "config.inc.php" as read-write for the group staff (so you do not have to login as mysql user or use "sudo" to modify this file), Turn on Web Sharing, logout, connect as ordinary user, and open URL "http://your-IP/~mysqladm/index.php" (or in case of local setup "http://127.0.0.1/~mysqladm/index.php"). phpMyAdmin screenshots on MacOS X client available here.

WEBMIN Administration Software

WEBMIN is very capable and popular Web-based administration software written in PERL. Along with MySQL, WEBMIN covers a lot of other UNIX administration needs. Although WEBMIN is advertised as being compatible with MacOS X Server, I do not know if it is compatible with MacOS X client (which, for example, does not include many popular PERL modules like DBI).

Commercial MySQL Packages for MacOS X

Commercial MySQL packages for MacOS X available from the company InterServices (SQL4X) and OpenOSX.com. SQL4X offers very easy installation procedure, and nice looking, powerful and capable GUI-based MacOS X native administration tools written with Cocoa. OpenOSX.com sells two MacOS X packaged bundles with MySQL - Database CD and OpenWeb. Both are created using Fink, so you may just download Fink and install all this stuff yourself.

Its up to you to decide whether these extras worth the price of SQL4X ($65) or OpenWeb ($30). However, if you are serious to develop something with MySQL, you should learn how to use UNIX hacks.

Freeware MacOS X Native Clients for MySQL

So far I found only one freeware MacOS X native client for MySQL called EstellaSQL (formerly XmySQL) written by Max Cantor. EstellaSQL is a Cocoa based application.

Shareware MacOS X Native Clients for MySQL

If you are not satisfied with phpMyAdmin or EstellaSQL for some reason or they do not suit your specialized needs you may give a try MySQL Magic or SQL Boss. The last supports a wide range of Open Source and commercial SQL databases like PostgreSQL, OpenBase, FrontBase, as well as database connectivity interface ODBC.

Visual MacOS X Native Tools for PHP and MySQL

Visual MacOS X native shareware editor JaneBUILDER for PHP/MySQL is available thanks to Jane Weeeland. JaneBUILDER allows quickly build dynamic Web pages using mouse/menu driven interface. JaneBUILDER is a great addition to extremely popular and capable general purpose programmer's text editor BBEdit.

Downloads

MySQL deb for MacOS X is available for download here.

 


MacGuruHQ and MacGuruHQ Logo

Copyright© 1996 - 2002 by Andrei Verovski. All right reserved. Content may not be copied in whole or in part, through electronic or other means, without the author's permission. MacGuru Logo (blue penguin) by Sergey Orehov.